SAMEER WADHWA
Temporary tables also called Global temporary tables
always created in a user’s temporary tablespace. Global temporary tables
contains either session specific data
or transaction specific data.
(ON COMMIT DELETE ROWS) (ON COMMIT PRESERVE
ROWS) 
Transaction Specific data
Session
specific data
Temporary Tablespace
Default Tablespace
USER
Syntax for creating a global temporary tables is as follows :-
Create GLOBAL TEMPORARY TABLE tablename
(columns)
[ ON
COMMIT PRESERVE | DELETE ROWS ]
The default option is to delete rows on commit.
Let us consider to create temporary table say session_temp for session specific data as
Sql > create GLOBAL TEMPORARY Table session_temp
( empno number primary key,deptno number,
ename varchar2(30)
on commit preserve rows;
Now let us consider to create temporary table say transaction_temp for transaction specific data as
Sql > create GLOBAL TEMPORARY Table transaction_temp
( empno number primary key,deptno number,
ename varchar2(30)
on commit
delete rows;
Temporary tables does not allocate
a segment after creation. Temporary
segment is allocated to temporary tables when first insert or create table as
select is performed. Different session using the same GLOBAL TEMPORARY table
get allocated different temporary segment.
In the following figure user say U
has three sessions US-A,US-B,US-C. All the Session is accessing temporary table
(T) created in a user default temporary tablespace. Depending upon session or transaction specific data ,each user session
has its own temporary segment to store data of temporary table .
The temporary segments are cleaned
up atomatically at session end or
transaction end .
US-C USER SESSIONS TEMPORARY SEGMENT (S3)
US-B
Temporary table which contains the session specific
data is independent with each of session created by user (U). The session US-A can not see the data of session US-B or US-C or vice
versa. Each session data in the temporary
table is only specific to that session.
Temporary table which contains the
transaction specific data is available only till the transaction is active or
open. Once the transaction is close by commiting or rollback , the data in the transaction
table is deleted automatically.
Some other Points to be remember regarding temporary tables
are as follows :-
· Indexes , triggers and views can be created on these
table
( Index
can only be created when the table is empty)
· Logging/NoLogging
clause can not be specified.
· Only redo for undo is generated in the redo log files.
· Constraints defined on tables are specific to session
.i.e if there is a primary key defined
on a table, Then duplicate data can be inserted by different sessions.
· Truncate truncate only session specific data.
· You can export or import only the definition of table
· Can not be partitioned, index- organized or
cluster
· Foreign key
can not be specified
· Can not
contains columns of nested table or varray.
· Storage clause can not be specified.
· Parallel query and parallel DML are not supported.
Conclusion :
Temporary tables is a
very powerful feature of oracle8i .
Developers and Dbas may have great advantange by using it correctly.
Reference:
Oracle server concepts
Oracle SQL References
Oracle Metalink
Thanks to you for
reading this article . Please feel free
to send me your comments or feedback at wadhwa_s@hotmail.com
Sameer wadhwa
|