Oracle Temp Table

Briefly

drop table tmp_qry_cus_186mcc_highuser_d;

Create global temporary table Tmp_qry_cus_186mcc_highuser_d

(

USER_ID Number (15)

) on commit preserve rows;

This temporary table does not occupy the table space, and the different sessions can not see each other's data in the table after the end of the data is automatically emptied, if delete ROWS is selected, the data will be emptied at the time of submission, preserve will always be the end of the session

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

(1) Session-specific temporal tables

CREATE GLOBAL Temporary <TABLE_NAME>

(

<column specification>

)

On COMMIT PRESERVE ROWS;

(2) Transaction-specific temporal tables

CREATE GLOBAL Temporary <TABLE_NAME>

(

<column specification>

)

On COMMIT DELETE ROWS;

The temporary table created by the CREATE GLOBAL temporary table mytemptable is present, but you try to insert a record and then use a different connection to mount the Select, the record is empty,

Understand, I put the following two words again:--on commit DELETE rows indicates that the temporary table is a transaction specification, after each commit Oracle will truncate the table (delete all rows)--on commit PRESERVE Rows Description Temp Table is a session designation, Oracle truncates the table when the session is interrupted. The problem of conflict is not to be considered.

A temporary table is just the data that is used to save 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 transaction type, the data is only present during the transaction, and for the session type temporary table, the data exists during the session.

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 COMMIT DELETE rows table name row is only visible during transaction

On COMMIT PRESERVE rows table name rows are visible throughout the session

You can create indexes, views, and departures for temporary tables, and 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.