ORACLE temporary table-ON COMMIT PRESERVE ROWS

ORACLE temporary table-on commit preserve rows temporary table: Like a common table, it has a structure, but its data management is different. The temporary table stores the intermediate result set of transactions or sessions, the data saved in the temporary table is only visible to the current session, and the data of other sessions is invisible to all sessions, even if other sessions are submitted. Temporary tables do not have concurrent behaviors because they are independent of the current session. When creating a temporary table at www.2cto.com, ORACLE only creates the table structure (defined in the data dictionary) and does not initialize the memory space. When a session uses a temporary table, ORALCE allocates a piece of memory space from the temporary tablespace of the current user. That is to say, the temporary table is allocated storage space only when data is inserted into the temporary table. Temporary tables are divided into transaction-level temporary tables and session-level temporary tables. The transaction-level temporary table is only valid for the current transaction. It is specified by the on commit delete rows statement. The session-level temporary table is valid for the current session. It is specified by the on commit preserve rows statement. Example of www.2cto.com usage (in SCOTT mode): create global temporary table session_temp_tab on commit preserve rows as select * FROM emp WHERE 1 = 2; the on commit preserve rows statement specifies that the created temporary table is a session-level temporary table. Data in the temporary table remains in until we disconnect or manually execute DELETE or TRUNCATE, only the current session can be seen, but not other sessions. Create global temporary table transaction_temp_tab on commit delete rows as select * FROM emp WHERE 1 = 2; on commit delete rows statement specifies that the created temporary table is a transaction-level temporary table, the data always exists before COMMIT or ROLLBACK. After the transaction is committed, the data in the table is automatically cleared. Www.2cto.com insert into session_temp_tab select * from emp; insert into transaction_temp_tab select * from emp; SQL> select count (*) from session_temp_tab; COUNT (*) ---------- 14 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 www.2cto.com when COMMIT is followed, the data in the temporary transaction-level table is automatically cleared. Therefore, when querying again, the result is 0. SQL> disconnect; not logged on SQL> connect scott/tiger; Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 Connected as scott SQL> select count (*) from transaction_temp_tab; COUNT (*) ---------- 0 SQL> select count (*) from session_temp_tab; COUNT (*) ---------- 0 when the connection is closed and then reconnected, the data in the session-level temporary table is also automatically deleted. Www.2cto.com