Oracle temporary tables temporary table

Although we often use temporary tables in daily database development and maintenance, we may still have a poor understanding of temporary tables.

I found a good post on the Internet today to share with you.

 

Source: http://www.matrix.org.cn/thread.shtml? Topicid = 31492 & forumid = 36

In Oracle8i or a later version, you can create two types of temporary tables:
1. Session-specific temporary table
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.

Temporary tables temporary table
1 Overview
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 save the data required in a transaction. When the session exits or the user commits a commit and rollback transaction, the data in the temporary table is automatically cleared,
However, the structure and metadata of the temporary table are stored in the user's data dictionary.
Temporary tables are only supported in Oracle8i and later products.
2. Details
Oracle temporary tables are divided into session-level temporary tables and transaction-level temporary tables.
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.
Transaction-level temporary table means that the data in the temporary table only exists in the transaction lifecycle. When a transaction ends (commit or rollback), Oracle automatically clears the data in the temporary table.
The data in the temporary table is only valid for the current session. Each session has its own temporary data and cannot access the data in the temporary table of other sessions. Therefore,
Temporary tables do not require DML locks. When a session ends (the user Exits normally and the user does not exit normally) or a transaction ends
Execute the truncate statement to clear the data in the temporary table. However, the data in the temporary table of other sessions is not cleared.
You can index a temporary table and create a view on the temporary table. Similarly, the index on the temporary table is temporary and only valid for the current session or transaction.
Temporary tables can have triggers.
3. Create a temporary table
The definition of a temporary table is visible to all sessions, but the data in the table is only valid for the current session or transaction.
Creation method:
1) on commit Delete rows defines how to create a transaction-level temporary table.
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
2 (startdate date,
3 enddate date,
4 class char (20 ))
5 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 how 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:

Session 1:
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 ?? -03 17-1 ?? -03 session temperary

Session 2:

SQL> select * From permernate;

A
----------
1
2

SQL> select * From admin_work_area;

Row not selected.

Session 2 cannot see the data in the temporary table in session 1.

4 similarities and differences between Oracle temporary tables and SQL Server temporary tables

SQL Server temporary table
You can also create a temporary table. Temporary tables are similar to permanent tables, but temporary tables are stored in tempdb. They are automatically deleted when they are no longer used.
There are two types of temporary tables: local and global, both of which have different names, visibility, and availability. The name of the local temporary table starts with a single digit;
They are only visible to the current user connection; when the user is from Microsoft? SQL Server? 2000 the instance is deleted when it is disconnected. The name of the global temporary table is a mathematical symbol.
(##) After the table is created, it is visible to all users and deleted when all users that reference the table are disconnected from SQL Server.
For example, if you create a table named employees, anyone who has the security permission to use the table in the database can use the table unless it has been deleted.
If you create a local temporary table named # employees, you can only perform operations on the table and delete the table when the connection is disconnected. If you create a global temporary table named # employees
Any user in the data table can perform operations on the table. If the table is not used by other users after you create it, delete it when you disconnect it. If the table is created
Otherwise, SQL Server deletes the table after all users are disconnected.
Different:
1. the SQL Server temporary table is a "memory table", which is stored in the memory. The Oracle temporary table is stored in the data dictionary unless drop table is executed.
2. SQL Server temporary tables do not support transaction-level functions similar to Oracle temporary tables.
3. the SQL Server local temporary table (#) is similar to the Oracle session-level temporary table, but Oracle does not delete the table when the session exits.
4. SQL Server's global temporary table (#) means that multiple connections share the same piece of memory. When no pointer references the memory area, SQL Server Automatically releases the global temporary table.
5 because oracle is not a database in memory, if Oracle frequently creates and deletes temporary tables like SQL Server, it will definitely affect the performance.
Therefore, Oracle retains the definition of the temporary table until you drop 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,
Add columns in the table that can uniquely identify the user. Use triggers and views. When the user exits, delete the data in the corresponding table based on the unique information of the login user.
This method brings a certain amount of load to Oracle.