Global temporary tables

Global temporary tables.

- The maintenance and management of temporary tables can be delegated to the server by using Global Temporary Tables.

- The DECLARE GLOBAL TEMPORARY TABLE statement defines a temporary table for the current connection.

- 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.

- The data in a global temporary table is private.

Temporary tables are useful when:

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

Syntax:
DECLARE GLOBAL TEMPORARY TABLE tempTableName
{ columnDefinition [ , columnDefinition ]* }
[ ON COMMIT { DELETE | PRESERVE } ROWS ]
NOT LOGGED [ ON ROLLBACK DELETE ROWS ]

Example:
CREATE GLOBAL TEMPORARY TABLE table_a
(
   x NUMBER,
   y NUMBER
) ON COMMIT PRESERVE ROWS;
What is Nested Table?
What is Nested Table? - Nested Table is a table inside a table. It can have several rows for each row of its parent table..
How to add a new column to an existing Table
How to add a new column to an existing Table - Use the ALTER TABLE command to do this....
How many types of Tables supported by Oracle?
How many types of Tables supported by Oracle? - Oracle supports 4 types of tables based on how data is organized in storage.....
Post your comment