Global Temporary Table in Oracle

GTT(Global Temporary Table) :-
These tables do not reside in the system catalogs and are not persistent. Temporary tables exist only during the connection that declared them and cannot be referenced outside of that connection. When the connection closes, the rows of the table are deleted, and the in-memory description of the temporary table is dropped.


Temporary tables are useful when:

    The table structure is not known before using an application.
    Other users do not need the same table structure.
    Data in the temporary table is needed while using the application.
    The table can be declared and dropped without holding the locks on the system catalog.

Syntax:-

    DECLARE GLOBAL TEMPORARY TABLE table_name
    { column-definition [ , column-definition ] * }
    [ ON COMMIT {DELETE | PRESERVE} ROWS ] 
    NOT LOGGED [ON ROLLBACK DELETE ROWS];


   
ON COMMIT:-
            Specifies the action taken on the global temporary table when a COMMIT operation is performed.


DELETE ROWS:-   
            All rows of the table will be deleted if no hold-able cursor is open on the table. This is the default value for ON COMMIT. If you specify ON ROLLBACK DELETE ROWS, this will delete all the rows in the table only if the temporary table was used. ON COMMIT DELETE ROWS will delete the rows in the table even if the table was not used (if the table does not have hold-able cursors open on it).


PRESERVE ROWS:-
                The rows of the table will be preserved.


NOT LOGGED:-
            Specifies the action taken on the global temporary table when a rollback operation is performed. When a ROLLBACK (or ROLLBACK TO SAVEPOINT) operation is performed, if the table was created in the unit of work (or savepoint), the table will be dropped. If the table was dropped in the unit of work (or savepoint), the table will be restored with no rows.


ON ROLLBACK DELETE ROWS:-
                        This is the default value for NOT LOGGED. NOT LOGGED [ON ROLLBACK DELETE ROWS ]] specifies the action that is to be taken on the global temporary table when a ROLLBACK or (ROLLBACK TO SAVEPOINT) operation is performed. If the table data has been changed, all the rows will be deleted.
   
   
Example:-

  CREATE GLOBAL TEMPORARY TABLE XX_EMPLOYEE_OCPR (
   EMP_ID NUMBER,
   HIRE_DATE DATE,
   SALARY NUMBER)
  ON COMMIT PRESERVE ROWS;

  
Example:-


  CREATE GLOBAL TEMPORARY TABLE XX_EMPLOYEE_OCDR (
   EMP_ID NUMBER,
   HIRE_DATE DATE,
   SALARY NUMBER)
  ON COMMIT DELETE ROWS;
 
 



  Thanks