Oracle creates a transaction-based and session-based temporary table and temporary table index creation Experiment

The temporary table of Oracle only exists in a session or transaction lifecycle. The data in the temporary table is only visible to the current session.

When the session exits or the user commits a commit or rollback transaction, the data in the temporary table is automatically cleared, but the structure and metadata of the temporary table are also stored in the user's data dictionary.

The definition of a temporary table is visible to all sessions, but the data in the table is only valid for the current SESSION or transaction.
Temporary tables are often used to store the intermediate data of an operation (intermediate stage of data processing ).

Temporary tables do not generate redo statements, which improves the performance of data operations.

Temporary tables do not produce locks and waits.

The experiment is as follows:

1. on commit delete rows: A transaction-based temporary table. The data in the temporary table is transaction-based. When a transaction is committed or rolled back, the data in the temporary table is cleared.

12:35:26 SQL> create global temporary table temp1 on commit delete rows as select * from dba_extents;
Table created
14:06:29 SQL> select count (*) from temp1;
COUNT (*)
----------
0
14:08:16 SQL> insert into temp1 select * from dba_extents;
9221 rows inserted
14:09:02 SQL> select count (*) from temp1;
COUNT (*)
----------
9221
14:09:20 SQL> commit;
Commit complete
14:09:24 SQL> select count (*) from temp1;
COUNT (*)
----------
0
14:09:27 SQL> select * from temp1;
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID bytes blocks RELATIVE_FNO
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

2. preserve rows is a session-based temporary table. The data in the temporary table is session-based. When the session ends, the data in the temporary table is cleared.

14:15:16 SQL> create global temporary table temp2 on commit preserve rows as select * from dba_extents;
Table created
14:15:39 SQL> select count (*) from temp2;
COUNT (*)
----------
9221
14:16:19 SQL> insert into temp2 select * from dba_extents;
9222 rows inserted
14:16:58 SQL> select count (*) from temp2;
COUNT (*)
----------
18443
14:17:15 SQL> commit;
Commit complete
14:17:18 SQL> select count (*) from temp2;
COUNT (*)
----------
18443
14:17:19 SQL>

Open another session to query:
SQL> select count (*) from temp2;
COUNT (*)
----------
0
SQL> select * from temp2;
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID bytes blocks RELATIVE_FNO
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL>

For more details, please continue to read the highlights on the next page:

Oracle tablespace and data files

Temporary Oracle tablespace management and impaired recovery

Oracle kill session

1 2 Next Page