Shulou(Shulou.com)06/01 Report--

Today, the developer emailed me to say that a stored procedure wanted to insert data to the target database through dblink, but it reported an error ORA-02069: global_names parameter must be set to TRUE for this operation and wanted me to change the global_names parameter to True on the database according to the error prompt.

Let's take a look at how the official documentation describes this parameter:

GLOBAL_NAMES specifies whether a database link is required to have the same name as the database to which it connects.

If the value of GLOBAL_NAMES is false, then no check is performed. If you use or plan to use distributed processing, then Oracle recommends that you set this parameter to true to ensure the use of consistent naming conventions for databases and links in a networked environment.

From the query below, you can see that the global_names parameter can be modified online.

Zx@TEST > col name for a30zx@TEST > select name,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name='global_names' NAME ISSES_MODIFIABL ISSYS_MODIFIABLE-- global_names TRUE IMMEDIATE

After looking at this parameter, let's take a look at the developed stored procedure code, in which a sequence is used in the insert statement, which leads to this error. A simple stored procedure is created on the test database to simulate the current problem.

Create a dblink

Zx@TEST > create database link link_orcl connect to zx identified by "zx" using 'orcl';Database link created.zx@TEST > select * from dual@link_orcl;DUM---X

First create a remote insert stored procedure without a sequence

Zx@TEST > create or replace procedure pro_a as 2 begin 3 insert into t2@link_orcl (C1) values ('a'); 4 commit; 5 end; 6 / Procedure created.

Execute this stored procedure and observe the results. The data can be inserted normally.

Zx@TEST > select * from t2links links or clones no rows selectedzx@TEST > exec pro_a;PL/SQL procedure successfully completed.zx@TEST > select C1 from T2 links links clones C1Murray a

Create a sequence and modify the stored procedure above

Zx@TEST > create sequence seq_a;Sequence created.zx@TEST > create or replace procedure pro_a as 2 begin 3 insert into t2@link_orcl (C1 Magnen 1) values ('axiomatica. Nextval); 4 commit; 5 end; 6 / Procedure created.

Execute the modified stored procedure to reproduce the above error ORA-02069

Zx@TEST > exec pro_a;BEGIN pro_a; END;*ERROR at line 1:ORA-02069: global_names parameter must be set to TRUE for this operationORA-06512: at "ZX.PRO_A", line 3ORA-06512: at line 1

First modify the global_names parameters at the session level, and then execute the stored procedure again, and a new error occurs: the database names on both sides are not the same.

Zx@TEST > alter session set global_names = true;Session altered.zx@TEST > exec pro_a;BEGIN pro_a; END * ERROR at line 1:ORA-02085: database link LINK_ORCL connects to ORCLORA-06512: at "ZX.PRO_A", line 3ORA-06512: at line 1zx@TEST >! oerr ora 208502085, 00000, "database link% s connects to% s" / * Cause: a database link connected to a database with a different name.// The connection is rejected.// * Action: create a database link with the same name as the database it// connects to, or set global_names=false.

So now the problem is, in actual production, the database names of the source side and the target side must be inconsistent, so modifying this parameter will not solve this problem.

There are only other ways to get around this mistake, and here are two suggestions for developers:

1. Deploy the stored procedure to the target to avoid calling sequence in the remote insert

2. Introduce the temporary table into the source-side storage process, insert the data into the temporary table first, and then insert the data from the temporary table to the remote table.

A related document (ORA-02069 DURING REMOTE INSERT OF A LOCAL SEQUENCE (document ID 1047673.6)) was found on MOS, which is consistent with our problem description.

Official document: http://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams098.htm#REFRN10065

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.