今天开发给发邮件过来说一个存储过程想通过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参数可以在线进行修改的
|
1
2
3
4
5
6
|
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
|
1
2
3
4
5
6
7
8
9
|
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的存储过程
|
1
2
3
4
5
6
7
8
|
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.
|
执行这个存储过程，观察结果，数据可以正常插入
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
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
|
创建一个序列，并修改上面的存储过程
|
1
2
3
4
5
6
7
8
9
10
11
12
|
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
|
1
2
3
4
5
6
7
8
|
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参数，再次执行存储过程，又出现了新的错误：说两端的数据库名不一致。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
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
.
|
那现在问题来了，实际生产中源端和目标端的数据库名肯定是不一致的，所以修改这个参数并不能解决这个问题。
只能想其他的办法来绕过这个错误，这里给开发提了两个建议：
1、把存储过程部署到目标端来避免远程insert中调用sequence
2、在源端存储过程中引入临时表，先把数据插入临时表，再从临时表插入到远端表。
在MOS上搜到了一个相关文档(ORA-02069 DURING REMOTE INSERT OF A LOCAL SEQUENCE (文档 ID 1047673.6))跟我们的问题描述一致。
官方文档：http://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams098.htm#REFRN10065
本文转自hbxztc 51CTO博客，原文链接：http://blog.51cto.com/hbxztc/1907253，如需转载请自行联系原作者
![2024Mysql And Redis基础与进阶操作系列（9）作者——LJS[含MySQL存储过程之局部、系统变量、参数传递、流程控制-判断/case具体详步骤；注意点及常见报错问题所对应的解决方法]](https://ucc.alicdn.com/pic/developer-ecology/56pfyutwi4tv4_c53df19098d2427391fe36eeb35f84c8.png?x-oss-process=image/resize,h_160,m_lfit)