Oracle Temporary Table Transfer Learning

Ext.: http://www.2cto.com/database/201210/163979.html

Temporary tables: Like normal tables, there is a structure, but the management of the data is not the same, the temporary table stores the transaction or the intermediate result set of the session, the data saved in the temporary table is only visible to the current session, all sessions do not see the data of the other session, even if the other session submitted, also do not see. Temporary tables do not have concurrency behavior because they are independent of the current session.

      when creating a temporary table, Oracle creates only the structure of the table (defined in the data dictionary), does not initialize the memory space, and when a session uses a temporary table, Oralce allocates a piece of memory space from the temporary table space of the current user. This means that the temporary table is allocated storage space only when data is inserted into the staging table.    temporary tables sub-transaction-level temporary tables and session-level temporary tables. The   transaction-level temporary table is valid only for the current transaction, as specified by the statement: on COMMIT DELETE ROWS statement.   Session-level temporary tables are valid for the current session, as specified by the statement: on COMMIT PRESERVE rows statement. Example of    usage (in Scott mode):  create GLOBAL temporary TABLE session_temp_tab on COMMIT PRESERVE ROWS as SELECT * from The EMP WHERE 1=2; on COMMIT PRESERVE rows statement specifies that the temporary table created is a session-level temporal table, and that the data in the staging table remains in   until we disconnect or manually execute delete or truncate. And only the current session can be seen, other sessions are not visible.   create GLOBAL Temporary TABLE transaction_temp_tab on COMMIT DELETE ROWS as SELECT * from EMP WHERE 1=2;  The on COMMIT DELETE rows statement specifies that the temporary table created is a transaction-level temporary table, which persists until commit or rollback, and the data in the table is automatically purged after the transaction commits.  insert to Session_temp_tab SELECT * from emp;  insert to Transaction_temp_tab select * from EMP;  sql& Gt Select COUNT (*) from Session_temp_tab;   count (*)  ----------         14 & nbsp Sql> SeleCT Count (*) from Transaction_temp_tab;   count (*)  ----------         14  Sql> commit;  commit complete  sql> Select COUNT (*) from Session_temp_tab;   COUNT (*)  ----------         14  sql> SELECT COUNT (*) from TRANSACTION_TEMP_ tab;   count (*)  ----------          0    When the data in the transaction level temporary table is cleared automatically after commit, the result is 0; sql> disconnect when querying again;  not logged on  sql> Connect Scott/tiger;  connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 connected as SCOTT &N Bsp Sql> Select COUNT (*) from Transaction_temp_tab;   count (*)  ----------          0  SQL> Select COUNT (*) from Session_temp_tab;   count (*)  ----------           0  The data in the session-level staging table is automatically deleted when you reconnect after disconnecting.

Oracle temporary table for learning