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.