Feature and Restriction of Temporary Table

Feature of Temporary Table
-----------------------------------
•Indexes can be created on temporary tables. They are also temporary and the data in the index has the same session or transaction scope as the data in the underlying table.

•Unlike permanent tables, temporary tables and their indexes do not automatically allocate a segment when they are created. Instead, segments are allocated when the first INSERT (or CREATE TABLE AS SELECT) is performed. This means that if a SELECT, UPDATE, or DELETE is performed before the first INSERT, the table appears to be empty.

•DDL operations (except TRUNCATE) are allowed on an existing temporary table only if no session is currently bound to that temporary table.

•If you rollback a transaction, the data you entered is lost, although the table definition persists.

•A transaction-specific temporary table allows only one transaction at a time. If there are several autonomous transactions in a single transaction scope, each autonomous transaction can use the table only as soon as the previous one commits.

•Because the data in a temporary table is, by definition, temporary, backup and recovery of temporary table data is not available in the event of a system failure.

•It is good to know about that temporary table itself is not temporary, the data within it is temporary.


Restriction of Temporary Table
-----------------------------------------------
•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.

•You cannot specify the following clauses of the LOB_storage_clause: TABLESPACE, storage_clause, or logging_clause.

•Parallel DML and parallel queries are not supported for temporary tables. Parallel hints are ignored. Specification of the parallel_clause returns an error.

•You cannot specify the segment_attributes_clause, nested_table_col_properties, or parallel_clause.

•Distributed transactions are not supported for temporary tables.

Related Documents
-----------------------------
Create Temporary Table in Oracle