Temporary Tables
Temporary tables hold session-private data that exists only for the duration of a transaction or session.
There are two options for temporary tables transaction specific or session specific.
ON COMMIT DELETE ROWS specifies that the temporary table is transaction specific and Oracle truncates the table (delete all rows) after each commit.
ON COMMIT PRESERVE ROWS specifies that the temporary table is session specific and Oracle truncates the table when you terminate the session.
Eg. This creates a session specific global temporary table
CREATE GLOBAL TEMPORARY TABLE name
ON COMMIT PRESERVE ROWS
AS
select * from v$sqlarea;
Each session can only see and modify its own data. DML locks are not acquired on the data of the temporary tables.
The LOCK statement has no effect on a temporary table, because each session has its own private data.
A TRUNCATE statement issued on a session-specific temporary table truncates data in its own session.
It does not truncate the data of other sessions that are using the same table.
DML statements on temporary tables do not generate redo logs for the data changes.
However, undo logs for the data and redo logs for the undo logs are generated.
Data from the temporary table is automatically dropped in the case of session termination,
either when the user logs off or when the session terminates abnormally such as during a session or instance crash.
You can create indexes for temporary tables using the CREATE INDEX statement.
Indexes created on temporary tables are also temporary, and the data in the index has the same session or transaction scope as the data in the temporary table.
You can create views that access both temporary and permanent tables. You can also create triggers on temporary tables.
The Export and Import utilities can export and import the definition of a temporary table. However, no data rows are exported even if you use the ROWS clause. Similarly, you can replicate the definition of a temporary table, but you cannot replicate its data.