Detailed Explanation and Examples of Temporary Tables in Oracle

In Oracle8In version i or above, the following two types of temporary tables can be created:

1. Session-specific temporary table

CREATE GLOBAL TEMPORARY <TABLE_NAME> ( <column specification> )
ON COMMIT PRESERVE ROWS;　

2. Transaction-specific temporary table

CREATE GLOBAL TEMPORARY <TABLE_NAME> ( <column specification> )
ON COMMIT DELETE ROWS;
CREATE GLOBAL TEMPORARY TABLE MyTempTable　　

The temporary table that is created exists, but if you try to insert a record and then log in to select with another connection, the record is empty. Got it? I'll repost the following two sentences:

--ON COMMIT DELETE ROWS indicates that the temporary table is transaction-specific, and ORACLE will truncate the table (delete all rows) after each commit.
--ON COMMIT PRESERVE ROWS indicates that the temporary table is session-specific, and when the session is interrupted, ORACLE will truncate the table.

The issue of conflict does not need to be considered at all.

Temporary tables only save the data used by the current session (session), and the data exists only during the transaction or session period.

A temporary table is created by the CREATE GLOBAL TEMPORARY TABLE command. For transaction-type temporary tables, data exists only during the transaction period, and for session-type temporary tables, data exists during the session period.

The data of the session is private to the current session. Each session can only see and modify its own data. DML locks are not added to the data of temporary tables. The following statements control the existence of rows.

● The rows of table name are visible only during the transaction period
● ON COMMIT PRESERVE ROWS makes the table rows visible throughout the session

Indexes, views, and triggers can be created on temporary tables, and the definitions of the tables can be imported and exported using the export and import tools, but the data cannot be exported. The table definition is visible to all sessions.

Temporary Tables

1Introduction

In addition to saving permanent tables, ORACLE database can also create temporary tables (temporary tables). These temporary tables are used to save the data of a session SESSION,

Or stored in the data needed in a transaction. When the session exits or the user submits a commit and rollback transaction, the data in the temporary tables is automatically cleared,
However, the structure and metadata of temporary tables are still stored in the user's data dictionary.

Temporary tables are only supported in oracle8i and above products are supported.

2Detailed introduction

Oracle temporary tables are divided into session-level temporary tables and transaction-level temporary tables.

Session-level temporary tables refer to the data in temporary tables that only exists within the session lifecycle. When a user logs out and the session ends, Oracle automatically clears the data in the temporary tables.
Transaction-level temporary tables refer to the data in temporary tables that only exists within the transaction lifecycle. When a transaction ends (commit or rollback), Oracle automatically clears the data in the temporary tables.

The data in temporary tables is only valid for the current Session. Each Session has its own temporary data, and it is not possible to access the data in other Sessions' temporary tables. Therefore,

Temporary tables do not require DML locks. When a session ends (either normal logout, abnormal logout, or ORACLE instance crash) or a transaction ends, Oracle clears the data for this session's

The TRUNCATE statement is used to clear the data of temporary tables. However, it will not clear the data in other sessions' temporary tables.

You can index temporary tables and create views based on them. Similarly, the indexes created on temporary tables are also temporary and are only valid for the current session or transaction.

Temporary tables can have triggers.

3Establishing temporary tables

The definition of temporary tables is visible to all sessions SESSION, but the data in the table is only valid for the current session or transaction.

Method of establishment:

1) ON COMMIT DELETE ROWS defines the method of creating transaction-level temporary tables.

CREATE GLOBAL TEMPORARY TABLE admin_work_area 
(startdate DATE, 
enddate DATE, 
class CHAR(20)) 
ON COMMIT DELETE ROWS; 

EXAMPLE:

SQL> CREATE GLOBAL TEMPORARY TABLE admin_work_area 
 (startdate DATE, 
enddate DATE, 
class CHAR(20)) 
ON COMMIT DELETE ROWS; 
SQL> create table permernate( a number); 
SQL> insert into admin_work_area values(sysdate,sysdate, 'temperary table ‘); 
SQL> insert into permernate values(1); 
SQL> commit; 
SQL> select * from admin_work_area; 
SQL> select * from permernate; 
A 
1 

2)ON COMMIT PRESERVE ROWS defines the method to create a session-level temporary table.

CREATE GLOBAL TEMPORARY TABLE admin_work_area 
(startdate DATE, 
enddate DATE, 
class CHAR(20)) 
ON COMMIT PRESERVE ROWS; 
EXAMPLE:

Session1:

SQL> drop table admin_work_area; 
SQL> CREATE GLOBAL TEMPORARY TABLE admin_work_area 
2 (startdate DATE, 
3 enddate DATE, 
4 class CHAR(20)) 
5 ON COMMIT PRESERVE ROWS; 
SQL> insert into permernate values(2); 
SQL> insert into admin_work_area values(sysdate,sysdate, 'session temperary '); 
SQL> commit; 
SQL> select * from permernate;
A 
---------- 
1 
2
SQL> select * from admin_work_area;
STARTDATE ENDDATE CLASS 
---------- ---------- -------------------- 
17-1;63;63; -03 17-1;63;63; -03 session temperary

Session2:

SQL> select * from permernate;
A 
---------- 
1 
2
SQL> select * from admin_work_area;

No rows selected.

Session2Invisible session1. Data in the temporary table

4 Differences between ORACLE temporary tables and SQLSERVER temporary tables

SQL SERVER temporary tables

Temporary tables can also be created. Temporary tables are similar to permanent tables, but they are stored in tempdb and are automatically deleted when no longer in use.
There are two types of temporary tables: local and global, which are different in terms of name, visibility, and availability. The name of a local temporary table is prefixed with a single number symbol (#);
They are only visible to the current user connection; when a user disconnects from Microsoft63; SQL Server63; 2000 is deleted when the instance is disconnected. The name of a global temporary table is prefixed with a mathematical symbol

(##) prefixed, it is visible to any user after creation, and it is deleted when all users who refer to the table disconnect from SQL Server.
For example, if a table named employees is created, anyone who has the security permission to use the table in the database can use the table, unless it has been deleted.
If a local temporary table named #employees is created, only you can perform operations on the table, and the table is deleted when the connection is disconnected. If a global temporary table named ##employees is created

Any user in the data table can perform operations on the table. If no other user uses the table after you create it, the table will be deleted when you disconnect. If the table is used by other users after you create it

If there are other users using it later, SQL Server will delete the table after all users disconnect.

Differences:

1. SQL SERVER temporary tables are a 'memory table', and the table is stored in memory. ORACLE temporary tables will retain the table definition in the data dictionary unless DROP TABLE is executed.

2. SQL SERVER temporary tables do not have the same transaction-level function as ORACLE temporary tables.

3 SQL SERVER's local temporary table (#) is similar to ORACLE's session-level temporary table, but ORACLE does not delete the table when the session exits.

4 SQL SERVER's global temporary table (##) refers to multiple connections sharing the same memory area. When there are no pointers referencing the memory area, SQL SERVER automatically releases the global temporary table.

5 Since ORACLE is not a memory database, if ORACLE frequently creates and deletes temporary tables like SQL SERVER, it will definitely affect performance.
Therefore, ORACLE will retain the definition of temporary tables until the user drops the table.

6 In ORACLE, if multiple users need to share a table (similar to SQL SERVER's global temporary table ##), you can use permanent tables.

And add some columns that can uniquely identify users in the table. Use triggers and views. When a user logs out, delete the corresponding data in the table based on the unique information of the login user.

This method brings a certain amount of load to ORACLE.

This is the compilation of information about Oracle temporary tables, and we will continue to supplement related information. Thank you for your support to our website!