Temporal tables in Oracle

Temp table:

In addition to the permanent table, Oracle can also create temporary tables to hold the session's private data, which exists only in a single transaction or during the duration of the session.

The Create GLOBAL temporary table statement establishes a temporary table, which can be either a transaction level or a session level. For a transaction-level temporal table, data exists for the duration of the transaction. For a session-level temporary, data exists for the duration of the session. The data in the temporary table is private to the respective session. Each session can only be seen and modified to its own data. DML lock is not required for data on a temporary table. The lock statement has no effect on the temporary table because each session has his own private data.

The truncate command is issued for a temporary table at the session level, which truncate the data belonging to that session. The truncate command does not truncate data for other sessions that use the same table name.

DML statements on a temporary table do not generate redo. However, the undo logs for the data and redo logs for the undo logs is generated. When the session terminates (user logs off or the session terminates abnormally), the data in the temporary table is automatically dropped.

You can use the CREATE INDEX statement on a temporary table to index it. Indexes built on temporary tables are also temporary, and data in the index has the same session or transaction scope as data in the staging table.

You can create a view that accesses temporary tables and permanent tables. You can also create a trigger on a temporary table.

Oracle Utilities (NOTE: EXP, etc.) can export and import the definition of a temporary table, but even if you use the ROWS clause, data rows in the staging table will not be exported. Similarly, you can copy the definition of a temporary table, but you cannot copy data from the table.