ORA-02019 DURING REMOTE INSERT WITH A LOCAL SEQUENCE

APPLIES TO

Oracle Database - Enterprise Edition - Version 8.1.7.4 to 10.2.0.4 [Release 8.1.7 to 10.2]
Information in this document applies to any platform.

SYMPTOMS

Problem Description

In your TNSNAMES.ORA you have: 
… 

LB1 = 
(DESCRIPTION = 
(ADDRESS_LIST = 
(ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xx.xxx.xx )(PORT = 1521)) 
) 
(CONNECT_DATA = 
(SERVICE_NAME = lb1) 
) 
) 

you create the database link:

create database link LB1
connect to scott
identified by tiger
using 'lb1'

You create local sequence:

create sequence rl1 increment by 1 start with 1 order ;

you try:

SQL> insert into emp@lb1 (empno) values (rl1.nextval) ;
insert into emp@lb1 (empno) values (rl1.nextval)
*
ERROR at line 1:
ORA-02019: connection description for remote database not found
ORA-02063: preceding line from LB1
The database link seems to be working fine.

CAUSE

In the above query, since the insert is into a remote object, the whole sql execution is done on the remote site. 
On the remote site, the process tries to connect back to the source database using a dblink to get the value of the sequence. 
This dblink name is assumed as the global_name of the source database.

说明:用户触发器代码中,使用了本地的序列,在远程执行成功之后,远端需要反向获取本地序列的值,这个地方就
需要用到一个反向的db link,默认这个db link的名称要与global_name相同。这也就是解决方案中,第二个解决办法的处理方式。

另外,可以通过将序列创建在远端数据库,这样需要对用户的触发器进行代码上的调整,涉及序列的地方,改为类似下面的值:
wsyy_sequence.nextval@gdb

这也就是解决方案的第一个处理方式。


SOLUTION

Solution Description

1. Create the sequence on the remote db and rewrite the query as
insert into emp@lb1 (empno) values (rl1.nextval@lb1);

or

2. Create a database link from db2 ---> db1 named as the global_name of the source db.

--鸽子--