（转）Oracle临时表使用注意事项
http://space.itpub.net/519536/viewspace-701024
【TEMPORARY TABLE】Oracle临时表使用注意事项
上一篇?/?下一篇??2011-06-29 23:37:48 / 个人分类：性能优化
查看( 3561 )?/?评论( 13 )?/?评分(?30?/?0?)? 此文将给出在使用Oracle临时表的过程中需要注意的事项，并对这些特点进行验证。?①临时表不支持物化视图
?②可以在临时表上创建索引
?③可以基于临时表创建视图
?④临时表结构可被导出，但内容不可以被导出
?⑤临时表通常是创建在用户的临时表空间中的，不同用户可以有自己的独立的临时表空间
?⑥不同的session不可以互相访问对方的临时表数据
?⑦临时表数据将不会上DML—ata 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—ata 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 --
1.物化视图的内容已经补充到文章中，谢谢；
2.④和⑥本质是一样的。原因就是不同session之间不能互相访问对方的数据，exp对数据库来说就是独立的session。
secooler
引用删除?? / ? 2011-06-30 11:01:02引用删除?? / ? 2011-06-30 10:43:53