To create an Oracle staging table, you can have two types of temporary tables:
Session-level temporal tables
temp table at the transaction level.
1) session-level temporary tables because the data in this temporary table is related to your current session,
When you are currently Session
of course, at this point, if you log in another session, you won't see the data inserted into the temporary table in the other session.
the. When a session exits, the data in the temporary table is truncated (TRUNCATE TABLE, the data is emptied).
session-level Temporal table creation method:
CREATE GLOBAL Temporary TABLE TABLENAME (
RowNUmber number,
Content VARCHAR2 (200)
) on COMMIT PRESERVE ROWS;
2) A transaction-level temporary table refers to a transaction-related temporal table, when a transaction is committed or a transaction is rolled back,
The data in the temporary table is truncated by itself, and the other content is consistent with the session-level temporal table (including when exiting the session, the transaction-level temporary table is automatically truncated).
How to create a transaction-level temporary table:
CREATE GLOBAL Temporary TABLE TABLENAME (
RowNUmber number,
Content VARCHAR2 (200)
) on COMMIT DELETE ROWS;
--Delete temporary tables
drop table TABLENAME;
3) Differences between the two types of temporary tables
The session Level temporary table takes on commit preserve rows, while the transaction level uses on commit delete rows;
Usage, the session level is truncated only when the session ends the data in the staging table, and the transaction level temporary table is either commit, rollback, or session end.
The data in the staging table is truncated.
Oracle Definition Temp Table