Global Temporary Table in Oracle

Global temporary tables are types of database tables which can privately store data, persistently for a session or transaction. The data flushes out at defined instant once commit is fired or the session has been aborted abruptly. 

“ON COMMIT DELETE ROWS”

This statement is passed implicitly once the global temporary table is created.

Syntax:

CREATE GLOBAL TEMPORARY TABLE TEMP1 (eid NUMBER)

ON COMMIT DELETE ROWS -- Not Mandatory to Pass as this is been passed implicitly by Oracle

“ON COMMIT PRESERVE ROWS”

This statement keeps the records intact even when the transaction has been committed.

Syntax:

CREATE GLOBAL TEMPORARY TABLE TEMP2 (eid NUMBER)

ON COMMIT PRESERVE ROWS

Example:

CREATE GLOBAL TEMPORARY TABLE g1

(

   eid     NUMBER,

   ename   VARCHAR2 (100)

);

INSERT INTO g1

     VALUES (1, 'BIJOY');

INSERT INTO g1

     VALUES (2, 'HELLO123');

INSERT INTO g1

     VALUES (3, 'NEETU');

select * from g1

1	BIJOY
2	HELLO123
3	NEETU

commit; 

The moment one commits, records are deleted from the temporary table and memory is released!!