上添加主键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_sessionExport: Release 10.2.0.1.0 - Production on Wed Jun 29 22:06:43 2011Copyright (c) 1982, 2005, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsExport done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character setAbout to export specified tables via Conventional Path .... . exporting table T_TEMP_SESSIONExport terminated successfully without warnings.可见在备份过程中,没有显示有数据被导出。2)使用imp工具的show选项查看备份介质中的SQL内容ora10g@secdb /home/oracle$ imp sec/sec file=t_temp_session.dmp full=y show=yImport: Release 10.2.0.1.0 - Production on Wed Jun 29 22:06:57 2011Copyright (c) 1982, 2005, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsExport file created by EXPORT:V10.02.01 via conventional pathimport 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=yImport: Release 10.2.0.1.0 - Production on Wed Jun 29 22:07:16 2011Copyright (c) 1982, 2005, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsExport file created by EXPORT:V10.02.01 via conventional pathimport done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set. importing SEC's objects into SEC. importing SEC's objects into SECImport 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 128SEC TEMP 370 389AEC58 1029988163 INDEX 1 128可见SEC用户中创建的临时表以及其上的索引均存放在TEMP临时表空间中。在创建用户的时候,可以指定用户的默认临时表空间,这样不同用户在创建临时表的时候便可以使用各自的临时表空间,互不干扰。6.不同的session不可以互相访问对方的临时表数据1)在第一个session中查看临时表数据sec@ora10g> select * from t_temp_session;X----------12此数据为初始化环境时候插入的数据。2)在单独开启一个session,查看临时表数据。ora10g@secdb /home/oracle$ sqlplus sec/secSQL*Plus: Release 10.2.0.1.0 - Production on Wed Jun 29 22:30:05 2011Copyright (c) 1982, 2005, Oracle. All rights reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionssec@ora10g> select * from t_temp_session;no rows selected说明不同的session拥有各自独立的临时表操作特点,不同的session之间是不能互相访问数据。7.临时表数据将不会上DML(Data Manipulation Language)锁1)在新session中查看SEC用户下锁信息sec@ora10g> col username for a8sec@ora10g> select2 b.username,3 a.sid,4 b.serial#,5 a.type "lock type",6 a.id1,7 a.id2,8 a.lmode9 from v$lock a, v$session b10 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> select2 b.username,3 a.sid,4 b.serial#,5 a.type "lock type",6 a.id1,7 a.id2,8 a.lmode9 from v$lock a, v$session b10 where a.sid=b.sid and b.username = 'SEC'11 order by username,a.sid,serial#,a.type;lock lockUSERNAME SID SERIAL# type id1 id2 mode-------- ---------- ---------- ------ ----------- ----------- ---------SEC 142 425 TO 12125 1 3SEC 142 425 TX 65554 446 6此时出现TO和TX类型锁。(3)提交数据后再次查看锁信息sec@ora10g> commit;Commit complete.sec@ora10g> select2 b.username,3 a.sid,4 b.serial#,5 a.type "lock type",6 a.id1,7 a.id2,8 a.lmode9 from v$lock a, v$session b10 where a.sid=b.sid and b.username = 'SEC'11 order by username,a.sid,serial#,a.type;lock lockUSERNAME 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 lockUSERNAME SID SERIAL# type id1 id2 mode-------- ---------- ---------- ------ ----------- ----------- ---------SEC 142 425 TO 12125 1 3SEC 142 425 TX 524317 464 6(3)提交数据sec@ora10g> commit;Commit complete.(4)锁信息情况lock lockUSERNAME SID SERIAL# type id1 id2 mode-------- ---------- ---------- ------ ----------- ----------- ---------SEC 142 425 TO 12125 1 34)测试删除数据场景下锁信息变化(1)删除临时表数据sec@ora10g> delete from t_temp_session;1 row deleted.(2)查看锁信息lock lockUSERNAME SID SERIAL# type id1 id2 mode-------- ---------- ---------- ------ ----------- ----------- ---------SEC 142 425 TO 12125 1 3SEC 142 425 TX 327713 462 6(3)提交数据sec@ora10g> commit;Commit complete.(4)锁信息情况lock lockUSERNAME SID SERIAL# type id1 id2 mode-------- ---------- ---------- ------ ----------- ----------- ---------SEC 142 425 TO 12125 1 35)总结在临时表上的增删改等DML操作都会产生TO锁和TX事务所。TO锁会从插入数据开始一直存在。但整个过程中都不会产生DML的TM级别锁。8.小结本文就临时表使用过程中常见的问题和特点进行了介绍。临时表作为Oracle的数据库对象,如果能够在理解这些特性基础上加以利用将会极大地改善系统性能。Good luck.secooler11.06.29-- The End --
oracle 临时表中无数据,【TEMPORARY TABLE】Oracle临时表使用注意事项
最新推荐文章于 2023-08-17 15:51:59 发布