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;
