Oracle 临时表总结===

Oracle 临时表总结

什么是临时表？

临时表只存在于某个会话或事务，不产生redo 。

临时表主要用于一些需要缓存结果的应用中。例如，在一个学生选课系统中，学生暂时的选课表信息保存到一个临时表中，此时的选课信息只对学生自己可见，当学生确定选课内容后，系统会将临时表中的信息转存到普通表，然后自动清除临时表中的内容。

与普通表一样，临时表的结构信息也是保存到数据字典中，当第一次向临时表中插入数据后，系统机会分配对应的临时段，当事务或者session结束后，会释放临时段空间。

默认情况下，临时表中的信息是保存到用户的默认临时表空间中，也可以在创建临时表的时候指定具体的表空间

临时表中的内容只有session内可见，但是临时表的定义信息是全局可见的，可以通过以下语句查询一个表是否为临时表

查看是否为临时表以及临时表的周期(transaction delete on commit, session preserve on commit )

select table_name,LOGGING,TEMPORARY,DURATION from user_tables

where TEMPORARY='Y';

MY_TEMP_TABLE NO Y SYS$TRANSACTION

PARTITION_TEST N

T_COMPRESS YES N

EMP YES N

ORIGINAL_INDEX_TAB YES N

T_UNCOMPRESS YES N

如何创建一个临时表？

创建临时表的语句如下：

CREATE GLOBAL TEMPORARY TABLE admin_work_area (startdate DATE, enddate DATE, class CHAR(20))

ON COMMIT DELETE ROWS;

创建临时表有两种：delete rows, preserve rows

delete rows: 表示一个事务结束后，临时表中的数据将会被自动清除

preserve rows: 表示事务结束后，依然保留临时表中的内容，直到session结束后，自动清除临时表中的内容。

delete rows 临时表示例：

SQL> CREATE GLOBAL TEMPORARY TABLE admin_work_area 2 (startdate DATE, 3 enddate DATE, 4 class CHAR(20)) 5 ON COMMIT DELETE ROWS; Table created.

SQL> insert into ADMIN_WORK_AREA values(sysdate,sysdate+100,'Math'); 1 row created. SQL> select count(*) from ADMIN_WORK_AREA;

COUNT(*) ---------- 1 SQL> commit; Commit complete. SQL> select count(*) from ADMIN_WORK_AREA; COUNT(*) ---------- 0

preserve rows 示例

SQL> CREATE GLOBAL TEMPORARY TABLE admin_work_area_preserve 2 (startdate DATE, 3 enddate DATE,

4 class CHAR(20)) 5 ON COMMIT preserve ROWS; SQL> insert into ADMIN_WORK_AREA_preserve values(sysdate,sysdate+100,'Math'); 1 row created. SQL> select count(*) from ADMIN_WORK_AREA_preserve; COUNT(*) ---------- 1 SQL> commit; Commit complete. SQL> select count(*) from ADMIN_WORK_AREA_preserve; COUNT(*) ----------

1 SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options you have mail in /var/spool/mail/oracle oracle@beiora01a beiora01:/home/oracle> sqlplus user01/**** SQL*Plus: Release 11.2.0.3.0 Production on Wed Aug 6 00:54:38 2014 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> show user

USER is \"USER01\" SQL> select count(*) from ADMIN_WORK_AREA_preserve; COUNT(*) ---------- 0

通过如下语句可以查询临时表的属性信息：

SQL> select table_name,LOGGING,TEMPORARY,DURATION from user_tables where TEMPORARY='Y'; TABLE_NAME LOG T DURATION ------------------------------ --- - --------------- MY_TEMP_TABLE NO Y SYS$TRANSACTION ADMIN_WORK_AREA NO Y SYS$TRANSACTION ADMIN_WORK_AREA_PRESERVE NO Y SYS$SESSION

临时表空间上也可以创建索引，周期与对应的临时表相同。创建临时表的索引时，确

保临时表不在使用中

SQL> create index idx_temp_admin on ADMIN_WORK_AREA_preserve(startdate); create index idx_temp_admin on ADMIN_WORK_AREA_preserve(startdate) * ERROR at line 1: ORA-14452: attempt to create, alter or drop an index on temporary table already in use SQL> commit; Commit complete. SQL> insert into ADMIN_WORK_AREA_preserve values(sysdate,sysdate+100,'Math'); 1 row created.