Author: William Mark

Page: 1 2

During database development we often use temporary tables for storing data for the duration of session or transaction. Oracle supports two types of temporary tables, transaction-specific temporary tables or session-specific temporary tables. The data is stored during the transaction for transaction-specific tables and for session-specific transaction the data is stored during the session. Oracle temporary tables are useful in applications which you need to store data for transaction or session level.

Creating Temporary Tables:

Oracle temporary tables are same as global temporary table and Oracle does not allow creation of temporary tables without using keyword ‘GLOBAL'. Oracle temporary tables are created using ‘CREATE GLOBAL TEMPORARY TABLE' command.

When temporary tables are created then although table will always reside in the schema but the data inside temporary table will be available only during the transaction or session based on the type of temporary table. It means once your transaction or session is ended then data will no longer exist in the temporary table.

The ‘ON COMMIT' clause decides if the table is transaction-specific or session-specific.

Temporary Tables - Indexes/Views/Triggers:

You can also create and analyze indexes on temporary tables however the indexes on temporary tables will also be temporary. You can also use views and triggers on temporary tables.

Temporary Tables - Import/Export Utilities:

You can also use import and export utilities with temporary tables.

Temporary Tables - Redo Logs:

In general ORACLE stores data changes in redo logs. However redo logs are not generated for DML statements in temporary tables. Temporary tables generate minimal amount of redo and are not stored in rollback segments.

Temporary Tables - Data Private for each Session:

Data can be inserted into temporary tables by every session simultaneously. However the data is private for each session and hence data inserted by one session cannot be viewed by other session.

Temporary Tables - TRUNCATE command:

If you issue ‘TRUNCATE' command in a session-specific temporary table then the records inserted by that particular session will be deleted and records inserted by other session will not be deleted and remain available to respective sessions. DML locks are not obtained against data in temporary tables. Continued...

Page: 1 2

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Efficient Tips to Get Rid of Long Running Queries!!
   Best Setting for Oracle Multiblock Read Count!!
   Some Exciting Oracle Advisory Utilities you should not miss!!
   DBA Tips: Is your SYSMAN Account Locked??