【TEMPORARY TABLE】Oracle臨時表使用注意事項
此文將給出在使用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 --
①臨時表不支援物化檢視
②可以在臨時表上建立索引
③可以基於臨時表建立檢視
④臨時表結構可被匯出，但內容不可以被匯出
⑤臨時表通常是建立在使用者的臨時表空間中的，不同使用者可以有自己的獨立的臨時表空間
⑥不同的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 --
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25097694/viewspace-1299962/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle臨時表使用注意事項Oracle
- Oracle臨時表GLOBAL TEMPORARY TABLEOracle
- 全域性臨時表 GLOBAL TEMPORARY TABLE
- Oracle Temporary Tables(Oracle 臨時表)Oracle
- 在T-SQL中使用臨時表的注意事項SQL
- 【TABLE】oracle表線上重定義注意事項Oracle
- oracle的臨時表空間temporary tablespaceOracle
- create table進階學習(二)_全域性臨時表_global temporary table
- orace global temporary table全域性臨時表測試小記
- oracle global temporary table全域性臨時表_測試及v$tempseg_usageOracle
- 【TEMPORARY TABLE】Oracle兩種臨時表型別功能特點比較Oracle型別
- 臨時表(GLOBAL TEMPORARY TABLE)及統計資訊收集，動態取樣
- removeChild使用時注意事項REM
- oracle temporary tableOracle
- Oracle使用*的注意事項Oracle
- Oracle資料庫表設計時的注意事項Oracle資料庫
- 更改當前資料庫預設臨時表空間注意事項資料庫
- 【徵文】應用oracle flashback(2.3)--Flashback Table之注意事項Oracle
- Oracle10新特性：臨時表空間組(temporary tablespace group)Oracle
- ORACLE Temporary Tables臨時表更適合做插入和查詢操作Oracle
- Oracle 重建表(rename)注意事項總結Oracle
- Oracle 重建表(rename)注意事項小結Oracle
- oracle 臨時表的使用Oracle
- 臨時表空間temporary tablespace相關操作
- Oracle10g新特性：臨時表空間組(temporary tablespace group)Oracle
- 全面學習oracle flashback特性(2.3)--Flashback Table之注意事項Oracle
- 資料庫表--temporary table資料庫
- 記憶體(memory)表和臨時(temporary)表之瞭解記憶體
- 【YEP】專案Oracle考卷使用注意事項Oracle
- Oracle10g使用ASM注意事項OracleASM
- 關於oracle with table as 建立臨時表的用法示例以及使用with as 的優點Oracle
- oracle臨時表Oracle
- Oracle 臨時表Oracle
- 使用parallel注意事項Parallel
- 使用代理IP時，有什麼注意事項？
- mybatis中使用in查詢時的注意事項MyBatis
- webbrowser控制元件使用時的注意事項Web控制元件
- (轉) oracle 臨時表（事務級、會話級）Oracle會話