Oracle Storage and High water Mark

Lets understand how data is stored in Oracle ...

Each table in oracle is made up of a number of extents and each extent is made up of a number of blocks. 

ie Table = x number of extents

  extent = y number of blocks

 segment = set of extents

A word about high water mark

HWM: Its divides segments into used and unused 

Blocks below HWM contain data and those beyond HWM are free.

DATA

_____________________________HWM__________________________

FREE BLOCKS

Resetting HWM:

Lets say that you fill your table with 4 million records. As a result, you fill in several extents. Now lets delete half of these records. Now your table still has those extents allocated but lots of them are empty. When we run a query on this table with a WHERE clause, all the extents are scanned including the empty ones which is obviously a performance overhead.

So to reset your HWM, use this statement ALTER TABLE MYTABLE MOVE TABLESPACE USERS.

Note: When data is inserted , HWM is automatically adusted. 

           When data is deleted, HWM is not adjusted automatically.

DELETE * v/s TRUNCATE

TRUNCATE resets the HWM, DELETE doesn't.

Global Temporary Tables

CREATE GLOBAL TEMPORARY TABLE was introduced in Oracle 8i.The CREATE GLOBAL TEMPORARY TABLE command creates a temporary table which can be transaction specific or session specific. For transaction-specific temporary tables, data exists for the duration of the transaction while for session-specific temporary tables, data exists for the duration of the session. Data in a temporary table is private to the session. Each session can only see and modify its own data.

Transaction specific table:

CREATE GLOBAL TEMPORARY TABLE T_TEMP (
 column1  NUMBER,
 column2  NUMBER
) ON COMMIT DELETE ROWS;

Session Specific table

CREATE GLOBAL TEMPORARY TABLE T_TEMP
(
 column1  NUMBER,
 column2  NUMBER
) ON COMMIT PRESERVE ROWS;

Features:

Data in temporary tables is automatically deleted at the end of the database session,, no matter what happens.
If the TRUNCATE statement is carried out on a temporary table, only the session specific data is truncated. Other sessions are not affectd.
We can perform DDL commands like ALTER TABLE, DROP TABLE etc only when no session is currently bound to it. A session gets bound to a temporary table when an INSERT is performed on it. The session gets unbound by a TRUNCATE, at session termination, or by doing a COMMIT or ABORT for a transaction-specific temporary table.

ORACLE - SPOOL COMMAND

What is SPOOL ?
Spool Command in ORACLE is used to transfer data from oracle tables into other files.

How to SPOOL from ORACLE in CSV format ??

Set echo off;
Set define Off;
Set feedback Off;
Set serveroutput On;
SET PAGESIZE 0
SET LINESIZE 1000
Spool c:\file.csv

SELECT EMPNAME||','||EMPID||','||EMPSAL FROM EMPLOYEE;

Spool Off;
Set define On;
Set feedback On;

Fiddling with DATES

1. Selecting current date
SELECT SYSDATE FROM DUAL;

2. Adds one day to current date
SELECT SYSDATE+1 FROM DUAL;

3. Adding months
SELECT ADD_MONTHS(SYSDATE,2) FROM DUAL;

4. TRUNC
It truncates the time part from the date TRUNC(SYSDATE).

5. MONTHS_BETWEEN( date1, date2 )
Gives the number of months between two dates.

EXECUTE IMMEDIATE

EXECUTE IMMEDIATE VS DBMS_SQL
EXECUTE IMMEDIATE provides better performance than DBMS_SQL.
EXECUTE IMMEDIATE" was added in Oracle 9i. DBMS_SQL package was used to execute
dynamic sql queries in earlier versions.

Using EXECUTE IMMEDIATE

1.
begin
execute immediate 'SQL Statement';
end;

2.To retrieve value from a dynamic statement
declare
var varchar2(20);
begin
execute immediate 'select count(*) from employee'
into var;
dbms_output.put_line(var);
end;

In case of static sql statement we use select count(*) into var from employee;

3.EXECUTE IMMEDIATE doesn't commits a DML transaction.An explicit commit should be done.

4.Using variables with EXECUTE IMMEDIATE

x number := 10000;
execute immediate 'select count(*) from employee where salary > '||x into var;