链接： http://blog.itpub.net/28602568/viewspace-1321645/

标题：Oracle 通过dblink访问表,因synonym失效，引起ORA-04045/ORA-16000/ORA-02063

前景介绍：

环境：RAC（FWSB用户[读写],FWSB_B用户[只提供plsql连接查询]）、DG环境【只读模式】 ..

需要：多个测试环境与生产环境脚本对比数据..为了不与RAC环境争抢资源，将在各测试环境创建连接DG环境的dblink(db_fwsb_b)

某天同事说查询表报错【ORA-04045/ORA-16000/ORA-02063】

最终知识点

1、synonym 映射的表重建会导致synonym失效；

2、10g开始失效对象在再次调用时自动会置为有效[包括：synonym/存储等]；

一、模拟报错

SQL> SELECT * FROM LOTTERY@db_fwsb ; --测试环境通过dblink查询生产环境数据

ORA-04045: 在重新编译/重新验证 FWSB_B.LOTTERY 时出错

ORA-16000: 打开数据库以进行只读访问

ORA-02063: 紧接着 2 lines (起自 DB_FWSB)

SQL> 查询用到的dblink,权限

SQL> SELECT * FROM DBA_DB_LINKS WHERE DB_LINK IN ('DB_BACKUP','DB_FWSB');

OWNER DB_LINK USERNAME HOST

------------------------------ -------------------- ------------------- ----------------------------

PUBLIC DB_FWSB FWSB_B DG 环境 --只读

SYS DB_BACKUP FWSB_B RAC环境 --可编辑fwsb_b环境synonym

SQL>

二、报错分析：

RAC/DG环境查询对象有效状态：

SQL> SELECT o.owner,o.object_name,o.object_type,o.status,o.created,o.last_ddl_time,o.timestamp FROM dba_objects o where o.object_name = 'LOTTERY';

OWNER OBJECT_NAME OBJECT_TYPE STATUS CREATED LAST_DDL_TIME TIMESTAMP

-------------------- --------------------------------------------------------------------

FWSB_B LOTTERY SYNONYM INVALID 2014/6/8 17 2014/6/8 17:5 2014-06-08:17:55:21

FWSB LOTTERY TABLE VALID 2014/11/2 3 2014/11/2 4:0 2014-11-02:03:05:18

synonym对应的表：

SQL> SELECT * FROM DBA_SYNONYMS S WHERE S.SYNONYM_NAME IN ('LOTTERY');

OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK

----------------- ----------------- ------------------------------ ------------------------------ ----------

FWSB_B LOTTERY FWSB LOTTERY

SQL>

三、分析总结

不难看出，fwsb_b.LOTTERY的synonym是失效状态导致测试环境通过dblink查询生产环境数据报错ORA-04045/ORA-16000/ORA-02063.

解决方案：

① 重新创建同义词【create or replace synonym fwsb_b.lottery for fwsb.lottery；】

② 重新编译同义词【ALTER SYNONYM fwsb_b.LOTTERY COMPILE；】、在有权限的用户下调用一次...

问题：

1、synonym为什么会变invalid状态？

2、fwsb.lottery表存在，在DG环境查询fwsb_b.lottery同义词报错，而在rac环境重新查询fwsb_b.lottery同义词不报错呢？

难道是和[存储等的表被重建了，调用会重新编辑] 一样？synonym调用也会先试着重新编辑？

带着疑问测试下： 重新创建表 对应表的synonym是否会失效，且重新查询synonym是否会自动将失效的synonym置为有效：

RAC环境：

SQL> SELECT O.OWNER,O.OBJECT_NAME,O.OBJECT_TYPE,O.STATUS,O.CREATED,O.LAST_DDL_TIME,O.TIMESTAMP FROM DBA_OBJECTS O WHERE O.OBJECT_NAME = 'LOTTERY';

OWNER OBJECT_NAME OBJECT_TYPE STATUS CREATED LAST_DDL_TIME TIMESTAMP

------------------------------ ------------------------------ ------------------- ------- ----------- ------------- -------------------

FWSB_B LOTTERY SYNONYM VALID 2014/11/5 1 2014/11/5 11: 2014-11-05:11:18:58

FWSB LOTTERY TABLE VALID 2014/11/5 1 2014/11/5 11: 2014-11-05:11:18:58

SQL> drop table fwsb.lottery purge;

Table dropped

SQL> create table fwsb.lottery as select * from dual;

Table created

SQL>

SQL> SELECT O.OWNER,O.OBJECT_NAME,O.OBJECT_TYPE,O.STATUS,O.CREATED,O.LAST_DDL_TIME,O.TIMESTAMP FROM DBA_OBJECTS O WHERE O.OBJECT_NAME = 'LOTTERY';

OWNER OBJECT_NAME OBJECT_TYPE STATUS CREATED LAST_DDL_TIME TIMESTAMP

------------------------------ ------------------------------ ------------------- ------- ----------- ------------- -------------------

FWSB_B LOTTERY SYNONYM INVALID 2014/11/5 1 2014/11/5 11: 2014-11-05:11:18:58

FWSB LOTTERY TABLE VALID 2014/11/5 1 2014/11/5 11: 2014-11-05:11:18:58

SQL>

测试环境：

SQL> SELECT * FROM LOTTERY@db_fwsb ; --使用连接DG的dblink查询测试下

SELECT * FROM LOTTERY@db_fwsb

ORA-04045: 在重新编译/重新验证 FWSB_B.LOTTERY 时出错

ORA-16000: 打开数据库以进行只读访问

ORA-02063: 紧接着 2 lines (起自 DB_FWSB)

SQL> SELECT count(1) FROM LOTTERY@db_backup; --因为【db_backup有对lottery同义词重新编译权限】所以select 调用就相当于重新编译synonym 了...

COUNT(1)

----------

99

SQL>

SQL> SELECT O.OWNER,O.OBJECT_NAME,O.OBJECT_TYPE,O.STATUS,O.CREATED,O.LAST_DDL_TIME,O.TIMESTAMP FROM DBA_OBJECTS O WHERE O.OBJECT_NAME = 'LOTTERY';

OWNER OBJECT_NAME OBJECT_TYPE STATUS CREATED LAST_DDL_TIME TIMESTAMP

------------------------------ --------------- --------------- ------------------- ------- ----------- ------------- -------------------

FWSB_B LOTTERY SYNONYM VALID 2014/11/5 1 2014/11/5 11: 2014-11-05:11:28:38

FWSB LOTTERY TABLE VALID 2014/11/5 1 2014/11/5 11: 2014-11-05:11:27:40

SQL>

SQL> SELECT * FROM LOTTERY@db_fwsb; --再使用连接DG的dblink查询可以了

DUMMY

-----

X

SQL>

扩展查询：

SQL> SELECT s.owner_name, s.name, s.type_num, s.type_name, s.status, s.ctime FROM KU$_SCHEMAOBJ_VIEW s【或者SYS.KU$_SYNONYM_VIEW】 where name IN ('LOTTERY');

OWNER_NAME NAME TYPE_NUM TYPE_NAME STATUS CTIME

------------------------------ ------------------------------ ---------- ---------------------------- ---------- -------------------

FWSB LOTTERY 2 TABLE 1 2014-11-02 04:23:19

FWSB_B LOTTERY 5 SYNONYM (有效状态是1) 5 2014-06-08 17:55:21

--KU$_SCHEMAOBJ_VIEW【或者SYS.KU$_SYNONYM_VIEW】没找到相关资料

总结：

SYNONYM和PROCEDURE..等对象一样，对象中的表重新创建后，失效的SYNONYM/PROCEDURE..再调用时会自动重新编辑

DG环境查下synonym 报错是因为DG只读环境没有权限执行ALTER SYNONYM fwsb_b.LOTTERY COMPILE；且ORA-04045带颜色部分已经编辑很清楚了..重新编辑出错

所以通过实验结合报错提示明了了吧！

此条目发表在 Oracle 分类目录。将固定连接加入收藏夹。

ITPub博客

Oracle 通过dblink访问表,因synonym失效，引起ORA-04045/ORA-16000/ORA-02063

最新文章