Sometimes we need to store data temporarily in a table . From Oracle 8i onward, we can manage temporary tables can be delegated to the server by using Global Temporary Tables.
Creation of Global Temporary Tables
The data in a global temporary table is private, such that data inserted by a session can only be accessed by that session.
CREATE GLOBAL TEMPORARY TABLE temp_test_gtt ( column1 NUMBER, column2 NUMBER ) ON COMMIT DELETE ROWS;
In contrast, the
ON COMMIT PRESERVE ROWSclause indicates that rows should be preserved until the end of the session.
CREATE GLOBAL TEMPORARY TABLE temp_test_gtt ( column1 NUMBER, column2 NUMBER ) ON COMMIT PRESERVE ROWS;
- If the TRUNCATE statement is issued against a temporary table, only the session specific data is trucated. There is no affect on the data of other sessions.
- Data in temporary tables is stored in temp segments in the temp tablespace.
- Data in temporary tables is automatically deleted at the end of the database session, even if it ends abnormally.
- Indexes can be created on temporary tables. The content of the index and the scope of the index is the same as the database session.
- Views can be created against temporary tables and combinations of temporary and permanent tables.
- Temporary tables can have triggers associated with them.
- Export and Import utilities can be used to transfer the table definitions, but no data rows are processed.
- Statistics on temporary tables are common to all sessions. Oracle 12c allows session specific statistics.
- There are a number of restrictions related to temporary tables but these are version specific.