最近在做正式台與測試台 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.
