Oracle temporary table

Oracle temporary table-ON COMMIT PRESERVE ROWS
 
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 on commit delete rows statement.
 
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 always exists before we disconnect or manually execute DELETE or TRUNCATE, only the current session can be seen, but not other sessions.
 
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.
 
Www.bkjia.com
 
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
 
Www.bkjia.com

1 2 Next Page