Oracle two temporary tables created and used in detail

In addition to saving permanent tables, the Oracle database can also establish temporary table temporary tables. These temporary tables are used to hold data for session sessions, or to store data in a transaction. When the session exits or the user submits a commit and rolls back the rollback transaction, the data for the temporary table is automatically emptied, but the structure of the temporary table and the metadata are also stored in the user's data dictionary.

Classification:
1. Session-Level staging table

A session-level temporary table is the data in a staging table that exists only during the session lifecycle, and when the user exits the end of the session, Oracle automatically clears the data from the staging table.

Format:

Create Global Temporary Table table_name
(
Col1 Type1,
Col2 Type2
...
)
On Commit Preserve Rows;
2. Transaction-level staging table
A transaction-level temporary table refers to the data in a staging table that exists only during the transaction life cycle.
Create Global Temporary Table table_name
(
Col1 Type1,
Col2 Type2
...
)
On Commit Delete Rows;

When a transaction ends (commit or rollback), Oracle automatically clears the data from the staging table.

The following shows the creation and use of temporary tables in Oracle 10g:

1. Create a transaction-level staging table, insert a piece of data, and query:

Create global temporary table TRANSACTION_TEMP_TB (col1 varchar) on commit delete Rows;insert into  transaction_te MP_TB values (' Test '); select * from  TRANSACTION_TEMP_TB;

2. When a commit or rollback operation is performed, the data in the temporary table is emptied, but the table structure still exists:

3. Create a session-level staging table, insert a piece of data, and query:

Create global temporary table SESSION_TEMP_TB (col1 varchar) on commit preserve rows;insert into SESSION_TEMP_TB value S (' Test '); select * from SESSION_TEMP_TB;

4. Perform a commit or rollback operation, the data in the table still exists, create a new command window (equivalent to open a new session), the data in the table can not be queried:

5. If the session that created the session temp table does not end, you cannot delete the temporary table because the temporary table is still in use, but the end session (which closes the command window that created the session-level temporary table) can be deleted:

Oracle two temporary tables created and used in detail