Temporary Oracle table

Create a temporary Oracle table

CREATE GLOBAL TEMPORARY TABLE TABLENAME (COL1 VARCHAR2(10),COL2 NUMBER) ON COMMIT PRESERVE(DELETE) ROWS ;

 

This temporary table does not occupy the tablespace, and the data of the other table cannot be seen between different sessions.
After the session ends, data in the table is automatically cleared. If delete rows is selected, data is cleared at the time of submission, and preserve is cleared until the session ends.

In Oracle8i, you can create two types of temporary tables:
(1) Session-specific temporary tables
Create global temporary <table_name> (<column specification>)
On commit preserve rows;

(2) temporary tables specific to transactions
Create global temporary <table_name> (<column specification>)
On commit Delete rows;

Create global temporary table mytemptable
Although the created temporary table exists, you can try to insert a record and then mount it to select using other connections. The record is empty. See it, I will post the following two sentences:
-- On commit Delete rows indicates that the temporary table is specified by a transaction. After each commit, Oracle truncates the table (delete all rows)
-- On commit preserve rows indicates that the temporary table is specified by the session. When the session is interrupted, Oracle truncates the table.
Conflict issues should not be considered.

A temporary table only saves the data used by the current session. The data only exists during the transaction or session.

Use the create global temporary table command to create a temporary table. For a temporary table of the transaction type,
Data only exists during the transaction. For temporary tables of the session type, data exists during the session.

The session data is private to the current session. Each session can only view and modify its own data. DML lock not added
Temporary table data. The following statement controls the existence of rows.

● On commit Delete rows table name rows are only visible during the transaction

● On commit preserve rows table name rows are visible throughout the session

You can create indexes, views, and starters for temporary tables. You can use the Export and Import tools to import and export tables.
Definition, but data cannot be exported. The table definition is visible to all sessions.

For example:

CREATE GLOBAL TEMPORARY TABLE TEMP_TAB1(table_name VARCHAR2(20),primary_key VARCHAR2(100),field VARCHAR2(1000))ON COMMIT PRESERVE ROWS;

 

 

CREATE GLOBAL TEMPORARY TABLE TEMP_TAB2(table_name VARCHAR2(20),primary_key VARCHAR2(100),field VARCHAR2(1000))ON COMMIT DELETE ROWS;