同时使用数据库链和序列时应注意的几点

转载于：http://blog.itpub.net/post/468/9547

 

 

 

 

 

 

在一条语句中如果同时包括数据库链和序列，就会出现潜在的问题，而Oracle的文档在这里并没有描述清楚。

下面通过一个例子对同时包括数据库链和序列的几种情况分别进行说明。

首先，构造一个测试的环境（两个数据库的GLOBAL_NAMES均为TRUE）：

SQL> conn scott/tiger@yangtk
已连接。
SQL> create table test_on_yangtk (id number);

表已创建。

SQL> insert into test_on_yangtk values (1);

已创建 1 行。

SQL> commit;

提交完成。

SQL> create sequence seq_on_yangtk;

序列已创建。

SQL> conn yangtk/yangtk@test4
已连接。
SQL> create table test_on_test4 (id number);

表已创建。

SQL> insert into test_on_test4 values (1);

已创建 1 行。

SQL> commit;

提交完成。

SQL> create sequence seq_on_test4;

序列已创建。

SQL> create database link yangtk connect to scott identified by tiger using 'yangtk';

数据库链接已创建。

对于简单的查询语句，可以分为四种情况。

1.从本地表中读取数据，并引用本地序列。

SQL> select seq_on_test4.nextval from test_on_test4;

   NEXTVAL
----------
         1

2.从本地表中读取数据，但访问远端序列，这时，需要在序列后面加上数据库链名称。

SQL> select seq_on_yangtk.nextval@yangtk from test_on_test4;

   NEXTVAL
----------
         1

3.读取远端数据表中数据，同时访问远端序列。

SQL> select seq_on_yangtk.nextval@yangtk from test_on_yangtk@yangtk;

   NEXTVAL
----------
         2

4.读取远端数据表中数据，但是访问本地序列。

SQL> select seq_on_test4.nextval from test_on_yangtk@yangtk;

   NEXTVAL
----------
         2

通过测试，这四种情况工作都很正常。

下面，考虑分布式事务——以INSERT INTO SELECT为例。

由于INSERT的表可以是本地表，也可以是远端表，对于每种情况，对应的子查询语句都可能包含上面四种情况，下面就一一进行分析。

一、插入本地表

1.从本地表中读取数据，并引用本地序列

SQL> insert into test_on_test4 select seq_on_test4.nextval from test_on_test4;

已创建 1 行。

SQL> rollback;

回退已完成。

2.从本地表中读取数据，但访问远端序列

SQL> insert into test_on_test4 select seq_on_yangtk.nextval@yangtk from test_on_test4;

已创建 1 行。

SQL> rollback;

回退已完成。

3.读取远端数据表中数据，同时访问远端序列。

SQL> insert into test_on_test4 select seq_on_yangtk.nextval@yangtk from test_on_yangtk@yangtk;

已创建 1 行。

SQL> rollback;

回退已完成。

4.读取远端数据表中数据，但是访问本地序列。

SQL>  insert into test_on_test4 select seq_on_test4.nextval from test_on_yangtk@yangtk;

已创建 1 行。

SQL> rollback;

回退已完成。

经测试，插入本地表的四种情况，没有出错。

二、插入远端表

1.从本地表中读取数据，并引用本地序列

SQL> insert into test_on_yangtk@yangtk select seq_on_test4.nextval from test_on_test4;
insert into test_on_yangtk@yangtk select seq_on_test4.nextval from test_on_test4
*
ERROR 位于第 1 行:
ORA-02289: 序列（号）不存在
ORA-02063: 紧接着line（源于TEST4）
ORA-02063: 紧接着2 lines（源于YANGTK）

2.从本地表中读取数据，但访问远端序列

SQL> insert into test_on_yangtk@yangtk select seq_on_yangtk.nextval@yangtk from test_on_test4;

已创建 1 行。

SQL> rollback;

回退已完成。

3.读取远端数据表中数据，同时访问远端序列。

SQL> insert into test_on_yangtk@yangtk select seq_on_yangtk.nextval@yangtk from test_on_yangtk@yangtk;

已创建 1 行。

SQL> rollback;

回退已完成。

4.读取远端数据表中数据，但是访问本地序列。

SQL> insert into test_on_yangtk@yangtk select seq_on_test4.nextval from test_on_yangtk@yangtk;
insert into test_on_yangtk@yangtk select seq_on_test4.nextval from test_on_yangtk@yangtk
*
ERROR 位于第 1 行:
ORA-02289: 序列（号）不存在
ORA-02063: 紧接着line（源于TEST4）
ORA-02063: 紧接着2 lines（源于YANGTK）

根据测试，第一种情况和第四种情况出现了相同的错误。

在和其他所有没有报错的情况比较后，可以得到这样的结论：当插入远端数据表，并使用本地序列时会出现错误。

Oracle的error文档上这样描述2289错误的：

ORA-02289 sequence does not exist
Cause: The specified sequence does not exist, or the user does not have the required privilege to perform this operation.
Action: Make sure the sequence name is correct, and that you have the right to perform the desired operation on this sequence.

根据目前的错误以及Oracle给出的错误原因，初步怀疑对于这种插入远端数据表的分布式事务，实际上是在远端上执行的。因此YANGTK上的scott用户找不到SEQ_ON_TEST4这个序列。

给YANGTK上的scott用户增加一个指向TEST4上yangtk用户的数据库链。

SQL> conn scott/tiger@yangtk
已连接。
SQL> create database link test4 connect to yangtk identified by yangtk using 'test4';

数据库链接已创建。

SQL> conn yangtk/yangtk@test4
已连接。
SQL> insert into test_on_yangtk@yangtk select seq_on_test4.nextval from test_on_test4;

已创建 1 行。

SQL> rollback;

回退已完成。

SQL> insert into test_on_yangtk@yangtk select seq_on_test4.nextval from test_on_yangtk@yangtk;

已创建 1 行。

SQL> rollback;

回退已完成。

建立数据库链之后，重新执行错误的语句，这次执行没有出现错误。

如果在一条语句中同时使用数据库链和序列，这时候应当小心，你可能不仅需要一条到远端的数据库链，还可能需要一个从远端到本地的数据库链。