远端数据库读取插入到另一个远端数据库

在ITPUB上看到一个有趣的问题，结果发现自己对数据库链的了解还不是很透彻：http://www.itpub.net/showthread.php?s=&threadid=523321

 

下面这个测试需要3个数据库，下面看一下3个数据库的环境：

本地数据库：

SQL> CONN YANGTK/YANGTK已连接。
SQL> SELECT * FROM GLOBAL_NAME;

GLOBAL_NAME
--------------------------------------------------------------------------
YTK

SQL> SHOW PARAMETER GLOBAL_NAME

NAME TYPE VALUE
------------------------------------ ----------- -------------------------
global_names boolean FALSE

远端数据库1：

SQL> CONN SYSTEM@FJREP输入口令: ************已连接。
SQL> SELECT * FROM GLOBAL_NAME;

GLOBAL_NAME
--------------------------------------------------------------------------
FJREP

SQL> SHOW PARAMETER GLOBAL_NAME

NAME TYPE VALUE
------------------------------------ ----------- -------------------------
global_names boolean FALSE

远端数据库2：

SQL> CONN SYSTEM@BJREP输入口令: *****已连接。
SQL> SELECT * FROM GLOBAL_NAME;

GLOBAL_NAME
-------------------------------------------------------------------------
BJREP.US.ORACLE.COM

SQL> SHOW PARAMETER GLOBAL_NAME

NAME TYPE VALUE
------------------------------------ ----------- ------------------------
global_names boolean FALSE

通过检查设置发现，三个数据库的GLOBAL_NAME都是FALSE，那么就是说对数据库链的名称应该没有任何的要求。

首先构建一下测试的环境：

SQL> CONN REPORT/REPORT@BJREP已连接。
SQL> CREATE TABLE TEST (NAME VARCHAR2(30));

表已创建。

SQL> INSERT INTO TEST VALUES ('BJREP');

已创建 1 行。

SQL> COMMIT;

提交完成。

SQL> CONN REPORT/REPORT@FJREP已连接。
SQL> CREATE TABLE TEST (NAME VARCHAR2(30));

表已创建。

SQL> INSERT INTO TEST VALUES ('FJREP');

已创建 1 行。

SQL> COMMIT;

提交完成。

下面回到本地数据库创建两个远端数据库的数据库链。注意BJREP的数据库链名称和远端数据库的GLOBAL_NAME并不一致，但是由于GLOBAL_NAME设置为FALSE，因此这个数据库可以正常工作。

SQL> CREATE DATABASE LINK FJREP CONNECT TO REPORT IDENTIFIED BY REPORT USING 'FJREP';

数据库链接已创建。

SQL> CREATE DATABASE LINK BJREP CONNECT TO REPORT IDENTIFIED BY REPORT USING 'BJREP';

数据库链接已创建。

SQL> SELECT COUNT(*) FROM TEST@BJREP;

COUNT(*)
----------
1

SQL> SELECT COUNT(*) FROM TEST@FJREP;

COUNT(*)
----------
1

SQL> INSERT INTO TEST@FJREP VALUES ('FROM LOCAL');

已创建 1 行。

SQL> INSERT INTO TEST@BJREP VALUES ('FROM LOCAL');

已创建 1 行。

SQL> COMMIT;

提交完成。

下面测试在本地环境中建立通过数据库链从远端数据库取数据并插入到远端数据库中：

SQL> INSERT INTO TEST@BJREP SELECT * FROM TEST@FJREP;
INSERT INTO TEST@BJREP SELECT * FROM TEST@FJREP
*第 1 行出现错误:
ORA-02019: 未找到远程数据库的连接说明
ORA-02063: 紧接着line（源于YTK）
ORA-02063: 紧接着 2 lines (起自 BJREP)

错误出现了，但是如果将二者的位置反过来却不会出现错误。

SQL> INSERT INTO TEST@FJREP SELECT * FROM TEST@BJREP;

已创建2行。

而且发现如果是对同一个远端数据库读取和插入是不会有问题的：

SQL> INSERT INTO TEST@FJREP SELECT * FROM TEST@FJREP;

已创建4行。

SQL> INSERT INTO TEST@BJREP SELECT * FROM TEST@BJREP;

已创建2行。

怀疑是由于Oracle要处理多个远端数据库，因此要求INSERT语句中的远端数据库的名称必须和GLOBAL_NAME中设置的一致。

奇怪的是，这里创建了BJREP.US.ORACLE.COM数据库链并不起作用，而是需要创建FJREP.US.ORACLE.COM。

SQL> CREATE DATABASE LINK BJREP.US.ORACLE.COM CONNECT TO REPORT IDENTIFIED BY REPORT USING 'BJREP';

数据库链接已创建。

SQL> INSERT INTO [e-mail] SELECT * FROM TEST@FJREP;
INSERT INTO [e-mail] SELECT * FROM TEST@FJREP
*第 1 行出现错误:
ORA-02019: 未找到远程数据库的连接说明
ORA-02063: 紧接着line（源于YTK）
ORA-02063: 紧接着 2 lines (起自 BJREP.US.ORACLE.COM)

SQL> DROP DATABASE LINK BJREP.US.ORACLE.COM;

数据库链接已删除。

SQL> CREATE DATABASE LINK FJREP.US.ORACLE.COM CONNECT TO REPORT IDENTIFIED BY REPORT USING 'FJREP';

数据库链接已创建。

SQL> INSERT INTO TEST@BJREP SELECT * FROM [e-mail];

已创建8行。

更奇怪的是，只有建立了这个数据库链，甚至在SELECT的时候不指定它，而还是用原来的数据库链都可以：

SQL> INSERT INTO TEST@BJREP SELECT * FROM TEST@FJREP;

已创建8行。

SQL> ROLLBACK;

回退已完成。

可以通过SQL_TRACE来跟踪可以发现Oracle实际找的数据库链的名称。

SQL> DROP DATABASE LINK FJREP.US.ORACLE.COM;
DROP DATABASE LINK FJREP.US.ORACLE.COM
*第 1 行出现错误:
ORA-02018: 同名的数据库链接具有打开的连接

SQL> DISC从 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options 断开
SQL> CONN YANGTK/YANGTK已连接。
SQL> DROP DATABASE LINK FJREP.US.ORACLE.COM;

数据库链接已删除。

SQL> CREATE DATABASE LINK BJREP.US.ORACLE.COM CONNECT TO REPORT IDENTIFIED BY REPORT USING 'BJREP';

数据库链接已创建。

SQL> ALTER SESSION SET SQL_TRACE = TRUE;

会话已更改。

SQL> INSERT INTO [e-mail] SELECT * FROM TEST@FJREP;
INSERT INTO [e-mail] SELECT * FROM TEST@FJREP
*第 1 行出现错误:
ORA-02019: 未找到远程数据库的连接说明
ORA-02063: 紧接着line（源于YTK）
ORA-02063: 紧接着 2 lines (起自 BJREP.US.ORACLE.COM)

SQL> INSERT INTO TEST@BJREP SELECT * FROM TEST@FJREP;
INSERT INTO TEST@BJREP SELECT * FROM TEST@FJREP
*第 1 行出现错误:
ORA-02019: 未找到远程数据库的连接说明
ORA-02063: 紧接着line（源于YTK）
ORA-02063: 紧接着 2 lines (起自 BJREP)

SQL> ALTER SESSION SET SQL_TRACE = FALSE;

会话已更改。

从TRACE文件中摘取一段就可以发现：

PARSING IN CURSOR #8 len=61 dep=0 uid=56 oct=2 lid=56 tim=3070147813 hv=923577066 ad='16ac9c48'
INSERT INTO [e-mail] SELECT * FROM TEST@FJREP
END OF STMT
PARSE #8:c=0,e=23515,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=3070147808
=====================
PARSE ERROR #2:len=43 dep=1 uid=56 oct=3 lid=56 tim=3070157311 err=2019
SELECT * FROM "TEST"@"FJREP.US.ORACLE.COM"
=====================
PARSING IN CURSOR #8 len=47 dep=0 uid=56 oct=2 lid=56 tim=3075496870 hv=3946478946 ad='16ae2944'
INSERT INTO TEST@BJREP SELECT * FROM TEST@FJREP
END OF STMT
PARSE #8:c=0,e=27874,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=3075496865
=====================
PARSE ERROR #7:len=43 dep=1 uid=56 oct=3 lid=56 tim=3075504320 err=2019
SELECT * FROM "TEST"@"FJREP.US.ORACLE.COM"
*** 2006-04-21 12:57:02.125

Oracle在这里真正找的数据库链是FJREP.US.ORACLE.COM。

引用：http://yangtingkun.itpub.net/post/468/71345