CREATE GLOBAL TEMPORARY TABLE INCOMING_DATA ( ROW_ID NUMBER(10) NOT NULL, SSN NUMBER(11), ENTRY_DATE DATE NOT NULL, HANDLED_BY VARCHAR2(30 BYTE) NOT NULL, CONTENT_CODE VARCHAR2(4 BYTE), MESSAGE CLOB ) ON COMMIT DELETE ROWS ;
ON COMMIT DELETE ROWS indicates that Oracle will purge the rows after an ended transaction (after a COMMIT, ROLLBACK or an implicit COMMIT).
Instead of ON COMMIT DELETE ROWS you could also use ON DELETE PRESERVE ROWS, which will remove the rows at the end of the user's session.
You can add indexes, triggers and views on a GTT. You can even truncate a temporary table, and it will only affect the curren session's rows, leaving other users' rows intact.
With Oracle 12c came the ability for temporary tables to store its UNDO segments in temporary tablespaces, rather than conventional tablespaces, thus removing the need to generate REDO.
To enable this for your session, use:
ALTER SESSION SET TEMP_UNDO_ENABLED = TRUE;
before creating the GTT.
Sources:
Oracle-Base: article about GTT and another one explaining the 12c new feature "temporary undo"
Oracle Documentation: Overview of GTT
Check my note on private temporary tables, available from Oracle 18c.
No comments:
Post a Comment