oracle临时表

oracle中有事务级临时表和会话级临时表，说明如下：

 --DELETE ROWS：事务级临时表：默认， 数据在 Transaction 期间有效，一旦COMMIT后，数据就被自动 TRUNCATE 掉了；
 --PRESERVE ROWS ：会话级临时表，数据在 Session 期间有效，一旦关闭了Session 或 Log Off 后，数据就被 ORACLE 自动 Truncate 掉。
临时表中的数据只在会话或者事务级别可见。不同用户可以使用同一个临时表，但是看到的都是各自的数据。
临时表存放在 Schema 所指定的临时表空间中。

临时表的创建

CREATE GLOBAL TEMPORARY TABLE tablename (columns) [ ON COMMIT PRESERVE | DELETE ROWS ]　

临时表的使用

1、临时表上可以创建索引、视图等对象。
SQL> CREATE GLOBAL TEMPORARY TABLE test (id int,name varchar2(100))  ON COMMIT  DELETE ROWS;

Table created.

SQL> create index i_test_id on test(id);

Index created.

SQL> create view v_test as select * from test;

View created.
2、临时表dml操作会生成redo
SQL> set autot on stat
SQL> insert into test select object_id,object_name from dba_objects;
86409 rows created.

Statistics
----------------------------------------------------------
         52  recursive calls
       7705  db block gets
       2575  consistent gets
        569  physical reads
    3339676  redo size
        839  bytes sent via SQL*Net to client
        818  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      86409  rows processed

SQL> commit;

Commit complete.

SQL> select * from test;

no rows selected

其他说明

1、与永久表不同，临时表及其索引在创建时不会自动分配段。相反，段是在执行第一次INSERT(或CREATE TABLE AS SELECT)时分配的。因此，如果在第一次INSERT操作之前执行SELECT、UPDATE或DELETE操作，则表看起来为空
Unlike permanent tables, temporary tables and their indexes do not automatically allocate a segment when they are created. Instead, segments are allocated when the first INSERT (or CREATE TABLE AS SELECT) is performed. Therefore, if a SELECT, UPDATE, or DELETE is performed before the first INSERT, then the table appears to be empty.
2、如果回滚事务，则输入的数据将丢失，但表定义将保持不变。
DDL operations (except TRUNCATE) are allowed on an existing temporary table only if no session is currently bound to that temporary table.
只有当前没有会话绑定到现有临时表时，才允许在现有临时表上进行DDL操作(TRUNCATE除外)。
If you rollback a transaction, the data you entered is lost, although the table definition persists.
3、特定于事务的临时表一次只允许一个事务。如果在单个事务范围中有多个自治事务，则每个自治事务只有在前一个事务提交后才能使用表。
A transaction-specific temporary table allows only one transaction at a time. If there are several autonomous transactions in a single transaction scope, each autonomous transaction can use the table only as soon as the previous one commits.
4、由于临时表中的数据在定义上是临时的，因此在系统发生故障时，临时表数据的备份和恢复不可用。为了应对这种故障，您应该开发用于保存临时表数据的替代方法。
Because the data in a temporary table is, by definition, temporary, backup and recovery of temporary table data is not available in the event of a system failure. To prepare for such a failure, you should develop alternative methods for preserving temporary table data.

参考文档

https://docs.oracle.com/cd/E11882_01/server.112/e25494/tables.htm#ADMIN11633