Oracle臨時表使用注意事項

風靈使發表於2018-07-19

此文將給出在使用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的資料庫物件，如果能夠在理解這些特性基礎上加以利用將會極大地改善系統效能。

Oracle使用*的注意事項
2024-03-06
Oracle
Oracle資料庫表設計時的注意事項
2020-04-07
Oracle資料庫
oracle 臨時表的使用
2019-02-15
Oracle
Oracle vs PostgreSQL，研發注意事項（1）-查詢鎖表
2018-07-20
OracleSQL
使用代理IP時，有什麼注意事項？
2023-01-04
Oracle 資料匯出注意事項
2018-09-21
Oracle
IMPDP分割槽表注意事項
2018-03-18
form表單提交注意事項
2018-12-30
ORM
ORACLE臨時表總結
2018-07-19
Oracle
使用Google Fonts注意事項
2021-10-24
Go
Oracle：記憶體設定注意事項
2021-11-15
Oracle記憶體
Oracle 臨時表 OracleDataAdapter 批次更新
2024-07-04
OracleAPT
使用regulator_get時的一個小注意事項
2021-06-12
C中memcpy使用注意事項
2018-10-26
memcpy
TCP使用注意事項總結
2019-06-27
TCP
萬兆網路卡使用注意事項
2022-08-26
Guava HashMultimap使用及注意事項
2022-05-26
Guava
MySQL半同步使用注意事項
2022-07-07
MySql
機械硬碟，使用注意事項
2024-12-09
硬碟
【exp/imp不同版本】Oracle不同版本的exp/imp使用注意事項
2019-02-13
Oracle
刷題時需要的注意事項
2018-09-01
畫PCB板時的注意事項
2024-08-20
【ASM】Oracle asm刪除磁碟組注意事項
2022-09-29
ASMOracle
SQL Server 表分割槽注意事項HX
2022-03-21
SQLServer
Oracle vs PostgreSQL，研發注意事項（6）- 事務處理
2018-09-10
OracleSQL
oracle臨時表空間相關
2023-12-29
Oracle
使用DistCp將Hadoop進行雲遷移時注意事項
2022-01-08
TCPHadoop
不同版本exp/imp使用注意事項
2018-06-27
Select 選擇器使用注意事項
2024-04-28
說點JSON使用的注意事項
2023-11-29
JSON
MCU看門狗使用注意事項
2023-11-29
使用MyBatis的注意事項有哪些
2024-06-13
MyBatis
使用HTTP的三個注意事項
2022-06-11
HTTP
使用Vue.js的注意事項
2022-06-14
Vue.js
伺服器使用安全注意事項
2022-03-23
伺服器
oracle11g 查詢臨時表空間的使用率和正在使用臨時表空間的使用者
2018-12-21
Oracle
RandomAccessFile注意事項
2019-03-12
randomMac
@Lombok注意事項
2024-07-15
Lombok

Oracle臨時表使用注意事項

相關文章