Database Reference
In-Depth Information
The
ON COMMIT DELETE ROWS
makes this a transaction-based temporary table. When my session commits, the
rows disappear. The rows will disappear by simply giving back the temporary extents allocated to my table—there is
no overhead involved in the automatic clearing of temporary tables.
Now, let's look at the differences between the two types:
EODA@ORA12CR1> insert into temp_table_session select * from scott.emp;
14 rows created.
EODA@ORA12CR1> insert into temp_table_transaction select * from scott.emp;
14 rows created.
We've just put 14 rows into each
TEMP
table, and this shows we can see them:
EODA@ORA12CR1> select session_cnt, transaction_cnt
2 from ( select count(*) session_cnt from temp_table_session ),
3 ( select count(*) transaction_cnt from temp_table_transaction );
SESSION_CNT TRANSACTION_CNT
----------- ---------------
14 14
EODA@ORA12CR1> commit;
Since we've committed, we'll see the session-based rows but not the transaction-based rows:
EODA@ORA12CR1> select session_cnt, transaction_cnt
2 from ( select count(*) session_cnt from temp_table_session ),
3 ( select count(*) transaction_cnt from temp_table_transaction );
SESSION_CNT TRANSACTION_CNT
----------- ---------------
14 0
EODA@ORA12CR1> disconnect
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
EODA@ORA12CR1> connect eoda
Enter password:
Connected.
Since we've started a new session, we'll see no rows in either table:
EODA@ORA12CR1> select session_cnt, transaction_cnt
2 from ( select count(*) session_cnt from temp_table_session ),
3 ( select count(*) transaction_cnt from temp_table_transaction );
SESSION_CNT TRANSACTION_CNT
----------- ---------------
0 0
