使用臨時表解決表衝突一例

使用oracle的全域性臨時表。

本文可以任意轉載，轉載時請務必以超連結形式標明文章原始出處和作者資訊及本宣告

http://blog.itpub.net/post/11/1194

平臺：windows 2000 server sp3

資料庫：Oracle 9.0.1.1.1

 

現場描述：使用者抱怨不能同時對多個單位進行資料處理，執行出現的錯誤資訊為：

SQL> exec sp_hyb_da_ryxx_dwbh_test('331028')

BEGIN sp_hyb_da_ryxx_dwbh_test('331028'); END;

 

*

ERROR 位於第 1 行:

ORA-02055: 分散式更新操作失效；要求回退

ORA-06502: PL/SQL: 數字或值錯誤 :  字串緩衝區太小

ORA-06512: 在"SIDB.SP_HYB_DA_RYXX_DWBH_TEST", line 55

ORA-06502: PL/SQL: 數字或值錯誤 :  字串緩衝區太小

ORA-06512: 在"SIDB.SP_HYB_DA_RYXX", line 145

ORA-06502: PL/SQL: 數字或值錯誤 :  字串緩衝區太小

ORA-06512: 在"SIDB.SP_HYB_DA_RYXX_YZ1", line 25

ORA-20901: 過程SP_HYB_DAXX出錯;grbh = 1000607288 ORA-02049: 超時:

分散式事務處理等待鎖定

請聯絡系統管理員;1

ORA-06512: 在line 1

 

經檢查後發現是後臺儲存過程中使用了‘臨時’表，這些臨時表用於臨時儲存部分正式表的資料，應用程式儲存過程的程式碼形如

DELETE ;

INSERT INTO SELECT *FROM WHERE …;

這種方法造成了在同時執行的時候由於對鎖定的等待而只能同時進行一個單位的資料處理。

可以透過使用Oracle真正意義上的臨時表消除對臨時表的鎖定。

臨時表的基本定義是：

資料庫中的所有會話均可以訪問臨時表，但只有插入資料到臨時表中的會話才能看到本身插入的資料。可以把臨時表指定為事務相關（預設）或者是會話相關：

ON COMMIT DELETE ROWS：指定臨時表是事務相關的，Oracle在每次提交後截斷表。

ON COMMIT PRESERVE ROWS：指定臨時表是會話相關的，Oracle在會話中止後截斷表。

透過使用Oracle的臨時表後可以並行的對資料進行處理，有效的提高了資料處理的速度。

 

附：建立臨時表的例子

 

CREATE GLOBAL TEMPORARY TABLE

AS

SELECT *FROM WHERE 1 = 2;

CREATE INDEX ON ();