Create and use two temporary tables in Oracle

Create and use two temporary tables in Oracle

In addition to permanent tables, the ORACLE database can also create temporary tables. These temporary tables are used to save the data of a SESSION or the data required in a transaction. When the session exits or the user commits a commit or rollback transaction, the data in the temporary table is automatically cleared, but the structure and metadata of the temporary table are also stored in the user's data dictionary.

Category:
1. Session-level temporary table

A session-level temporary table means that the data in the temporary table only exists in the session lifecycle. When the user exits the session, Oracle automatically clears the data in the temporary table.

Format:

Create Global Temporary Table Table_Name
(
Col1 Type1,
Col2 Type2
...
)
On Commit Preserve Rows;
2. Transaction-level temporary table
Transaction-level temporary table means that the data in the temporary table only exists in the transaction lifecycle.
Create Global Temporary Table Table_Name
(
Col1 Type1,
Col2 Type2
...
)
On Commit Delete Rows;

When a transaction ends (commit or rollback), Oracle automatically clears the data in the temporary table.

The following shows how to create and use a temporary table in Oracle 10 GB:

1. Create a temporary transaction table, insert a piece of data, and query:

create global temporary table transaction_temp_tb (col1 varchar(20)) on commit delete rows;insert into  transaction_temp_tb values('test');select * from  transaction_temp_tb;

2. Execute the commit or rollback operation to clear the data in the temporary table, but the table structure still exists:

3. Create a session-level temporary table, insert a piece of data, and query:

create global temporary table session_temp_tb (col1 varchar(20)) on commit preserve rows;insert into session_temp_tb values('test');select * from session_temp_tb;

4. Execute the commit or rollback operation. The data in the table still exists. Creating a new command window (equivalent to opening a new session) means that the data in the table cannot be queried:

5. if the session for creating a session temporary table does not end, the temporary table cannot be deleted because the temporary table is still in use but ends the session (close the command window for creating a session-level temporary table) then you can delete it: