2010/01/13

ORA-02069: global_names parameter must be set to TRUE for this operation

最近在做正式台與測試台 Oracle 的資料轉檔,結果遇到這個錯誤

ORA-02069: global_names parameter must be set to TRUE for this operation

會遇到這個錯誤,小熊子初步分析是用 View 的結果轉入 DBLink 的遠端 Table 問題。

 

 

後來找到這麼一篇文章,我的解法是用暫存 table 轉入 DBLink 的遠端 Table 以下是範例:

 

--依 select 結果建立暫存資料表

CREATE TABLE myTemp as

Select * from v_myView;

--用暫存資料表轉入遠端 Table

INSERT INTO myRemoteTable@remoteDB

select * from myTemp;

--卸除暫存資料表

DROP TABLE myTemp;

 

 

Heres a note from Oracle that explains whats going on, and offers a bit of a clumsy workaround.

Problem Description: 
==================== 
You receive an ORA-02069 error during an insert to a remote database through a 
database link when the insert contained a local sequence. 
    ORA-02069: global_names parameter must be set to TRUE for this operation 
        Cause: A remote mapping of the statement is required but cannot be 
               achieved because global_names should be set to TRUE for it to 
               be achieved. 
       Action: Issue alter session set global_names = true if possible 
Problem Explanation:
====================
This is expected behaviour.
The insert statement is transformed so that it can be executed at the remote 
end of the dblink. The reference to the local sequence has to be qualified so 
that the remote end knows to call back to the local instance. The qualification
is made by appending @local_dbname.domain to the sequence reference if 
global_names=true. When global_names=false, the code cannot make the assumption
that the qualifier will be valid and reports the error ORA-02069 'global_names 
parameter must be set to TRUE for this operation'.
Search Words: 
============= 
DBLINK SEQUENCE GLOBAL_NAMES  ORA-2069
Solution Description: 
===================== 
Use the following workaround: 
1. Create a 'temporary' table on the local database for holding the inserts. 
2. Insert the entire row into this table. 
3. Insert the 'temporary' row from the temporary table to the remote table. 
4. Delete the 'temporary' row from the temporary table.

This is slower but it will get around the problem. 
--OR--
Set GLOBAL_NAMES=TRUE in the init<SID>.ora
Solution Explanation:
=====================
The first workaround bypasses the remote node having to make a 
call back to the local instance to reference the local sequence.

沒有留言: