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;
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.
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.
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;
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;
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;