Oracle temporary tables and examples _oracle

In Oracle8i or later versions, you can create the following two types of temporary tables:

1. Session-Specific temporary tables

CREATE GLOBAL Temporary <TABLE_NAME> (<column specification>)
On COMMIT PRESERVE ROWS;

2. Transaction-specific temporary tables

CREATE GLOBAL Temporary <TABLE_NAME> (<column specification>)
On COMMIT DELETE ROWS;
CREATE GLOBAL Temporary TABLE mytemptable

Although the temporary table is in existence, but you try to insert a record and then use a different connection board to select, the record is empty, understand, I put the following two words to paste again:

--on COMMIT Delete rows Description The temporary table is a transaction specified, and Oracle truncates the table after each commit (delete all rows)
--on COMMIT PRESERVE ROWS indicates that the temporary table is a session-specific, and Oracle truncates the table when the session is interrupted.

The problem of conflict is more to be considered.

A temporary table simply saves the data that is used by the current session, and the data exists only during a transaction or session.

Create a temporary table through the Create GLOBAL temporary table command, for temporary tables of transaction types, data exists only during transactions, and for temporary tables of session types, data exists during sessions.

The data for the session is private to the current session. Each session can only see and modify its own data. DML locks are not added to data on temporary tables. The following statement controls the existence of a row.

On COMMIT DELETE rows table name row only visible during transaction
On COMMIT PRESERVE rows table name row is visible throughout the session

You can create indexes on temporary tables, views, and departures, and you can import definitions of exported tables with the export and import tools, but you cannot export data. The definition of a table is visible to all sessions.

Temporary Tables temporary table

1 Introduction

In addition to saving permanent tables, Oracle databases can also create temporary tables temporary tables. These temporary tables are used to hold data for session sessions.

Or save the data that you want in a transaction. When a session exits or a user commits a commit and rolls back a ROLLBACK transaction, the data for the temporary table is automatically emptied,
However, the structure of the temporary table and the metadata are also stored in the user's data dictionary.

Temporary tables are only supported in oracle8i and above products.

2 Detailed Introduction

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

A session-level temporary table refers to data in a temporary table that exists only during the session lifecycle, and when the user exits the session, Oracle automatically clears the data in the temporary table.
A transaction-level temporary table means that data in a temporary table exists only in the transaction lifecycle. When a transaction ends (commit or rollback), Oracle automatically clears the data in the temporary table.

Data in a temporary table is valid only for the current session, each session has its own temporary data, and cannot access data from other temporary tables in the session. So

The temporary table does not require a DML lock. When a session ends (the user normally exits the user from an abnormal exit of the Oracle instance) or a transaction ends, Oracle's

The table executes the TRUNCATE statement to empty the temporary table data. However, the data in the other session temp table will not be emptied.

You can index temporary tables and build views on the basis of temporary tables. Similarly, indexes built on temporary tables are also temporary and are valid only for current sessions or transactions.

Temporary tables can have triggers.

3 Establishment of temporary tables

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

To establish a method:

1 on COMMIT DELETE ROWS defines a method for establishing a transaction-level temporary table.

CREATE GLOBAL Temporary TABLE admin_work_area 
(startdate date, 
enddate date, 
class CHAR (20)) 

EXAMPLE:

sql> CREATE GLOBAL Temporary TABLE admin_work_area 
 (startdate date, 
enddate date, 
class CHAR) 
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 a method for creating session-level temporary tables.

CREATE GLOBAL Temporary TABLE admin_work_area 
(startdate date, 
enddate date, 
class CHAR ()) 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 ()) 
   5 on COMMIT PRESERVE ROWS; 
sql> INSERT INTO permernate values (2); 
sql> INSERT into the 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??-17-1??-session Temperary

Session 2:

Sql> select * from Permernate;

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

sql> select * from Admin_work_area;

No rows were selected.

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

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

SQL Server Temp Table

You can also create temporary tables. Temporary tables are similar to permanent tables, but temporary tables are stored in tempdb and automatically deleted when they are no longer in use.
There are both local and global types of temporary tables, which differ in name, visibility, and availability. The name of the local temporary table begins with a single number sign (#);
They are visible only to the current user connection, when the user is from Microsoft? SQL Server? 2000 instance is deleted when disconnected. The name of the global temporary table is mathematically symbolic

(# #) starts, is visible to any user after it is created, and is deleted when all users referencing the table are disconnected from SQL Server.
For example, if you create a table named employees, anyone who has security permissions 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, only you can perform an operation on the table and delete the table when you disconnect. If you create a global temporary table named # #employees

Any user in the datasheet can perform an action on the table. If the table is not used by another user after you create it, the table is deleted when you disconnect. If the table is created in your

is used by another user, SQL Server deletes the table after all users have disconnected

Different:

1. A SQL Server temp table is a "memory table" that is stored in memory. Oracle Temporary tables The table definition remains in the data dictionary unless the drop table is executed.

2. SQL Server temp tables do not have features similar to the Oracle Temp table transaction level.

3 SQL Server 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 (# #) means that multiple connections share the same piece of memory. SQL Server automatically releases global temporary tables when there is no pointer to the memory area.

5 because Oracle is not an in-memory database. So if Oracle is similar to SQL SERVER's frequent creation and deletion of temporary tables, it will certainly affect performance.
So Oracle retains the definition of the temporary table until the User drop table.

6 in Oracle, if multiple users are required to share a table (SQL Server-like global temporary table # #). You can use a permanent table,

and add some columns to the table that uniquely identify the user. Use triggers and views. When the user exits, the data in the corresponding table is deleted according to the unique information of the logged in user.

This approach brings a certain amount of load to Oracle.

The above is the Oracle temporary table data collation, follow-up continue to supplement the relevant information, thank you for your support of this site!