GTT in ORACLE

What are Global Temporary Tables (GTT) in ORACLE and how to use them?

•  Data stored temporarily. Data stored as long as the session or transaction lasts and is private for each              session.
•  The Definition is visible to all sessions.
•  Data stored in RAM so processing will be faster. Logical read from RAM.
•  After Commit or Disconnection, the data is lost but table definition remains in DB.
•  Foreign key constraints are not applicable in case of Temporary table.
•  Temporary tables are useful while doing runtime operations.
•  We can store the data at runtime and after completing the operation the data will be deleted.

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.
• Decreased redo generation as, by definition, they are non-logging.
• Non-interference between private sets of data
• The table can be declared and dropped without holding the locks on the system catalog.

Types of GTT

• Transaction specific GTT
• Session specific GTT

Transaction specific GTT

Data will be there until you perform Commit/Rollback.

Create Global Temporary Table GTT

(

ID Number

)

On Commit Delete rows;

Data will be removed once you fire commit/Rollback after DML operations on GTT.

SQL> CREATE Global Temporary Table MyGTT
  2  (ProductID NUMBER, Description Varchar2(10))
  3  ON COMMIT DELETE ROWS;

Table created.

Now Insert Data into this table – Commit- and check the result.

/*Inserting Data Into TRANSACTION Specific GTT*/

SQL> INSERT INTO MyGTT Values(10001,'iPhone');

1 row created.

SQL> INSERT INTO MyGTT Values(10002,'Samsung');

1 row created.

SQL> INSERT INTO MyGTT Values(10003,'LG');

1 row created.

SQL> INSERT INTO MyGTT Values(10004,'Nokia');

1 row created.

SQL> SELECT Count(*) From MyGTT;

  COUNT(*)
----------
         4

/*Now Commit and see the Result*/

SQL> COMMIT;

Commit complete.

SQL> SELECT Count(*) From MyGTT;

  COUNT(*)
----------
         0

Session specific GTT

Data will remain till the end of the session.

Create Global Temporary Table GTT

(

ID Number

)

On Commit Preserve rows;

Data will remain even if you fire commit/Rollback after DML operations on GTT2 within the same session. Once you close the session table data will be Deleted.

In contrast, the ON COMMIT PRESERVE ROWS clause indicates that rows should persist beyond the end of the transaction. They will only be removed at the end of the session.

SQL> CREATE Global Temporary Table MyGTT
  2  (ProductID NUMBER, Description Varchar2(10))
  3  ON COMMIT PRESERVE ROWS;

Table created.

Now Insert Data into this table – Reconnect- and check the result.

/*Inserting Data into Session Specific Table*/

SQL> INSERT INTO MyGTT Values(10001,'iPhone');

1 row created.

SQL> INSERT INTO MyGTT Values(10002,'Samsung');

1 row created.

SQL> INSERT INTO MyGTT Values(10003,'LG');

1 row created.

/*Now Commit and see the Result*/

SQL> COMMIT;

Commit complete.

SQL> SELECT Count(*) From MyGTT;

  COUNT(*)
----------
         3

SQL> SELECT * From MyGTT;

 PRODUCTID DESCRIPTIO
---------- ----------
     10001 iPhone
     10002 Samsung
     10003 LG

/*Data is still there as it is SESSION Specific GTT not Transaction Specific*/
/*Now Reconnect and check the Table Content*/

SQL> Conn HR/Baluni007
Connected.
SQL> SELECT * From MyGTT;

no rows selected

You can use any of the above GTT type as per your requirement.

Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit. So, leave your thoughts before you leave the page.