Oracle temporary table GLOBALTEMPORARYTABLE

ORACLE only creates the table structure (defined in the data dictionary) and does not initialize the memory space. When a session uses a temporary table, ORALCE will use the temporary table space of the current user.

ORACLE only creates the table structure (defined in the data dictionary) and does not initialize the memory space. When a session uses a temporary table, ORALCE will use the temporary table space of the current user.

Temporary tables: they are structured like normal tables, but managed differently. Temporary tables store intermediate result sets of transactions or sessions, the data saved in the temporary table is only visible to the current session, and the data of other sessions is invisible to all sessions, even if other sessions are submitted. Temporary tables do not have concurrent behaviors because they are independent of the current session.

When creating a temporary table, Oracle only creates the table structure (defined in the data dictionary) and does not initialize the memory space. When a session uses a temporary table ,, ORALCE allocates a piece of memory space from the temporary tablespace of the current user. That is to say, the temporary table is allocated storage space only when data is inserted into the temporary table.

Temporary tables are divided into transaction-level temporary tables and session-level temporary tables.
The transaction-level temporary table is only valid for the current transaction. It is specified by the statement: on commit delete rows.
The session-level temporary table is valid for the current session. It is specified by the on commit preserve rows statement.

Example (in SCOTT mode ):
Create global temporary table session_temp_tab on commit preserve rows as select * FROM emp WHERE 1 = 2;
The on commit preserve rows statement specifies that the created temporary table is a session-level temporary table. Data in the temporary table is stored until we disconnect the connection or manually execute DELETE or TRUNCATE.
In, and only the current session can be seen, other sessions can not be seen.

Create global temporary table transaction_temp_tab on commit delete rows as select * FROM emp WHERE 1 = 2;
The on commit delete rows statement specifies that the created temporary table is a transaction-level temporary table. Before COMMIT or ROLLBACK, the data always exists. After the transaction is committed, the data in the table is automatically cleared.

Insert into session_temp_tab select * from emp;
Insert into transaction_temp_tab select * from emp;


SQL> select count (*) from session_temp_tab;

COUNT (*)
----------
14

SQL> select count (*) from transaction_temp_tab;

COUNT (*)
----------
14
SQL> commit;

Commit complete

SQL> select count (*) from session_temp_tab;

COUNT (*)
----------
14

SQL> select count (*) from transaction_temp_tab;

COUNT (*)
----------
0


When COMMIT is followed, the data in the temporary transaction-level table is automatically cleared. Therefore, when you query again, the result is 0;
SQL> disconnect;
Not logged on

SQL> connect scott/tiger;
Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0
Connected as scott

SQL> select count (*) from transaction_temp_tab;

COUNT (*)
----------
0

SQL> select count (*) from session_temp_tab;

COUNT (*)
----------
0
After the temporary table is disconnected, the data in the session-level temporary table is automatically deleted.