[20120901]关于同义词(Synonyms).txt

    我第1次知道同义词这个概念，是自己写代码，要访问别的schema的表，前面要加schema，问了当时开发人员才知道要建立同一词。

现在想想自己都觉得好笑！

    我还记得以前优化sql，因为以dba用户进入，要执行用户的sql语句，总是无法执行，我自己总是给语句补上schema，后来才知道

命令：ALTER SESSION SET current_schema = "XXX";

    知道同义词后，为了简便（我记得以前toad 7没有像现在的版本一样，可以在执行前，选择current_schema)，我会建立对应表的

同义词。知道有一次我遇到一个问题，我看到的执行计划老是与实际执行的不符合，检查才发现原来程序中访问的表与我定义的同义词

对应的表不一致。从那以后，我很少为了优化，工作方便等原因，定义同义词。而且我自己一直很烦同义词。

1.测试环境：

SQL> select * from v$version ;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

create table t as select rownum id,'test' name from dual connect by level

create public synonym t_syn for t;

SQL> select status from dba_objects where object_name='T_SYN';

STATUS

-------

VALID

SQL> SELECT * FROM DBA_DEPENDENCIES WHERE name = 'T_SYN'  and wner='PUBLIC';

OWNER      NAME       TYPE               REFERENCED_OWNER               REFERENCED REFERENCED_TYPE    REFERENCED DEPE

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

PUBLIC     T_SYN      SYNONYM            SCOTT                          T        TABLE                         HARD

SQL> column db_link format a10

SQL> select * from DBA_synonyms where wner='PUBLIC' and SYNONYM_NAME='T_SYN';

OWNER      SYNONYM_NAME                   TABLE_OWNER                    TABLE_NAME DB_LINK

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

PUBLIC     T_SYN                          SCOTT                          T

2，修改表名：

alter table T rename to zzz;

SQL> SELECT * FROM DBA_DEPENDENCIES WHERE name = 'T_SYN'  and wner='PUBLIC';

OWNER      NAME       TYPE               REFERENCED_OWNER               REFERENCED REFERENCED_TYPE    REFERENCED DEPE

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

PUBLIC     T_SYN      SYNONYM            SCOTT                          ZZZ        TABLE                         HARD

--REFERENCED='ZZZ'?

SQL> select status from dba_objects where object_name='T_SYN';

STATUS

-------

INVALID

SQL> select * from DBA_synonyms where wner='PUBLIC' and SYNONYM_NAME='T_SYN';

OWNER      SYNONYM_NAME                   TABLE_OWNER                    TABLE_NAME DB_LINK

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

PUBLIC     T_SYN                          SCOTT                          T

--DBA_synonyms依旧指向的是表T。

SQL> select * from  t_syn;

select * from  t_syn

               *

ERROR at line 1:

ORA-00980: synonym translation is no longer valid

SQL> select status from dba_objects where object_name='T_SYN';

STATUS

-------

VALID

--??视乎修正回来了，但是执行,问题依旧。语句应该通过DBA_synonyms来定位，表T已经不存在了，自然访问错误！

SQL> select * from  t_syn;

select * from  t_syn

               *

ERROR at line 1:

ORA-00980: synonym translation is no longer valid

修改回来后一些正常！

alter table zzz rename to t;

3.删除表t：

SQL> drop table t;

SQL> select status from dba_objects where object_name='T_SYN';

STATUS

-------

INVALID

SQL> select * from DBA_synonyms where wner='PUBLIC' and SYNONYM_NAME='T_SYN';

OWNER      SYNONYM_NAME                   TABLE_OWNER                    TABLE_NAME DB_LINK

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

PUBLIC     T_SYN                          SCOTT                          T

--依旧指向表T。

SQL> SELECT * FROM DBA_DEPENDENCIES WHERE name = 'T_SYN'  and wner='PUBLIC';

OWNER      NAME       TYPE               REFERENCED_OWNER               REFERENCED REFERENCED_TYPE    REFERENCED DEPE

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

PUBLIC     T_SYN      SYNONYM            SCOTT                          BIN$yJ2b7H TABLE                         HARD

                                                                        dJ1VbgQKjA

                                                                        KGRoDQ==$0

--DBA_DEPENDENCIES的REFERENCED指向了删除的对象。

SQL> select * from t_syn;

select * from t_syn

              *

ERROR at line 1:

ORA-00980: synonym translation is no longer valid

SQL> SELECT * FROM DBA_DEPENDENCIES WHERE name = 'T_SYN'  and wner='PUBLIC';

no rows selected

--如果执行查询select * from t_syn;再查询SELECT * FROM DBA_DEPENDENCIES WHERE name = 'T_SYN'  and wner='PUBLIC';

--结果没有行返回。看来有后台进程做清理工作。

4.建立一张新表T，里面的字段与原来不同。并且做一个跟踪看看。

SQL> create table t as select * from emp;

SQL> select object_id,data_object_id,status from dba_objects where object_name='T_SYN';

 OBJECT_ID DATA_OBJECT_ID STATUS

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

    106164                INVALID

SQL> select * from DBA_synonyms where wner='PUBLIC' and SYNONYM_NAME='T_SYN';

OWNER      SYNONYM_NAME                   TABLE_OWNER                    TABLE_NAME DB_LINK

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

PUBLIC     T_SYN                          SCOTT                          T

SQL> SELECT * FROM DBA_DEPENDENCIES WHERE name = 'T_SYN'  and wner='PUBLIC';

no rows selected

SQL> alter session set events '10046 trace name context forever, level 12'

SQL> select * from t_syn where rownum

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO

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

      8000 ZWS        CLERK           7782 2001-01-23 00:00:00       1700                    60

--执行成功！

SQL> alter session set events '10046 trace name context forever, level 12'

SQL> SELECT * FROM DBA_DEPENDENCIES WHERE name = 'T_SYN'  and wner='PUBLIC';

OWNER      NAME       TYPE               REFERENCED REFERENCED REFERENCED_TYPE    DEPE

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

PUBLIC     T_SYN      SYNONYM            SCOTT      T          TABLE              HARD

--从跟踪文件发现：

SQL ID: 1xb9vhvwp9g2s

Plan Hash: 0

ALTER PUBLIC SYNONYM "T_SYN" COMPILE

insert into dependency$(d_obj#,d_timestamp,order#,p_obj#,p_timestamp,

  property, d_attrs)

values

 (:1,:2,:3,:4,:5,:6, :7)

5.定义环路问题：

SQL> drop public synonym t_syn;

Synonym dropped.

SQL>  create public synonym t_syn for t_syn;

Synonym created.

--按照道理，这样建立不应该成功。

--我自己曾经遇到的问题是在expdp在备份文件时要产生一个SYS_EXPORT_TABLE_01文件，如果在toad里面没有刷新，实际上备份

--完成后会删除，如果建立同义词是对应的表已经删除就会出现以上情况。以后expdp就会出错。

ORA-39001: invalid argument value

ORA-01775: looping chain of synonyms

SQL> SELECT * FROM DBA_DEPENDENCIES WHERE name = 'T_SYN'  and wner='PUBLIC';

OWNER      NAME       TYPE               REFERENCED REFERENCED REFERENCED_TYPE    DEPE

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

PUBLIC     T_SYN      SYNONYM            PUBLIC     T_SYN      SYNONYM            HARD

SQL> select object_id,data_object_id,status from dba_objects where object_name='T_SYN';

 OBJECT_ID DATA_OBJECT_ID STATUS

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

    106164                VALID

SQL> select * from DBA_synonyms where wner='PUBLIC' and SYNONYM_NAME='T_SYN';

OWNER      SYNONYM_NA TABLE_OWNE TABLE_NAME

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

PUBLIC     T_SYN      SCOTT      T_SYN

SQL> select * from t_syn;

select * from t_syn

              *

ERROR at line 1:

ORA-01775: looping chain of synonyms

--出现环路！

还有比较特殊的情况像这样

CREATE SYNONYM s1 for s2;

CREATE SYNONYM s2 for s3;

CREATE SYNONYM s3 for s1;

--可以通过如下脚本查询:

--参考http://tamimdba.wordpress.com/2011/03/12/ora-01775-looping-chain-of-synonyms/

SELECT     owner, synonym_name, CONNECT_BY_ISCYCLE CYCLE

      FROM dba_synonyms

     WHERE CONNECT_BY_ISCYCLE > 0

CONNECT BY NOCYCLE PRIOR table_name = synonym_name AND PRIOR table_owner = owner

UNION

SELECT 'PUBLIC', synonym_name, 1

  FROM dba_synonyms

 WHERE wner = 'PUBLIC' AND table_name = synonym_name AND (table_name, table_owner) NOT IN (SELECT object_name, owner

                                                                                              FROM dba_objects

                                                                                             WHERE object_type != 'SYNONYM')