Oracle事務臨時表的一個隱藏問題
Oracle臨時分為兩類,
一類是事務臨時表,以on commit delete rows指示。按照指示含義,我們可以知道這類臨時表資料是在commit之後就清除資料的。
一類是session臨時表,以on commit preserve rows指示。同樣,按照含義,表明這類表在commit之後還是保留資料的(直到session結束後會被自動清除)。
為什麼要提Oracle的這兩類臨時表呢?他們的屬性就這麼點,還有啥好提的?呵呵。看下面例子:
--先建立一個事務臨時表:
create global temporary table xxx(x int) on commit delete rows;
--執行如下語句:
SQL> declare
2 cursor cx is
3 select * from xxx;
4 type typ_cx is table of cx%rowtype index by pls_integer;
5 v_cx typ_cx;
6 begin
7 insert into xxx
8 select rownum from dual connect by rownum <= 20000;
9 open cx;
10 loop
11 fetch cx bulk collect into v_cx limit 3000;
12 exit when v_cx.count = 0;
13 dbms_output.put_line('Count: ' || v_cx.count);
14 commit;
15 end loop
16 commit;
17 end;
18 /
發生報錯:
ORA-01410: invalid ROWID
ORA-06512: at line 12
覺得正常嗎?
現在刪除該臨時表,建立一個session級的臨時表,其他都不變:
--刪除臨時表
drop table xxx
--建立session臨時表
create global temporary table xxx(x int) on commit preserve rows;
--執行之前那個過程
SQL> declare
2 cursor cx is
3 select * from xxx;
4 type typ_cx is table of cx%rowtype index by pls_integer;
5 v_cx typ_cx;
6 begin
7 insert into xxx
8 select rownum from dual connect by rownum <= 20000;
9 open cx;
10 loop
11 fetch cx bulk collect into v_cx limit 3000;
12 exit when v_cx.count = 0;
13 dbms_output.put_line('Count: ' || v_cx.count);
14 commit;
15 end loop
16 commit;
17 end;
18 /
PL/SQL procedure successfully completed
執行成功!!這是為什麼?
看程式碼知道,這個過程就是將20000個數字的資料插入到臨時表,然後開啟臨時表的遊標,將這些數字以每批3000分批從遊標中取出並列印對應批次的數量。列印後,執行一個commit命令。問題就出在這個commit命令上,因為commit表示一個提交,而事務臨時表在commit之後,資料隨之被清空,導致下次fetch遊標中的資料時無法取到,從而發生第一個過程執行時的錯誤。而session臨時表就不會有這個問題。
另外,需要注意的是,session臨時表是在整個session期間有效的,因此在同個session期間如果執行了多次,會重複的插入資料到該表中。開發的時候要注意這個現象,否則很容易出現累加效應。
一類是事務臨時表,以on commit delete rows指示。按照指示含義,我們可以知道這類臨時表資料是在commit之後就清除資料的。
一類是session臨時表,以on commit preserve rows指示。同樣,按照含義,表明這類表在commit之後還是保留資料的(直到session結束後會被自動清除)。
為什麼要提Oracle的這兩類臨時表呢?他們的屬性就這麼點,還有啥好提的?呵呵。看下面例子:
--先建立一個事務臨時表:
create global temporary table xxx(x int) on commit delete rows;
--執行如下語句:
SQL> declare
2 cursor cx is
3 select * from xxx;
4 type typ_cx is table of cx%rowtype index by pls_integer;
5 v_cx typ_cx;
6 begin
7 insert into xxx
8 select rownum from dual connect by rownum <= 20000;
9 open cx;
10 loop
11 fetch cx bulk collect into v_cx limit 3000;
12 exit when v_cx.count = 0;
13 dbms_output.put_line('Count: ' || v_cx.count);
14 commit;
15 end loop
16 commit;
17 end;
18 /
發生報錯:
ORA-01410: invalid ROWID
ORA-06512: at line 12
覺得正常嗎?
現在刪除該臨時表,建立一個session級的臨時表,其他都不變:
--刪除臨時表
drop table xxx
--建立session臨時表
create global temporary table xxx(x int) on commit preserve rows;
--執行之前那個過程
SQL> declare
2 cursor cx is
3 select * from xxx;
4 type typ_cx is table of cx%rowtype index by pls_integer;
5 v_cx typ_cx;
6 begin
7 insert into xxx
8 select rownum from dual connect by rownum <= 20000;
9 open cx;
10 loop
11 fetch cx bulk collect into v_cx limit 3000;
12 exit when v_cx.count = 0;
13 dbms_output.put_line('Count: ' || v_cx.count);
14 commit;
15 end loop
16 commit;
17 end;
18 /
PL/SQL procedure successfully completed
執行成功!!這是為什麼?
看程式碼知道,這個過程就是將20000個數字的資料插入到臨時表,然後開啟臨時表的遊標,將這些數字以每批3000分批從遊標中取出並列印對應批次的數量。列印後,執行一個commit命令。問題就出在這個commit命令上,因為commit表示一個提交,而事務臨時表在commit之後,資料隨之被清空,導致下次fetch遊標中的資料時無法取到,從而發生第一個過程執行時的錯誤。而session臨時表就不會有這個問題。
另外,需要注意的是,session臨時表是在整個session期間有效的,因此在同個session期間如果執行了多次,會重複的插入資料到該表中。開發的時候要注意這個現象,否則很容易出現累加效應。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/12932950/viewspace-682606/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle 表結構順序一致 隱藏的2個問題...Oracle
- 27、oracle的臨時表問題Oracle
- 請教一個在完整提交前臨時儲存的問題(事務)!!
- Oracle 表結構順序不一致 隱藏的2個問題...Oracle
- (轉) oracle 臨時表(事務級、會話級)Oracle會話
- mysql臨時表的問題MySql
- Oracle臨時表使用注意事項Oracle
- oracle 9i 臨時表空間問題Oracle
- 解決Oracle臨時表空間佔滿的問題Oracle
- 會話與事務級臨時表和dual表會話
- oracle的臨時表空間解決問題的步驟Oracle
- Oracle的臨時表Oracle
- 【TEMPORARY TABLE】Oracle臨時表使用注意事項Oracle
- oracle臨時表Oracle
- Oracle 臨時表Oracle
- 轉:Oracle 臨時表空間過大問題解決Oracle
- 建立基於事務和基於會話的臨時表及臨時表建索引的實驗會話索引
- oracle 臨時表的使用Oracle
- oracle臨時表的用法Oracle
- ORACLE臨時表和SQLSERVER臨時表異同OracleSQLServer
- 一個詭異的MySQL查詢超時問題,居然隱藏著存在了兩年的BUGMySql
- oracle的臨時表空間Oracle
- oracle臨時表的用法(轉)Oracle
- 系統臨時表空間不足問題
- Oracle日常問題-臨時表過多導致exp速度慢Oracle
- 【實驗】重建臨時表空間解決臨時表空間過大問題
- Oracle Temporary Tables(Oracle 臨時表)Oracle
- 關於Oracle臨時表的使用的小經歷和一點疑問?Oracle
- ORACLE臨時表總結Oracle
- Oracle全域性臨時表Oracle
- oracle 臨時表空間Oracle
- oracle臨時表空間Oracle
- 一次臨時表空間大量佔用問題的處理
- 請教一個關於spring事務的問題 ?Spring
- 請教一個事務+多執行緒 的問題執行緒
- oracle全域性臨時表的特性Oracle
- Oracle 臨時表空間的概念Oracle
- oracle 臨時表間重建的方法Oracle