Oracle存储过程报错ORA-02069: global_names parameter must be set to TRUE for this operation

201次阅读

共计 2677 个字符，预计需要花费 7 分钟才能阅读完成。

今天开发给发邮件过来说一个存储过程想通过 dblink 往目标库 insert 数据，但报错 ORA-02069: global_names parameter must be set to TRUE for this operation，想让我根据错误提示在数据库上修改 global_names 参数修改为 True。

先来看看官方文档是如何介绍这个参数的：

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.

从下面的查询可以看出 global_names 参数可以在线进行修改的

zx@TEST>col name for a30
zx@TEST>select name,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name=’global_names’;

NAME ISSES_MODIFIABL ISSYS_MODIFIABLE
—————————— ————— —————————
global_names TRUE IMMEDIATE

看完了这个参数，再来看看开发的存储过程代码，其中 insert 语句中是用到了一个序列，所以导致了这个报错。先在测试数据库上创建了一个简单的存储过程来模拟现再这个问题

创建一个 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

先创建一个不带序列的远程 insert 的存储过程

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.

执行这个存储过程，观察结果，数据可以正常插入

zx@TEST>select * from t2@link_orcl;

no rows selected

zx@TEST>exec pro_a;

PL/SQL procedure successfully completed.

zx@TEST>select c1 from t2@link_orcl;

C1
—
a

创建一个序列，并修改上面的存储过程

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,n1) values(‘a’,seq_a.nextval);
4 commit;
5 end;
6 /

Procedure created.

执行修改后的存储过程，重现上面的错误 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 operation
ORA-06512: at “ZX.PRO_A”, line 3
ORA-06512: at line 1

先在 session 层面修改 global_names 参数，再次执行存储过程，又出现了新的错误：说两端的数据库名不一致。

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 ORCL
ORA-06512: at “ZX.PRO_A”, line 3
ORA-06512: at line 1

zx@TEST>!oerr ora 2085
02085, 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.