Here is another feature I found
interesting that was introduced in Oracle 8i. Temporary
tables, yep temporary tables. You may say, whats
the big deal, I was creating temporary tables from
before, whats new now. These temporary tables have
a global definition. When a user inserts rows in
a session, those rows are only visible to that session
and the rows are only available until the transaction
or the session is ended based on how the table has
been defined. Confused....Read On...
Temporary Tables
Oracle 8i introduces a new
concept called global temporary tables. This version
only provides global temporary tables and local
tables are expected in the future. These are like
normal tables but the segment is created only when
the data is inserted and hence temporary in nature.
create global temporary table
dbatest
( c1 number, c2 number);
The above statement creates
a table whose definition is visible to all the sessions
but the data is visible to only the session that
has created it. Temporary tables can be used by
developers to store session / transaction specific
data that can be discarded as soon as the session/transaction
ends. When a truncate is issued on this table, only
data related to that particular session is truncated.
The above code creates a
table named dbatest. When data is inserted into
this table the data persists either at the session
or transaction level based on how "On Commit"
parameter is specified. On commit can be specified
as "delete rows" or "preserve rows".
Delete Rows seems to be the default.
Create global temporary table
dbatest
(
c1 number,
c2 number
) on commit delete rows;
In the above table, as soon as
the user ends the transaction by issuing a commit
statement, the data in the temporary table is deleted
( truncated ).
Create global temporary table
dbatest
(
c1 number,
c2 number
) on commit preserve rows;
The above statement causes the
creation of a table that will keep its rows even
after the transaction is committed. If the user
inserts rows into the above table and then commits
the data, the data that was inserted will exist
until the session is ended.
Restrictions
Temporary tables cannot
contain nested tables or varray types or they cannot
be partitioned, index-organized or clustered. They
cannot be used in parallel DML or parallel queries
and distributed transactions are not supported on
these tables.
