Oracle全局临时表
目前所有使用Oracle作为数据库支撑平台的应用，大部分是数据量比较庞大的系统，即表的数据量级一般情况下都是在百万级以上。当然，在Oracle中创建分区是一种不错的选择，但是当发现应用有多张表关联的时候，并且这些表大部分都比较庞大，而关联的时候发现其中的某一张或者某几张表关联之后得到的结果集非常小，并且查询得到这个结果集的速度非常快，那么这个时候考虑在Oracle中创建“临时表”。
在创建数据表的时候，如果没有特殊地指明，那么创建的表是一个永久的关系型表，也就是说，这个表中对应的数据，除非是显式地删除，否则表中的数据是永远都存在的。相对应的，在Oracle数据库中，还有一种类型的表，叫做临时表。这个临时表跟永久表最大的区别就是表中的数据不会永远地存在。当一个会话结束或者事务结束的时候，这个临时表中的数据，不用用户自己删除，数据库自己会自动清除。
Oracle的临时表创建之后基本不占用表空间，如果没有指定临时表（包括临时表的索引）存放的表空间，那么插入到临时表的数据是存放在Oracle系统的默认临时表空间中（TEMP），一个系统可能有多个临时表空间。临时表的数据只能存放在临时表空间中。
临时表的数据只是在事务期间存在，对于会话类型的临时表，数据在会话期间存在，会话的数据对于当前会话私有，每个会话只能看到并修改自己的数据。DML锁不会加到临时表的数据上。可以对临时表创建索引、视图、触发器，可以用exp和imp工具导入导出表的定义，但是不能导出数据。
（一）临时表的特点
l 多用户操作的独立性：对于使用同一张临时表的不同用户，Oracle都会分配一个独立的TEMP SEGMENT，这样就避免了多个用户在对同一张临时表操作时发生交叉，从而保证了多个用户操作的并发性和独立性。
l 数据的临时性：既然是临时表，顾名思义，存放在该表中的数据是临时性的。Oracle根据创建临时表时指定的参数（ON COMMIT DELETE ROWS /ON COMMIT PRESERVE ROWS），自动将数据TRUNCATE掉。
（二）临时表的分类
Oracle数据库根据临时表的性质不同，可以分为事务临时表（ON COMMIT DELETE ROWS）与会话临时表（ON COMMIT PRESERVE ROWS）。
1、事务临时表
事务临时表是指数据只有在当前事务内有效，该临时表与事务相关，当进行事务提交或者事务回滚的时候，临时表中的数据将自动被清空，其它的内容和会话级的临时表的一致（包括退出SESSION的时候，事务级的临时表的数据也会被清空）。一般情况下，如果在创建数据表的时候，没有特殊指明表是会话临时表的话，那么该表默认为事务临时表。
以下三种情况下，事务临时表中的数据就会被清空：
① 提交事务（COMMIT）
② 回滚事务（ROLLBACK）
③ 退出SESSION
创建事务临时表的语法如下所示：
CREATE GLOBAL TEMPORARY TABLE TABLE_NAME
(COL1 TYPE1,COL2 TYPE2...)
[ON COMMIT DELETE ROWS];
事务临时表示例如下所示：
SYS@lhrdb> CREATE GLOBAL TEMPORARY TABLE CGTT_DELETE_LHR AS SELECT * FROM SCOTT.EMP WHERE 1=2;
Table created.
SYS@lhrdb> INSERT INTO CGTT_DELETE_LHR SELECT * FROM SCOTT.EMP WHERE ROWNUM<=5;
5 rows created.
SYS@lhrdb> SELECT * FROM CGTT_DELETE_LHR;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
SYS@lhrdb> COMMIT;
Commit complete.
SYS@lhrdb> SELECT * FROM CGTT_DELETE_LHR;
no rows selected
从示例中可以看到，当执行完COMMIT后，当前会话就看不到数据了。
2、会话临时表
会话临时表，顾名思义，是指数据只在当前会话内是有效的临时表。关闭当前会话或者进行新的连接之后，数据表中的内容就会被清除。
CREATE GLOBAL TEMPORARY TABLE TABLE_NAME
(COL1 TYPE1,COL2 TYPE2...)
ON COMMIT PRESERVE ROWS;
会话临时表示例如下所示：
SYS@lhrdb> CREATE GLOBAL TEMPORARY TABLE CGTT_PRESERVE_LHR ON COMMIT PRESERVE ROWS AS SELECT * FROM SCOTT.EMP WHERE 1=2;
Table created.
SYS@lhrdb> INSERT INTO CGTT_PRESERVE_LHR SELECT * FROM SCOTT.EMP WHERE ROWNUM<=5;
5 rows created.
SYS@lhrdb> SELECT * FROM CGTT_PRESERVE_LHR;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
SYS@lhrdb> COMMIT;
Commit complete.
SYS@lhrdb> SELECT * FROM CGTT_PRESERVE_LHR;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
SYS@lhrdb> CONN / AS SYSDBA
Connected.
SYS@lhrdb> SELECT * FROM CGTT_PRESERVE_LHR;
no rows selected
从示例中可以看到，当执行完COMMIT后，数据依然存在，但是当重新连接会话后，数据就被清空了。
查看一张表是否临时表，可以从DBA_TABLES视图的DURATION列来查询：
SELECT UT.TABLE_NAME,
UT.TABLESPACE_NAME,
UT.TEMPORARY,
DECODE(UT.DURATION,'SYS$SESSION','会话级','SYS$TRANSACTION','事务级') 临时表类型
FROM DBA_TABLES UT
WHERE UT.TEMPORARY = 'Y'
AND UT.TABLE_NAME LIKE '%CGTT%';
TABLE_NAME TABLESPACE_NAME T TYPE
------------------------------ ------------------------------ - ------
CGTT_DELETE_LHR Y 事务级
CGTT_PRESERVE_LHR Y 会话级
会话临时表与事务临时表主要的差异就在于删除数据的时机不同。事务临时表是在事务提交或回滚的时候清除数据，而会话临时表则是在关闭当前会话的时候清除数据。只要当前会话没有关闭，即使事务完成了，会话临时表中的数据仍然存在，不会被清除。
（三）临时表的统计信息
临时表默认是不收集统计信息的，但是可以使用DBMS_STATS.GATHER_SCHEMA_STATS在SCHEMA级别收集，需要设置GATHER_TEMP为TRUE（默认为FALSE）；也可以和普通表一样使用GATHER_TABLE_STATS在表级别来收集。需要注意的是，只能收集会话临时表的统计信息，不能收集事务临时表的统计信息。会话临时表的统计信息是被所有的会话所共享的，所以，在一般情况下，不建议收集临时表的统计信息，否则可能会导致很严重的数据库性能问题。临时表的统计信息在生成执行计划时一般是被动态采样的。
下面给出一个示例，该示例演示了由于收集了临时表的统计信息引发的性能问题。
环境准备：
DROP TABLE T_20170619_LHR CASCADE CONSTRAINTS PURGE;
DROP TABLE T_CGTT_20170619_LHR CASCADE CONSTRAINTS PURGE;
CREATE TABLE T_20170619_LHR (
ID NUMBER NOT NULL,
N NUMBER,
CONTENTS VARCHAR2(4000)
);
CREATE GLOBAL TEMPORARY TABLE T_CGTT_20170619_LHR (
ID NUMBER NOT NULL,
T_ID NUMBER NOT NULL,
N NUMBER,
CONTENTS VARCHAR2(4000)
) ON COMMIT PRESERVE ROWS
;
EXECUTE DBMS_RANDOM.SEED(0);
INSERT INTO T_20170619_LHR
SELECT ROWNUM, ROWNUM, DBMS_RANDOM.STRING('A', 50)
FROM DUAL
CONNECT BY LEVEL <= 10
ORDER BY DBMS_RANDOM.RANDOM;
INSERT INTO T_CGTT_20170619_LHR
SELECT ROWNUM, ROWNUM, ROWNUM, DBMS_RANDOM.STRING('B', 50)
FROM DUAL CONNECT BY LEVEL <= 100000
ORDER BY DBMS_RANDOM.RANDOM;
COMMIT;
SELECT COUNT(*) FROM T_20170619_LHR; --10
SELECT COUNT(*) FROM T_CGTT_20170619_LHR; --100000
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'LHR',TABNAME => 'T_20170619_LHR',ESTIMATE_PERCENT => 100,CASCADE=>TRUE);
正常的执行计划如下：
SET LINESIZE 1000
ALTER SESSION SET STATISTICS_LEVEL=ALL ;
SELECT *
FROM T_20170619_LHR A,T_CGTT_20170619_LHR B
WHERE A.ID = B.T_ID;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST ADVANCED'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
SQL_ID awa3ys5000qc1, child number 0
-------------------------------------
SELECT * FROM T_20170619_LHR A,T_CGTT_20170619_LHR B WHERE A.ID = B.T_ID
Plan hash value: 1110746760
---------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 272 (100)| | 10 |00:00:00.03 | 990 | | | |
|* 1 | HASH JOIN | | 1 | 10 | 20980 | 272 (1)| 00:00:04 | 10 |00:00:00.03 | 990 | 821K| 821K| 1142K (0)|
| 2 | TABLE ACCESS FULL| T_20170619_LHR | 1 | 10 | 570 | 3 (0)| 00:00:01 | 10 |00:00:00.01 | 6 | | | |
| 3 | TABLE ACCESS FULL| T_CGTT_20170619_LHR | 1 | 90611 | 176M| 268 (1)| 00:00:04 | 100K|00:00:00.01 | 984 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / A@SEL$1
3 - SEL$1 / B@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "A"@"SEL$1")
FULL(@"SEL$1" "B"@"SEL$1")
LEADING(@"SEL$1" "A"@"SEL$1" "B"@"SEL$1")
USE_HASH(@"SEL$1" "B"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."ID"="B"."T_ID")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1) "A"."ID"[NUMBER,22], "B"."T_ID"[NUMBER,22], "A"."CONTENTS"[VARCHAR2,4000], "A"."N"[NUMBER,22], "B"."ID"[NUMBER,22],
"B"."CONTENTS"[VARCHAR2,4000], "B"."N"[NUMBER,22]
2 - "A"."ID"[NUMBER,22], "A"."N"[NUMBER,22], "A"."CONTENTS"[VARCHAR2,4000]
3 - "B"."ID"[NUMBER,22], "B"."T_ID"[NUMBER,22], "B"."N"[NUMBER,22], "B"."CONTENTS"[VARCHAR2,4000]
Note
-----
- dynamic sampling used for this statement (level=2)
这个时候执行计划是正确的，因为T_20170619_LHR是小表（共10行数据），而T_CGTT_20170619_LHR是大表（共100000行数据），处于被驱动的位置，是正确的，而且在执行中，Oracle对大表使用了动态采样。下面新开一个会话，然后收集全局临时表T_CGTT_20170619_LHR表的统计信息，如下：
SYS@orclasm > SELECT TABLE_NAME,
2 PARTITION_NAME,
3 LAST_ANALYZED,
4 PARTITION_POSITION,
5 NUM_ROWS
6 FROM DBA_TAB_STATISTICS T
7 WHERE TABLE_NAME = 'T_CGTT_20170619_LHR';
TABLE_NAME PARTITION_NAME LAST_ANALYZED PARTITION_POSITION NUM_ROWS
------------------------------ ------------------------------ ------------------- ------------------ ----------
T_CGTT_20170619_LHR
SYS@orclasm > EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'LHR',TABNAME => 'T_CGTT_20170619_LHR',ESTIMATE_PERCENT => 100,CASCADE=>TRUE,NO_INVALIDATE => FALSE); --注意这里要加NO_INVALIDATE => FALSE
PL/SQL procedure successfully completed.
SYS@orclasm > SELECT TABLE_NAME,
2 PARTITION_NAME,
3 LAST_ANALYZED,
4 PARTITION_POSITION,
5 NUM_ROWS
6 FROM DBA_TAB_STATISTICS T
7 WHERE TABLE_NAME = 'T_CGTT_20170619_LHR';
TABLE_NAME PARTITION_NAME LAST_ANALYZED PARTITION_POSITION NUM_ROWS
------------------------------ ------------------------------ ------------------- ------------------ ----------
T_CGTT_20170619_LHR 2017-06-19 15:30:24 0
然后回到刚才的会话，继续查询执行计划，发现执行计划变动了：
SET LINESIZE 1000
ALTER SESSION SET STATISTICS_LEVEL=ALL ;
SELECT *
FROM T_20170619_LHR A,T_CGTT_20170619_LHR B
WHERE A.ID = B.T_ID;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST ADVANCED'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
SQL_ID awa3ys5000qc1, child number 0
-------------------------------------
SELECT * FROM T_20170619_LHR A,T_CGTT_20170619_LHR B WHERE A.ID = B.T_ID
Plan hash value: 991471220
---------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 6 (100)| | 10 |00:00:00.05 | 989 | | | |
|* 1 | HASH JOIN | | 1 | 1 | 2098 | 6 (17)| 00:00:01 | 10 |00:00:00.05 | 989 | 10M| 2143K| 11M (0)|
| 2 | TABLE ACCESS FULL| T_CGTT_20170619_LHR | 1 | 1 | 2041 | 2 (0)| 00:00:01 | 100K|00:00:00.01 | 982 | | | |
| 3 | TABLE ACCESS FULL| T_20170619_LHR | 1 | 10 | 570 | 3 (0)| 00:00:01 | 10 |00:00:00.01 | 7 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / B@SEL$1
3 - SEL$1 / A@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "B"@"SEL$1")
FULL(@"SEL$1" "A"@"SEL$1")
LEADING(@"SEL$1" "B"@"SEL$1" "A"@"SEL$1")
USE_HASH(@"SEL$1" "A"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."ID"="B"."T_ID")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1) "B"."T_ID"[NUMBER,22], "A"."ID"[NUMBER,22], "B"."ID"[NUMBER,22], "B"."CONTENTS"[VARCHAR2,4000], "B"."N"[NUMBER,22],
"A"."CONTENTS"[VARCHAR2,4000], "A"."N"[NUMBER,22]
2 - "B"."ID"[NUMBER,22], "B"."T_ID"[NUMBER,22], "B"."N"[NUMBER,22], "B"."CONTENTS"[VARCHAR2,4000]
3 - "A"."ID"[NUMBER,22], "A"."N"[NUMBER,22], "A"."CONTENTS"[VARCHAR2,4000]
可见，没有使用动态采样，而且生成的执行计划是错误的，因为大表（10W条记录）竟然做了驱动表。当删除临时表的统计信息后，执行计划恢复正常：
LHR@orclasm > EXEC DBMS_STATS.DELETE_TABLE_STATS(OWNNAME => 'LHR',TABNAME => 'T_CGTT_20170619_LHR',NO_INVALIDATE => FALSE);
PL/SQL procedure successfully completed.
SET LINESIZE 1000
ALTER SESSION SET STATISTICS_LEVEL=ALL ;
SELECT *
FROM T_20170619_LHR A,T_CGTT_20170619_LHR B
WHERE A.ID = B.T_ID;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST ADVANCED'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
SQL_ID awa3ys5000qc1, child number 0
-------------------------------------
SELECT * FROM T_20170619_LHR A,T_CGTT_20170619_LHR B WHERE A.ID = B.T_ID
Plan hash value: 1110746760
---------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 272 (100)| | 10 |00:00:00.03 | 990 | | | |
|* 1 | HASH JOIN | | 1 | 10 | 20980 | 272 (1)| 00:00:04 | 10 |00:00:00.03 | 990 | 821K| 821K| 1142K (0)|
| 2 | TABLE ACCESS FULL| T_20170619_LHR | 1 | 10 | 570 | 3 (0)| 00:00:01 | 10 |00:00:00.01 | 6 | | | |
| 3 | TABLE ACCESS FULL| T_CGTT_20170619_LHR | 1 | 90611 | 176M| 268 (1)| 00:00:04 | 100K|00:00:00.01 | 984 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / A@SEL$1
3 - SEL$1 / B@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "A"@"SEL$1")
FULL(@"SEL$1" "B"@"SEL$1")
LEADING(@"SEL$1" "A"@"SEL$1" "B"@"SEL$1")
USE_HASH(@"SEL$1" "B"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."ID"="B"."T_ID")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1) "A"."ID"[NUMBER,22], "B"."T_ID"[NUMBER,22], "A"."CONTENTS"[VARCHAR2,4000], "A"."N"[NUMBER,22], "B"."ID"[NUMBER,22],
"B"."CONTENTS"[VARCHAR2,4000], "B"."N"[NUMBER,22]
2 - "A"."ID"[NUMBER,22], "A"."N"[NUMBER,22], "A"."CONTENTS"[VARCHAR2,4000]
3 - "B"."ID"[NUMBER,22], "B"."T_ID"[NUMBER,22], "B"."N"[NUMBER,22], "B"."CONTENTS"[VARCHAR2,4000]
Note
-----
- dynamic sampling used for this statement (level=2)
所以，对于临时表尽量使用动态采样，而不应该收集它的统计信息。因为统计信息被所有会话所共享，而全局临时表的数据是与会话相关的，不同的会话其含有的数据量是不同的。
真题1、 You issued the following command:
CREATE GLOBAL TEMPORARY TABLE admin_work_area
(startdate DATE,
enddate DATE,
class CHAR(20))
ON COMMIT DELETE ROWS
TABLESPACE tbs_t1;
An index is then created on the ADMIN_WORK_AREA temporary table. Which two statements are true regarding the TBS_T1 tablespace in the above command? (Choose two.)
A、It stores only the temporary table but not its indexes.
B、It stores both the temporary table as well as its indexes.
C、It must be a nondefault temporary tablespace for the database.
D、It can be a default or nondefault temporary tablespace for the database.
E、It must be the default temporary tablespace of the user who issues the command.
答案：B、D。
本题中的命令是在TBS_T1表空间中创建了一个事务临时表ADMIN_WORK_AREA，并且在该临时表上创建了一个索引，最后要求选出有关TBS_T1表空间的2个正确的选项。
本题中，对于选项A，TBS_T1表空间只存数据而不存索引，说法错误，应该都存储。所以，选项A错误。
对于选项B，TBS_T1表空间即存储数据也存储索引，说法正确。所以，选项B正确。
对于选项C，TBS_T1表空间必须是非默认的表空间，说法错误，默认和非默认都可以。所以，选项C错误。
对于选项D，TBS_T1表空间可以是一个默认或非默认的数据库的临时表空间。所以，选项D正确。
对于选项E，TBS_T1表空间必须是默认的表空间，说法错误，默认和非默认都可以。所以，选项E错误。
所以，本题的答案为B、D。
临时表(Tempotary table)用于保存事务或会话期间的中间结果集。临时表中保存的数据只对当前会话可见，所有会话都看不到其他会话的数据；即使当前会话已经提交了(commit)数据，别的会话也看不到它的数据。对于临时表，不存在多用户并发的问题，因为一个会话不会因为使用一个临时表而阻塞另一个会话。即使我们“锁住”了临时表，也不会妨碍其他会话使用临时表。
临时表比常规表生成的redo少得多。不过由于临时表必须为其中包含的数据生成undo信息，所以也会生成一定的redo。update和delete会生成最多的undo，insert和select生成的undo最少。
临时表会从当前登录用户的临时表空间分配存储空间，或者如果从一个定义者权限过程访问临时表，就会使用该过程所有者的临时表空间。全局临时表实际上是表本身的一个模板。创建临时表的动作不涉及存储空间分配，不会为此初始区段，这与常规表有所不同。对于临时表，运行时当一个会话第一次在临时表中放入数据时，才会为该会话创建一个临时段。由于每个回话会得到其自己的临时段(而不是一个现有段的一个区段)，每个用户可能在不同的表空间为其看in是表分配空间。USER1的临时表空间可能设置为TEMP1，因此他的临时表会从这个表空间分配。USER2可能把TEMP2作为其临时表空间，它的临时表就会从那里分配。
Oracle的临时表与其他关系数据库中的临时表类似，主要区别是：Oracle的临时表是“静态”定义的。每个数据库只创建一次临时表，而不是为数据库中的每个存储过程都创建一次。在Oracle历史表一定存在，他们作为对象放在数据字典中，但是在会话向临时表中放入数据之前，临时表看上去总是空的。由于临时表是静态定义的，所以你能创建引用临时表的视图，还可以创建存储过程只用静态SQL来引用临时表，等等。
临时表可以是会话的(临时表中的数据可以跨提交存在，即提交之前仍然存在，但是断开连接后再连接时数据就没有了)，也可以是基于事务的(提交之后数据就消失)。语法如下：
- 基于会话 create global temporary table temp_table_session (...) on commit preserve rows
基于会话的临时表，在会话断开之前，或者通过一个delete或truncate物理地删除行之前，这些行会一直存在于这个临时表中。只有我的会话能看到这些行，即使我已经提交，其他会话也无法看到我的行。
- 基于事务 create global temporary table temp_table_session (...) on commit delete rows
基于事务的临时表，我的会话提交时，临时表中的行就不见了。只需把分配给这个表的临时区段交回，这些行就会消失，在临时表的自动清除过程中不存在开销。
临时表可以有永久表的许多属性。他们可以有触发器，检查约束，索引等。但永久表的某些特性在临时表中并不支持，这包括以下特性。
- 不能有引用完整性约束。临时表不能作为外键的目标，也不能在临时表中定义外键。
- 不能是IOT
- 不能在任何类型的聚簇中
- 不能分区
- 不能通过ANALYZE表命令生成统计信息
在所有数据库中，临时表的缺点之一就是优化器不能正常地得到临时表的真实统计信息。使用基于代价的优化器(CBO)时，有效的统计对于优化器的成败至关重要。
全局临时表的统计信息是不能被收集的，如果被收集，它的统计信息肯定为0，会造成执行计划不准，所以要锁定它的统计信息，禁止系统自动收集。
Oracle临时表使用注意事项
① 临时表不支持物化视图
② 可以在临时表上创建索引
③ 可以基于临时表创建视图
④ 临时表结构可被导出，但内容不可以被导出
⑤ 临时表通常是创建在用户的临时表空间中的，不同用户可以有自己的独立的临时表空间
⑥ 不同的session不可以互相访问对方的临时表数据
⑦ 临时表数据将不会上DML（Data Manipulation Language）锁
1. 临时表不支持物化视图
1）环境准备
（1）创建基于会话的临时表
sec@ora10g> create global temporary table t_temp_session (x int) on commit preserve rows;
Table created.
sec@ora10g> col TABLE_NAME for a30
sec@ora10g> col TEMPORARY for a10
sec@ora10g> select TABLE_NAME,TEMPORARY from user_tables where table_name = 'T_TEMP_SESSION';
TABLE_NAME TEMPORARY
------------------------------ ----------
T_TEMP_SESSION Y
（2）初始化两条数据
sec@ora10g> insert into t_temp_session values (1);
1 row created.
sec@ora10g> insert into t_temp_session values (2);
1 row created.
sec@ora10g> commit;
Commit complete.
sec@ora10g> select * from t_temp_session;
X
----------
1
2
（3）在临时表 T_TEMP_SESSION 上添加主键
sec@ora10g> alter table T_TEMP_SESSION add constraint PK_T_TEMP_SESSION primary key(x);
Table altered.
2）在临时表T_TEMP_SESSION上创建物化视图
（1）创建物化视图日志日志
sec@ora10g> create materialized view log on T_TEMP_SESSION with sequence, rowid (x) including new values;
create materialized view log on T_TEMP_SESSION with sequence, rowid (x) including new values
*
ERROR at line 1:
ORA-14451: unsupported feature with temporary table
可见，在创建物化视图时便提示，临时表上无法创建物化视图日志。
（2）创建物化视图
sec@ora10g> create materialized view mv_T_TEMP_SESSION build immediate refresh fast on commit enable query rewrite as select * from T_TEMP_SESSION;
create materialized view mv_T_TEMP_SESSION build immediate refresh fast on commit enable query rewrite as select * from T_TEMP_SESSION
*
ERROR at line 1:
ORA-23413: table "SEC"."T_TEMP_SESSION" does not have a materialized view log
由于物化视图日志没有创建成功，因此显然物化视图亦无法创建。
2.在临时表上创建索引
sec@ora10g> create index i_t_temp_session on t_temp_session (x);
Index created.
临时表上索引创建成功。
3.基于临时表创建视图
sec@ora10g> create view v_t_temp_session as select * from t_temp_session where x<100;
View created.
基于临时表的视图创建成功。
4.临时表结构可被导出，但内容不可以被导出
1）使用exp工具备份临时表
ora10g@secdb /home/oracle$ exp sec/sec file=t_temp_session.dmp log=t_temp_session.log tables=t_temp_session
Export: Release 10.2.0.1.0 - Production on Wed Jun 29 22:06:43 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table T_TEMP_SESSION
Export terminated successfully without warnings.
可见在备份过程中，没有显示有数据被导出。
2）使用imp工具的show选项查看备份介质中的SQL内容
ora10g@secdb /home/oracle$ imp sec/sec file=t_temp_session.dmp full=y show=y
Import: Release 10.2.0.1.0 - Production on Wed Jun 29 22:06:57 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
. importing SEC's objects into SEC
. importing SEC's objects into SEC
"CREATE GLOBAL TEMPORARY TABLE "T_TEMP_SESSION" ("X" NUMBER(*,0)) ON COMMIT "
"PRESERVE ROWS "
"CREATE INDEX "I_T_TEMP_SESSION" ON "T_TEMP_SESSION" ("X" ) "
Import terminated successfully without warnings.
这里体现了创建临时表和索引的语句，因此临时表的结构数据是可以被导出的。
3）尝试导入数据
ora10g@secdb /home/oracle$ imp sec/sec file=t_temp_session.dmp full=y ignore=y
Import: Release 10.2.0.1.0 - Production on Wed Jun 29 22:07:16 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
. importing SEC's objects into SEC
. importing SEC's objects into SEC
Import terminated successfully without warnings.
依然显示没有记录被导入。
5.查看临时表空间的使用情况
可以通过查询V$SORT_USAGE视图获得相关信息。
sec@ora10g> select username,tablespace,session_num sid,sqladdr,sqlhash,segtype,extents,blocks from v$sort_usage;
USERNAME TABLESPACE SID SQLADDR SQLHASH SEGTYPE EXTENTS BLOCKS
-------- ---------- ------- -------- ---------- ------- ------- -------
SEC TEMP 370 389AEC58 1029988163 DATA 1 128
SEC TEMP 370 389AEC58 1029988163 INDEX 1 128
可见SEC用户中创建的临时表以及其上的索引均存放在TEMP临时表空间中。
在创建用户的时候，可以指定用户的默认临时表空间，这样不同用户在创建临时表的时候便可以使用各自的临时表空间，互不干扰。
6.不同的session不可以互相访问对方的临时表数据
1）在第一个session中查看临时表数据
sec@ora10g> select * from t_temp_session;
X
----------
1
2
此数据为初始化环境时候插入的数据。
2）在单独开启一个session，查看临时表数据。
ora10g@secdb /home/oracle$ sqlplus sec/sec
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jun 29 22:30:05 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
sec@ora10g> select * from t_temp_session;
no rows selected
说明不同的session拥有各自独立的临时表操作特点，不同的session之间是不能互相访问数据。
7.临时表数据将不会上DML（Data Manipulation Language）锁
1）在新session中查看SEC用户下锁信息
sec@ora10g> col username for a8
sec@ora10g> select
2 b.username,
3 a.sid,
4 b.serial#,
5 a.type "lock type",
6 a.id1,
7 a.id2,
8 a.lmode
9 from v$lock a, v$session b
10 where a.sid=b.sid and b.username = 'SEC'
11 order by username,a.sid,serial#,a.type;
no rows selected
不存在任何锁信息。
2）向临时表中插入数据，查看锁信息
（1）插入数据
sec@ora10g> insert into t_temp_session values (1);
1 row created.
（2）查看锁信息
sec@ora10g> select
2 b.username,
3 a.sid,
4 b.serial#,
5 a.type "lock type",
6 a.id1,
7 a.id2,
8 a.lmode
9 from v$lock a, v$session b
10 where a.sid=b.sid and b.username = 'SEC'
11 order by username,a.sid,serial#,a.type;
lock lock
USERNAME SID SERIAL# type id1 id2 mode
-------- ---------- ---------- ------ ----------- ----------- ---------
SEC 142 425 TO 12125 1 3
SEC 142 425 TX 65554 446 6
此时出现TO和TX类型锁。
（3）提交数据后再次查看锁信息
sec@ora10g> commit;
Commit complete.
sec@ora10g> select
2 b.username,
3 a.sid,
4 b.serial#,
5 a.type "lock type",
6 a.id1,
7 a.id2,
8 a.lmode
9 from v$lock a, v$session b
10 where a.sid=b.sid and b.username = 'SEC'
11 order by username,a.sid,serial#,a.type;
lock lock
USERNAME SID SERIAL# type id1 id2 mode
-------- ---------- ---------- ------ ----------- ----------- ---------
SEC 142 425 TO 12125 1 3
事务所TX被释放。TO锁保留。
3）测试更新数据场景下锁信息变化
（1）更新临时表数据
sec@ora10g> update t_temp_session set x=100;
1 row updated.
（2）锁信息如下
lock lock
USERNAME SID SERIAL# type id1 id2 mode
-------- ---------- ---------- ------ ----------- ----------- ---------
SEC 142 425 TO 12125 1 3
SEC 142 425 TX 524317 464 6
（3）提交数据
sec@ora10g> commit;
Commit complete.
（4）锁信息情况
lock lock
USERNAME SID SERIAL# type id1 id2 mode
-------- ---------- ---------- ------ ----------- ----------- ---------
SEC 142 425 TO 12125 1 3
4）测试删除数据场景下锁信息变化
（1）删除临时表数据
sec@ora10g> delete from t_temp_session;
1 row deleted.
（2）查看锁信息
lock lock
USERNAME SID SERIAL# type id1 id2 mode
-------- ---------- ---------- ------ ----------- ----------- ---------
SEC 142 425 TO 12125 1 3
SEC 142 425 TX 327713 462 6
（3）提交数据
sec@ora10g> commit;
Commit complete.
（4）锁信息情况
lock lock
USERNAME SID SERIAL# type id1 id2 mode
-------- ---------- ---------- ------ ----------- ----------- ---------
SEC 142 425 TO 12125 1 3
5）总结
在临时表上的增删改等DML操作都会产生TO锁和TX事务所。TO锁会从插入数据开始一直存在。
但整个过程中都不会产生DML的TM级别锁。
8.小结
本文就临时表使用过程中常见的问题和特点进行了介绍。临时表作为Oracle的数据库对象，如果能够在理解这些特性基础上加以利用将会极大地改善系统性能。
Good luck.
secooler
11.06.29
-- The End --
在Oracle8i或以上版本中，可以创建以下两种临时表：
1。会话特有的临时表
CREATE GLOBAL TEMPORARY ( )
ON COMMIT PRESERVE ROWS；
2。事务特有的临时表
CREATE GLOBAL TEMPORARY ( )
ON COMMIT DELETE ROWS；
CREATE GLOBAL TEMPORARY TABLE MyTempTable
所建的临时表虽然是存在的，但是你试一下insert 一条记录然后用别的连接登上去select，记录是空的，明白了吧，我把下面两句话再贴一下：
--ON COMMIT DELETE ROWS 说明临时表是事务指定，每次提交后ORACLE将截断表（删除全部行）
--ON COMMIT PRESERVE ROWS 说明临时表是会话指定，当中断会话时ORACLE将截断表。
冲突的问题更本不用考虑.
临时表只是保存当前会话(session)用到的数据，数据只在事务或会话期间存在。
通过CREATE GLOBAL TEMPORARY TABLE命令创建一个临时表，对于事务类型的临时表， 数据只是在事务期间存在，对于会话类型的临时表，数据在会话期间存在。
会话的数据对于当前会话私有。每个会话只能看到并修改自己的数据。DML锁不会加到 临时表的数据上。下面的语句控制行的存在性。
● ON COMMIT DELETE ROWS 表名行只是在事务期间可见
● ON COMMIT PRESERVE ROWS 表名行在整个会话期间可见
可以对临时表创建索引，视图，出发器，可以用export和import工具导入导出表的 定义，但是不能导出数据。表的定义对所有的会话可见。
Temporary Tables临时表
1简介
ORACLE数据库除了可以保存永久表外，还可以建立临时表temporary tables。这些临时表用来保存一个会话SESSION的数据，
或者保存在一个事务中需要的数据。当会话退出或者用户提交commit和回滚rollback事务的时候，临时表的数据自动清空，
但是临时表的结构以及元数据还存储在用户的数据字典中。
临时表只在oracle8i以及以上产品中支持。
2详细介绍
Oracle临时表分为 会话级临时表 和 事务级临时表。
会话级临时表是指临时表中的数据只在会话生命周期之中存在，当用户退出会话结束的时候，Oracle自动清除临时表中数据。
事务级临时表是指临时表中的数据只在事务生命周期中存在。当一个事务结束（commit or rollback），Oracle自动清除临时表中数据。
临时表中的数据只对当前Session有效，每个Session都有自己的临时数据，并且不能访问其它Session的临时表中的数据。因此，
临时表不需要DML锁.当一个会话结束(用户正常退出 用户不正常退出 ORACLE实例崩溃)或者一个事务结束的时候，Oracle对这个会话的
表执行 TRUNCATE 语句清空临时表数据.但不会清空其它会话临时表中的数据.
你可以索引临时表和在临时表基础上建立视图.同样,建立在临时表上的索引也是临时的,也是只对当前会话或者事务有效.
临时表可以拥有触发器.
3建立临时表
临时表的定义对所有会话SESSION都是可见的,但是表中的数据只对当前的会话或者事务有效.
建立方法:
1) ON COMMIT DELETE ROWS 定义了建立事务级临时表的方法.
EXAMPLE:
2)ON COMMIT PRESERVE ROWS 定义了创建会话级临时表的方法.
会话1:
会话2:
未选择行.
会话2看不见会话1中临时表的数据.
4 ORACLE临时表和SQLSERVER临时表异同
SQL SERVER临时表
也可以创建临时表。临时表与永久表相似，但临时表存储在 tempdb 中，当不再使用时会自动删除。
有本地和全局两种类型的临时表，二者在名称、可见性和可用性上均不相同。本地临时表的名称以单个数字符号 (#) 打头；
它们仅对当前的用户连接是可见的；当用户从 Microsoft? SQL Server? 2000 实例断开连接时被删除。全局临时表的名称以数学符号
(##) 打头，创建后对任何用户都是可见的，当所有引用该表的用户从 SQL Server 断开连接时被删除。
例如，如果创建名为 employees 的表，则任何人只要在数据库中有使用该表的安全权限就可以使用该表，除非它已删除。
如果创建名为 #employees 的本地临时表，只有您能对该表执行操作且在断开连接时该表删除。如果创建名为 ##employees 的全局临时表
数据表中的任何用户均可对该表执行操作。如果该表在您创建后没有其他用户使用，则当您断开连接时该表删除。如果该表在您创建
后有其他用户使用，则 SQL Server在所有用户断开连接后删除该表
不同:
1. SQL SERVER临时表是一种”内存表”,表是存储在内存中的.ORACLE临时表除非执行DROP TABLE,否则表定义会保留在数据字典中.
2. SQL SERVER临时表不存在类似ORACLE临时表 事务级别 上的功能.
3 SQL SERVER本地临时表(#) 与 ORACLE的会话级别临时表类似,但是在会话退出的时候,ORACLE不会删除表.
4 SQL SERVER的全局临时表(##) 是指多个连接共享同一片内存.当没有指针引用该内存区域时,SQL SERVER自动释放全局临时表.
5 由于ORACLE不是一种 内存中的数据库. 所以如果ORACLE类似SQL SERVER 频繁的对临时表进行建立和删除,必定会影响性能.
所以ORACLE会保留临时表的定义直到用户DROP TABLE.
6 在ORACLE中,如果需要多个用户共享一个表(类似SQL SERVER的全局临时表##).则可以利用永久表,
并且在表中添加一些可以唯一标识用户的列.利用触发器和视图.当用户退出的时候,根据该登陆用户的唯一信息删除相应的表中的数据.
这种方法给ORACLE带来了一定量的负载.
我们都知道，全局临时表GTT分为两种，一种是transaction level，一种是session level，
分别通过on commit delete rows/preserve rows实现，其中session level表示在本sessoin
数据有效，相同session内，之前事务操作的数据，对于后续的操作都可见，而事务级的GTT表示
一旦事务结束(commit)那么立即delete，相同session 的后续操作看不到之前事务操作。
在9i阶段可以使用GATHER_TABLE_STATS调用来收集统计信息须传入参数GATHER_TEMP为TRUE，
10g开始oracle对于普通表和GTT收集统计信息并没有特殊处理，都是通过GATHER_TABLE_STATS
存储过程来收集，但是由于上述的两种GTT特殊性，收集统计信息有特殊性：
1.对于session level的，因为GTT数据并不持久化，存在session 隔离性，需要在当前session 收集，
若是通过另起窗口(新session)收集统计信息会不成功,原因就是收集统计信息的session 没有数据，
自然也收集不到统计信息了。
2.对于transaction level的，即便是当前session 收集，因为GATHER_TABLE_STATS会先执行默认提交，
所以数据就自动删除，自然也就没有数据可收集了。所以针对这种情景，oracle 有官方note 403587.1介绍
下面就是移花接木办法来收集事务级GTT的步骤
1. create a PRESERVE ROWS table
SQL> create global temporary table TT(I number) on commit preserve rows;
2. populate with representative data
SQL> insert into TT select rownum from dba_objects where rownum<1000;
3. gather stats
SQL> exec dbms_stats.gather_table_stats(null,'TT');
4. create a STAT table
SQL> exec dbms_stats.create_stat_table(null,'TTSTATS');
5. export the stats from the PRESERVE ROWS table
SQL> exec dbms_stats.export_table_stats(null,'TT',null,'TTSTATS',null,true);
6. truncate then drop the PRESERVE ROWS table
SQL> truncate table TT;
SQL> drop table TT;
7. now create the real temporary table (defined using DELETE ROWS - the default)
SQL> create global temporary table TT(I number);
8. finally import the stats exported from the STAT table
SQL> exec dbms_stats.import_table_stats(null,'TT',null,'TTSTATS',null,true);
3.在12c版本，oracle已经进步改善了对这种transaction level GTT的统计信息收集，
也就是说GATHER_TABLE_STATS收集统计信息的时候不会默认发起commit，这样就不会
破坏当前session的事务完整性，收集统计信息的存储过程就可以看到当前session的
数据情况并收集统计信息。
下面是一个简单的测试过程：
3.1.创建transaction level GTT
Create Global Temporary Table maob_temp (a number,b varchar2(100)) On Commit delete Rows; <<delete Rows
Table created.
3.2.插入数据
insert into maob_temp select rownum,object_name from dba_objects where rownum<1000;
SQL>
999 rows created.
3.3.收集统计信息
exec dbms_stats.gather_table_stats(user,'MAOB_TEMP');
SQL>
PL/SQL procedure successfully completed.
3.4.check是否数据已经被删除
select count(*)from maob_temp;
SQL>
COUNT(*)
----------
999
3.5.查看统计信息是否已经收集成功
SQL> select TABLE_NAME,NUM_ROWS,BLOCKS,SCOPE from DBA_TAB_STATISTICS where owner='MAOB' AND TABLE_NAME='MAOB_TEMP';
TABLE_NAME NUM_ROWS BLOCKS SCOPE
--------------------------------------
MAOB_TEMP 0 0 SHARED
MAOB_TEMP 999 4 SESSION <<<<
注意：这一步要在和上述步骤相同的session执行，因为12c的这个新功能默认对GTT收集统计信息是session scope的，也就是说统计信息也是
session 隔离的，其他session 看不到这个session收集的统计信息，若是变成传统的shared scope，那么仍然会默认先commit再收集
统计信息并记录数据字典表，供其他session 使用，对于transaction level仍然存在先commit在收集情况，那么要解决问题，仍需要参考步骤
2的移花接木办法,但是创建表之后要先指定为shared scope再收集统计信息。
EXEC DBMS_STATS.SET_TABLE_PREFS (NULL,'TT','GLOBAL_TEMP_TABLE_STATS','SHARED');
About Me
...............................................................................................................................
● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用
● 本文在itpub（http://blog.itpub.net/26736162）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新
● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/
● 本文博客园地址：http://www.cnblogs.com/lhrbest
● 本文pdf版及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/
● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/
● QQ群：230161599 微信群：私聊
● 联系我请加QQ好友(646634621)，注明添加缘由
● 于 2017-06-02 09:00 ~ 2017-06-30 22:00 在魔都完成
● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
...............................................................................................................................
拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号：xiaomaimiaolhr，扫描右边的二维码加入小麦苗的QQ群，学习最实用的数据库技术。
