- https://oracle-base.com/articles/misc/temporary-tables
- Oracle Global Temporary Tables (GTTs) are permanent database objects whose structure is visible to all sessions, but their data content is private to each session. This means that while all users can see the table definition, each session can only access the data it has inserted into the GTT.
- Here are key characteristics of GTTs in Oracle:
- Permanent Structure, Temporary Data: The CREATE GLOBAL TEMPORARY TABLE statement defines a table structure that persists in the database. However, the data inserted into this table is temporary and session-specific or transaction-specific.
- Data Scoping: GTTs can be defined with two data retention options:
- ON COMMIT DELETE ROWS: Data is automatically deleted at the end of each transaction (commit or rollback).
- ON COMMIT PRESERVE ROWS: Data persists for the entire session and is only cleared when the session ends.
- Session Isolation: Each session interacts with its own independent set of data within the GTT, ensuring data isolation between users.
- Temporary tables can have triggers associated with them.
- Views can be created against temporary tables.
- All undo associated with DML against a GTT is written to the normal undo tablespace.
- The data in a GTT is stored in the temporary tablespace, not the permanent one.
- You can create indexes on GTTs.
- Views can be created against temporary tables and combinations of temporary and permanent tables
- A GLOBAL TEMPORARY TABLE's definition is available to multiple sessions.