-Oracle allows us create a temporary table.
-The definition of a temporary table is visible to all sessions, but the data in a temporary table is visible only to the session that inserts the data into the table.
-We have to use the CREATE GLOBAL TEMPORARY TABLE statement to create a temporary table.
-The ON COMMIT clause indicates if the data in the table is transaction-specific (the default) or session-specific
DELETE ROWS
This creates a temporary table that is transaction specific. A session becomes bound to the temporary table with a transactions first insert into the table. The binding goes away at the end of the transaction. The database truncates the table (delete all rows) after each commit.
PRESERVE ROWS
This creates a temporary table that is session specific. A session gets bound to the temporary table with the first insert into the table in the session. This binding goes away at the end of the session or by issuing a TRUNCATE of the table in the session. The database truncates the table when you terminate the session.
Temporary tables are useful in applications where a result set is to be buffered, perhaps because it is constructed by running multiple DML operations
(startdate DATE,
enddate DATE,
gl_id CHAR(20))
ON COMMIT DELETE ROWS;
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..
If the TRUNCATE statement is issued against a temporary table, only the session specific data is truncated. There is no effect on the data of other sessions.
If you rollback a transaction, the data you entered is lost, although the table definition persists.
Data in temporary tables is stored in temp segments in the temp tablespace which does not generate any redo so operation using global temporary table are relatively faster. But undo are still generated in undo tablespace which has redo logging. So redo operation are not totally eliminated in Global temporary tables but they are relatively lower
Data in temporary tables is automatically deleted at the end of the database session, even if it ends abnormally.
Views can be created against temporary tables and combinations of temporary and permanent tables. They can be have triggers associated with them
With 12.1(12c database) Oracle release , concept of temporary undo has been introduced which allows the undo segments for global temporary tables to be stored in the temporary tablespace. This allows global temporary tables to be used in physical standby databases and read-only databases, as well as removing the need to create redo.
ALTER SYSTEM SET TEMP_UNDO_ENABLED = TRUE;
ALTER SYSTEM SET TEMP_UNDO_ENABLED = FALSE;