oracle-temporary table

Migration time: June 1, 2017 17:49:39author:marydoncreatetime--2017 January 20 09:57:48 Second, Oracle temporary table case:

--Create Table Three directory monitoring stored procedure temp TableCreateGlobalTemporary TableL_diaitem_error (Forgid Number,--Organization IDForgnameVARCHAR2( the),--Organization NameNccw Number,--Name Error numberWdz Number            --No Control Count) on Commit DeleteRows--clears the table data when the transaction commits--Insert a piece of dataINSERT  intoL_diaitem_error (Nccw,wdz)SELECT 1,2  fromDUAL;--querying the table dataSELECT *  fromL_diaitem_error

Description

After executing the INSERT statement, if there is no commit, execute the query statement, the table has data, if the transaction is committed, execute the query statement, the table has no data.

Detailed Introduction

Reference Link: http://www.cnblogs.com/vigarbuaa/archive/2012/06/04/2534797.html

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

(1) Session-specific temporal tables

Grammar:

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

(2) Transaction-specific temporal tables

Grammar:

CREATE GLOBAL Temporary <TABLE_NAME> (<column specification>) on COMMIT DELETE ROWS;

Example:

CREATE GLOBAL Temporary TABLE mytemptable

Difference:

　　　　On COMMIT DELETE ROWS indicates that the staging table is a transaction specification, and Oracle truncates the table (clears the table data) after each commit

　　　　On COMMIT DELETE ROWS indicates that the staging table is a transaction specification, and Oracle truncates the table (clears the table data) after each commit

Scope:

Create a temporary table by creating the GLOBAL temporary Table command,
For a temporary table of a transaction type, the data is only present during this transaction, and for a session type temporary table, the data exists during this session.

Features of temporary tables:

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

(1) The independence of multi-user operation: For different users who use the same temporary table, Oracle assigns a separate Temp Segment, which avoids multiple users crossing the same temporary table operation, thus guaranteeing the concurrency and independence of multiple user operations;

(2) Temporary data: Since it is a temporary table, as the name implies, the data stored in the table is temporary. Oracle automatically truncate data out based on the parameters that you specified when you created the temporary table (on commit Delete rows/on commit Preserve Rows).

oracle-temporary table