author :skate
time: 2009/02/21
最近在两台数据库的同步的时侯,经常遇到如下的错误,下面是对这个问题解决总结:
ORA-02085 错误解决2008-12-04 13:30程序报错:
ORA-02085: database link WD2YT_ERATING.LK connects to ESUITE.NET
服务器现状:
两台数据库间通过dblink互访对方的表, 而且执行对方的存储过程.
两库的global_names参数和global_name分别如下:
A库:
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
ESUITE.NET
SQL> show parameter global_names
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean TRUE
B库:
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
WENDING.LK
SQL> show parameter global_names
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean FALSE
原因:
由于A库建Streams, 把global_names参数从false改为true, 导致A库和B库的dblink都得重建并且dblink name得和对方库的global_name完全一样.
注意两点:
1. 两个库都得重建dblink;
2. dblink name要完全等同于对方库的global_name, 不是什么实例名, 服务名.
解决方法:
--重建dblink
SQL> CREATE DATABASE LINK esuite.net CONNECT TO ERATING_YITIAN IDENTIFIED BY "aaa"USING 'yitian';
--重建基于该dblink的同义词
SQL> CREATE or replace SYNONYM TRIAL.PKG_AUTHENTICATION FOR EPASS.PKG_AUTHENTICATION@esuite.net;
可以通过如下sql获得基于原dblink的同义词的重建语句:
SQL> SELECT 'create or replace synonym '
|| owner
|| '.'
|| synonym_name
|| ' for '
|| table_owner
|| '.'
|| table_name
|| '@esuite.net'
|| ';'
FROM dba_synonyms
WHERE db_link = 'WD2YT_ERATING.LK';
如果dblink和远程数据库的global_name不一样,可以通过如下语句来解决:
SQL>alter database rename global_name to TEST.ORACLE.COM;
参考文件:
http://hi.baidu.com/edeed/blog/item/014a7becd1147ad62e2e215b.html
http://blog.csdn.net/zhpsam109/archive/2006/05/13/727314.aspx
--- end ---