Oracle Temporary Table

Syntax: CREATE GLOBAL TEMPORARY TABLE ... ON COMMIT DELETE|PRESERVE ROWS;

DELETE ROWS: specify it for a transaction-specific temporary table. This is the default. Oracle database will truncate the table (delete all its rows) after each commit.

PRESERVE ROWS: specify it for a session-specific temporary table. Oracle database will truncate the table (delete all its rows) when the session is terminated.

When a temporary table is created the first time, its table metadata is stored in the data dictionary, but no space is allocated. Space is allocated for the table segement at the time of the first DML operation on the table, onto the user's default temporary tablespace. Multiple sessions/users can use the same temporary table definition but each session will be allocated with its own temporary table segment.

Privilege: for an user to access the temporary table in another schema, it needs the same privileges: SELECT, INSERT, UPDATE or DELETE as a normal table. However the user just needs INSERT privilege in order to use it and be able to DELETE|PRESERVE ROWS, which doesn't depend on the other privileges.