[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')
