Oracle Temp Table

In Oracle8i or later, you can create the following two types of temporary tables:
1. Session-specific Temporal tables

CREATE Temporary < table_name><column specification>onCOMMIT PRESERVE ROWS;

2. Transaction-specific temporal tables

CREATE Temporary < table_name><column specification>onCOMMIT  DELETECREATEtemporaryTABLE

The temporary table built by

is present, but you try to insert a record and then use a different connection to go to the Select, the record is empty, understand, I put the following two words again:
--on COMMIT DELETE ROWS Description Temp table is a transaction specified , Oracle truncates the table after each commit (delete all rows)
--on COMMIT PRESERVE rows indicates that the staging table is a session designation and Oracle truncates the table when the session is interrupted. The problem of the
conflict is not to be considered. The

Temporary table simply saves the data that is used for the current session, and the data exists only during a transaction or session.

Creates a temporary table from the Create GLOBAL temporary table command, and for a temporary table of the transaction type, the
data is only present during the transaction, and the data exists during the session for the session type temporary table. 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 the data on the
staging table. The following statement controls the existence of a row.

on the Commit DELETE rows table name row is only visible during the transaction
on COMMIT PRESERVE rows are visible throughout the session

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

Temporary tables temporary table
1 Introduction
Oracle Database You can also create a temporary table temporary tables in addition to saving a permanent table. These temporary tables are used to hold data for session sessions,
or data to be saved in a transaction. When the session exits or the user submits a commit and rolls back the rollback transaction, the data for the temporary table is automatically emptied, and the
but the structure of the temporary table and the metadata are also stored in the user's data dictionary. The
temporary table is only supported in oracle8i and above products.
2 Details
The Oracle staging table is divided into session-level temporary tables and transaction-level temporary tables. The
Session-level temporary table refers to the data in the staging table that exists only during the session life cycle, and when the user exits the end of the session, Oracle automatically clears the data from the staging table. The
Transaction-level temporary table refers to the data in the staging table that exists only during the transaction life cycle. When a transaction ends (commit or rollback), Oracle automatically clears the data from the staging table. The data in the
temporary table is valid only for the current session, each session has its own temporary data, and it cannot access data from the temporary table of the other session. Therefore, the
temporary table does not require a DML lock. When a session ends (the user exits gracefully when the user quits abnormally) or the end of a transaction, Oracle executes the TRUNCATE for this session's
table Statement to empty the temporary table data. The data in the other session staging table is not emptied.
You can index temporary tables and build views on temporary tables. Similarly, indexes built on temporary tables are temporary and valid only for the current session or transaction. A
temporary table can have triggers.
3 Create temporary table
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.
Setup method:
1) on COMMIT DELETE ROWS defines a method for establishing a transaction-level temporary table.

CREATE Temporary TABLE  CHAR(onCOMMITDELETE 

EXAMPLE:

Sql> CREATEGLOBALTemporary TABLEAdmin_work_area(StartDate DATE,EndDate DATE,ClassCHAR( -))  on COMMIT DELETEROWS; SQL> Create TablePermernate (A Number); SQL> Insert  intoAdmin_work_areaValues(Sysdate,sysdate,'temperary Table'); SQL> Insert  intoPermernateValues(1); SQL> Commit; SQL> Select *  fromAdmin_work_area; SQL> Select *  frompermernate; A1 

2) on COMMIT PRESERVE ROWS defines a method for creating a session-level temporary table.

CREATE Temporary TABLE  CHAR(onCOMMIT  PRESERVE ROWS; EXAMPLE:

Session 1:

Sql> Drop TableAdmin_work_area; SQL> CREATEGLOBALTemporary TABLEAdmin_work_area2(StartDate DATE,3EndDate DATE,4ClassCHAR( -)) 5  on COMMITPRESERVE ROWS; SQL> Insert  intoPermernateValues(2); SQL> Insert  intoAdmin_work_areaValues(Sysdate,sysdate,'Session Temperary'); SQL> Commit; SQL> Select *  frompermernate; A---------- 1 2SQL> Select *  fromAdmin_work_area; StartDate ENDDATE CLASS---------- ---------- --------------------  --1??-Geneva  --1??-GenevaSession Temperary

Session 2:

SQL>Select*from--12sql  >Select* from admin_work_area; row not selected.

Session 2 does not see data for temporary tables in session 1.

4 Similarities and differences between Oracle temporal tables and SQL Server temp 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 are 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 only visible to the current user connection, and when the user is from Microsoft? SQL Server? 2000 the instance is removed when the connection is disconnected. The name of the global temporary table is in mathematical notation
(# #), which 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 operations on the table and delete the table when you disconnect. 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 being used by other users after you create it, the table is deleted when you disconnect. If the table is created in your
SQL Server deletes the table after all users are disconnected after it is used by other users
Different:
1. The SQL Server staging table is a "memory table" in which the table is stored in memory. The table definition remains in the data dictionary unless the drop table is executed by the Oracle temp table.
2. SQL Server temporary tables do not have functionality similar to those on the Oracle Temp table transaction level.
3 SQL Server local temporary tables (#) are similar to Oracle's session-level temporary tables, but Oracle does not delete tables when the session exits.
4 The Global temporary table (# #) for SQL Server refers to multiple connections that share the same piece of memory. SQL Server automatically frees the global temporary table when no pointer references the region of memory.
5 because Oracle is not an in-memory database. Therefore, if Oracle-like SQL SERVER frequently creates and deletes temporary tables, it must affect performance.
Therefore, Oracle retains the definition of the temporary table until the User drop table.
6 in Oracle, if more than one user is required to share a table (a global temporary table like SQL Server # # #). You can use a permanent table,
and add some columns in the table that uniquely identify the user. Take advantage of triggers and views. When the user exits, the data in the corresponding table is deleted based on the unique information of the logged-on user.
This approach brings a certain amount of load to Oracle.

Oracle Temp Table