Oracle temporary table

I. Session-based temporary tables

-- To create a session-based temporary table, remember the global temporary keyword and on commit preserve rows (Session-based)

Create global temporary table TEMP_SESSION (COL1 TYPE1) on commit preserve rows;

SQL> create global temporary table ljb_tmp_session on commit preserve rows as select * from dba_objects where 1 = 2;

Table created

-- The following shows that the attribute of the current table is a global temporary table and the type is session-based.

SQL> select table_name, temporary, duration from user_tables where table_name = 'ljb _ TMP_SESSION ';

TABLE_NAME TEMPORARY DURATION

-------------------------------------------------------------------------------------------

LJB_TMP_SESSION y sys $ SESSION

Ii. Temporary transaction-based tables

-- To create a session-based temporary table, remember the global temporary keyword and on commit delete rows (based on transactions)

Create global temporary table Table_TRANSACTION (COL1 TYPE1) on commit delete rows;

SQL> create global temporary table ljb_tmp_transaction on commit delete rows as select * from dba_objects where 1 = 2;

Table created

-- The following shows that the attribute of the current table is a global temporary table and the type is transaction-based.

SQL> select table_name, temporary, DURATION from user_tables where table_name = 'ljb _ tmp_transaction ';

TABLE_NAME TEMPORARY DURATION

-----------------------------------------------------------------------------------------------

LJB_TMP_TRANSACTION y sys $ TRANSACTION

Whether it is a transaction-based or SESSION-based temporary table, it is invisible to other sessions. In other words, data only exists in the current SESSION. The temporary table based on the transaction has any commit action in this SESSION, and the data disappears immediately. The SESSION-based temporary table still submits data during the SESSION lifetime and can be rolled back, there is no difference between a SESSION and a normal table.

Iii. Differences:

1. SESSION-level temporary table: the data in the temporary table is related to the current session. When your SESSION does not exit, the data in the temporary table exists. When you exit or disconnect the session, do not cut off the data. When you log in with another SESSION, you cannot see the data of other SESSION temporary tables. Creation Method

Create global temporary table TEMP_SESSION (COL1 TYPE1) on commit preserve rows;

2. Temporary table at the transaction level: the temporary table is related to the transaction. when the transaction is committed or rolled back, the temporary table data is automatically truncated. Other and SESSION-level temporary tables, including table truncation when the SESSION is exited.

Create global temporary table Table_TRANSACTION (COL1 TYPE1) on commit delete rows;

3. Differences between two types of temporary tables: on commit preserve rows is used for session-level temporary tables, and on commit delete rows is used for transactions, the data in the temporary table at the session level is truncated only when the session ends. In the temporary table at the transaction level, whether it is commit, rollback, or session ends, the data in the temporary table is truncated.

The conclusion is as follows: if the application is very simple, you should not combine the temporary tables multiple times, such as merging or deleting multiple operations, and use transaction-based, simpler! If you want to combine temporary tables, You must select a session-based temporary table. If you select an incorrect table, the data in the combination operation will be lost, causing a big problem!

This article is from the "wushuang City" blog and is not reposted!