I am very confused about how to use temporary table in Oracle 9.i.
Even through, i know the way to create such kind of its,
Could you show me the use of the table in Oracle as that in SQL server??
I myself find out how to make temporary table and to
use it, so i could share my opinion to you.
1. To create temporary table, use following syntax:
CREATE GLOBAL TEMPORARY TABLE temp
[ON COMMIT DELETE ROWS](default)
or [ON COMMIT PRESERVE ROWS]
2. From now on, read below instructions for more
Specify GLOBAL TEMPORARY to indicate that the table is
temporary and that its definition is visible to all
sessions. The data in a temporary table is visible
only to the session that inserts the data into the
A temporary table has a definition that persists the
same as the definitions of regular tables, but it
contains either session-specific or
transaction-specific data. You specify whether the
data is session- or transaction-specific with the ON
You can perform DDL operations (such as ALTER TABLE,
DROP TABLE, CREATE INDEX) on a temporary table only
when no session is bound to it. A session becomes
bound to a temporary table by performing an INSERT
operation on the table. A session becomes unbound to
the temporary table by issuing a TRUNCATE statement or
at session termination, or, for a transaction-specific
temporary table, by issuing a COMMIT or ABORT
Oracle9i Database Concepts for information on
temporary tables and "Temporary Table Example"
Restrictions on Temporary Tables
* Temporary tables cannot be partitioned,
clustered, or index organized.
* You cannot specify any foreign key constraints
on temporary tables.
* Temporary tables cannot contain columns of
nested table or varray type.
* You cannot specify the following clauses of the
LOB_storage_clause: TABLESPACE, storage_clause,
logging_clause, MONITORING or NOMONITORING, or
* Parallel DML and parallel queries are not
supported for temporary tables. (Parallel hints are
ignored. Specification of the parallel_clause returns
* You cannot specify the
nested_table_col_properties, or parallel_clause.
* Distributed transactions are not supported for
The ON COMMIT clause is relevant only if you are
creating a temporary table. This clause specifies
whether the data in the temporary table persists for
the duration of a transaction or a session.
Specify DELETE ROWS for a transaction-specific
temporary table (this is the default). Oracle will
truncate the table (delete all its rows) after each
Specify PRESERVE ROWS for a session-specific temporary
table. Oracle will truncate the table (delete all its
rows) when you terminate the session.