What is a Global Temporary Table in Oracle?

Global Temporary Table:

The oracle gives the advantage of creating the session specific tables known as Global temporary table.

They are same as the other tables in oracle except that they are exclusive for every session means that the data in temporary table is private to the session (i.e. each session can see and modify its own data). The LOCK statement has no effect on a temporary table because each session has its own private data.

A TRUNCATE statement issued on a session-specific temporary table truncates data in its own session.

It does not truncate the data of other sessions that are using the same table.

Syntax for creating Global Temporary Table:

·         Session Level Global Temporary Table:-

    Create global temporary table temp_table (column1  NUMBER,column2  NUMBER)

    on commit preserve rows

·         Transaction Level Global Temporary Table:-

    Create global temporary table temp_table (column1  NUMBER,column2  NUMBER)

     on commit delete rows;

The ON COMMIT DELETE ROWS clause indicates that the data should be deleted at the end of the transaction.

The ON COMMIT PRESERVE ROWS clause indicates that rows should be preserved until the end of the session.

Summary:

·         You can create indexes for temporary tables using the CREATE INDEX statement. Indexes created on temporary tables are also temporary, and the data in the index has the same session or transaction scope as the data in the temporary table.

·         You can create views that access both temporary and permanent tables.

·         You can also create triggers on temporary tables.

·         You can perform DDL statements (ALTER TABLE, DROP TABLE, CREATE INDEX, and so on) on a temporary table 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.