Temporary oracle table

An Oracle temporary table can have two types of temporary tables
1. Session-level temporary table
Saves the data used by the current session, which exists during the data session. After each submission, ORACLE truncates the table (deletes all rows). The data inserted by two different sessions is irrelevant.
2. Transaction-level temporary tables
Save the data used by the current transaction. The data only exists during the transaction. When the session is interrupted, ORACLE truncates the table. Session data is private to the current session. Each session can only view and modify its own data. The DML lock is not added to the data in the temporary table.
Ii. Create syntax
1 SESSION-level temporary table
SQL code
Create global temporary table temp_tbl (col_a varchar2 (30 ))
On commit preserve rows

2. TRANSACTION-level temporary table
SQL code
Create global temporary table temp_tbl (col_a varchar2 (30 ))
On commit delete rows

These two types of tables are inserted and queried in the same way as other tables.
Differences between the three types of temporary tables
Session-level temporary tables use on commit preserve rows, while transaction-level temporary tables use on commit delete rows. In usage, session-level data is truncated only when the session ends temporary tables, in addition, the data in the temporary transaction table is truncated, whether it is commit, rollback, or session termination.
4. Create
SQL code
Create procduce test
Is
Isql varchar2 (200 );
Dptable varchar2 (100): = 'drop table test ';
Begin
Isql: = 'Create global temporary table test (sid int, sname varchar2 (20) on commit delete rows;
Execute immediate isql; -- create a temporary table
Insert into test values (1, 'abc ');
Execute immediate dptable; --- delete a temporary table
End;

Author: "Getting to the ground"