Syntax for creating temporary tables in Oracle

In Oracle8i or a later version, you can create two types of temporary tables: 1. Session-specific TEMPORARY table create global temporary <TABLE_NAME> (<column specification>) on commit preserve rows; 2. TEMPORARY table www.2cto.com create global temporary <TABLE_NAME> (<column specification>) on commit delete rows;
Although the temporary table created by create global temporary table MyTempTable exists, you can try to insert a record and then attach it to select using other connections. The record is empty. See it, I will post the following two statements: -- ON COMMIT DELETE ROWS indicates that the temporary table is specified by a transaction. After each COMMIT, ORACLE will cut off the table (DELETE all ROWS) -- on commit preserve rows indicates that the temporary table is specified by the session. When the session is interrupted, ORACLE truncates the table. The temporary table only saves the data used by the current session. The data only exists during the transaction or session. Use the create global temporary table command to CREATE a temporary table. For a temporary table of the transaction type, data only exists during the transaction. For a temporary table of the session type, data exists during the session.
The session data is private to the current session. Each session can only view and modify its own data. The DML lock is not added to the data in the temporary table. The following statement controls the existence of rows. Www.2cto.com ● on commit delete rows table name ROWS are only visible during the transaction ● on commit preserve rows table name ROWS can be seen throughout the session to create indexes, views, and starters for temporary tables, you can use the export and import tools to import and export table definitions, but cannot export data. The table definition is visible to all sessions. Temporary Tables temporary table 1 Introduction In addition to saving permanent tables, the ORACLE database can also create Temporary Tables temporary tables. These temporary tables are used to save the data of a SESSION or the data required in a transaction. When the session exits or the user commits a commit or rollback transaction, the data in the temporary table is automatically cleared, but the structure and metadata of the temporary table are also stored in the user's data dictionary. Temporary tables are only supported in oracle8i and later products.
2. The Oracle temporary tables are divided into session-level temporary tables and transaction-level temporary tables. A session-level temporary table means that the data in the temporary table only exists in the session lifecycle. When the user exits the session, Oracle automatically clears the data in the temporary table. Transaction-level temporary table means that the data in the temporary table only exists in the transaction lifecycle. When a transaction ends (commit or rollback), Oracle automatically clears the data in the temporary table. The data in the temporary table is only valid for the current Session. Each Session has its own temporary data and cannot access the data in the temporary table of other sessions. Therefore, DML locks are not required for temporary tables. when a session ends (the user Exits normally and the user does not exit normally and the ORACLE instance crashes) or a transaction ends, Oracle executes the TRUNCATE Statement on the table in the session to clear the temporary table data. however, data in other temporary session tables is not cleared. you can index a temporary table and create a view based on the temporary table. similarly, indexes created on temporary tables are temporary and only valid for the current session or transaction. temporary tables can have triggers. 3. The definition of a temporary table is visible to all sessions, but the data in the table is only valid for the current SESSION or transaction. creation Method: www.2cto.com 1) on commit delete rows defines the method for creating a transaction-level temporary table. create global temporary table admin_work_area (startdate DATE, enddate DATE, class CHAR (20) on commit delete rows; E XAMPLE: SQL> create global temporary table admin_work_area2 (startdate DATE, 3 enddate DATE, 4 class CHAR (20) 5 on commit delete rows; SQL> create table permernate (a number ); SQL> insert into admin_work_area values (sysdate, sysdate, 'temperary table'); SQL> insert into permernate values (1); SQL> commit; SQL> select * from admin_work_area; SQL> select * from permernate; A12) ON COMMIT PRESERVE ROWS defines the creation of session-level temporary tables. Method. create global temporary table admin_work_area (startdate DATE, enddate DATE, class CHAR (20) on commit preserve rows; EXAMPLE: session 1: SQL> drop table admin_work_area; SQL> create global temporary table admin_work_area2 (startdate DATE, 3 enddate DATE, 4 class CHAR (20) 5 on commit preserve rows; SQL> insert into permernate values (2 ); SQL> insert into admin_work_area values (sysdate, sysdate, 'session temperary '); SQL> commit; SQL> select * from permernate; A----------12 SQL> select * from admin_work_area; STARTDATE ENDDATE CLASS ---------- ------------------ 17-1 ?? -03 17-1 ?? -03 session temperary www.2cto.com session 2: SQL> select * from permernate; A----------12 SQL> select * from admin_work_area; row not selected. session 2 cannot see the data in the temporary table in session 1.