ORACLE TEMPORARY TABLES
The CREATE GLOBAL TEMPORARY TABLE command creates a temporary table which can be transaction specific or session specific. For transaction-specific temporary tables, data exists for the duration of the transaction while for session-specific temporary tables, data exists for the duration of the session. Data in a temporary table is private to the session. Each session can only see and modify its own data. The table definition itself is not temporary.
You can create indexes for temporary tables using the CREATE INDEX command. 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 perform DDL commands (ALTER TABLE, DROP TABLE, CREATE INDEX, and so on) on a temporary table only when no session is currently bound to it. A session gets bound to a temporary table when an INSERT is performed on it. The session gets unbound by a TRUNCATE, at session termination, or by doing a COMMIT or ABORT for a transaction-specific temporary table.
Temporary tables use temporary segments. Temporary segments are de-allocated at the end of the transaction for transaction-specific temporary tables. For session-specific temporary tables, they are de-allocated at the end of the session.
Multi-session scenarios:
1- Two different users try to create a global temporary table with the same name
Both tables get created. Each user sees his/her own table.
2- Two sessions by a single user try to create a global temporary table with the same name.
Once the table has been created in one of the sessions, the table exists and an error is given to the second session when trying to create the table. This behavior occurs whether or not the table structure is defined to be the same in both sessions.
3- Two sessions by a single user insert rows into a global temporary table.
Each session can insert rows into the table, no matter which session created the table.
4- Two sessions by the same user select from a global temporary table.
A given session will only see the rows that it has inserted, as if the table was private to that session.
5- A session tries to alter a temporary table that another session is also using.
Columns can not be added/deleted/modified as long as the table is bound to a session.
Renaming, however, can be done even when another session has rows in the table.
6- A session attempts to rename a global temporary table in which another session has inserted some rows
The table gets renamed and rows are preserved. After the rename has taken place, both sessions must use the new name to refer to that table.
99
