Global Temporary Table in Oracle


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.
table-Name
Names the temporary table. If a schema-Name other than SESSION is specified, an error will occur (SQLSTATE 428EK). If the schema-Name is not specified, SESSION is assigned. Multiple connections can define declared global temporary tables with the same name because each connection has its own unique table descriptor for it.

EXAMPLE:

A Web-based E-commerce purchase application allows a customer to create several optional itineraries. Each itinerary is represented by a row in a temporary table. The application updates the rows to reflect changes in the itineraries. When the customer decides which itinerary she wants to use, the application moves the row for that itinerary to a persistent table.During the session, the route data is private. At the end of the session, the optional routes are dropped.The definition of a temporary table is visible to all sessions, but the data in a temporary table is visible only to the session that inserts the data into the table.

so 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 automatically. They often find their application in the situations where data fetch and passage is not possible in single stretch. 

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

SQL> CREATE GLOBAL TEMPORARY TABLE my_temp_table

(column1 NUMBER, column2 NUMBER)

ON COMMIT DELETE ROWS;

Table created.

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.

Examples

set schema myapp;

create table t1(c11 int, c12 date);

declare global temporary table SESSION.t1(c11 int) not logged;
-- The SESSION qualification is redundant here because temporary 
-- tables can only exist in the SESSION schema. 

declare global temporary table t2(c21 int) not logged; 
-- The temporary table is not qualified here with SESSION because temporary 
-- tables can only exist in the SESSION schema.

insert into SESSION.t1 values (1); 
-- SESSION qualification is mandatory here if you want to use 
-- the temporary table, because the current schema is "myapp."

select * from t1; 
-- This select statement is referencing the "myapp.t1" physical 
-- table since the table was not qualified by SESSION.

Note: Temporary tables can be declared only in the SESSION schema. You should never declare a physical schema with the SESSION name.
IMPORTANT:
Prior to Oracle 11g, TABLESPACE cannot be defined for GTT i.e. GTT segments were created in user’s default tablespace. But after Oracle 11g, GTT segments can be created on other tablespaces too, provided the tablespace must be a Temporary tablespace.If the TRUNCATE statement is issued against a temporary table, only the session specific data is truncated. There is no affect on the data of other sessions.
Data in temporary tables is stored in temp segments in the temp tablespace.
Data in temporary tables is automatically deleted at the end of the database session, even if it ends abnormally.
Indexes can be created on temporary tables. The content of the index and the scope of the index is the same as the database session.
Views can be created against temporary tables and combinations of temporary and permanent tables.
Temporary tables can have triggers associated with them.
Export and Import utilities can be used to transfer the table definitions, but no data rows are processed.
Statistics on temporary tables are common to all sessions. Oracle 12c allows session specific statistics.
There are a number of restrictions related to temporary tables but these are version specific.

Temporary tables cannot be partitioned.
Scope of TRUNCATE command is a single database session. There is no effect of TRUNCATE on other sessions.
 Foreign key constraints are not applicable in case of Temporary tables.
references: http://oracle-base.com/articles/
                http://oracle-dba-training-in-chennai.blogspot.in/
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s