[20150430]同义词使用问题.txt
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SCOTT@test> desc tt
ERROR:
ORA-04043: object tt does not exist
SCOTT@test> create synonym syn_public_tt1 for tt;
Synonym created.
SCOTT@test> create synonym syn_public_tt2 for syn_public_tt1;
Synonym created.
--居然可以建议同义词.
SCOTT@test> select * from syn_public_tt1;
select * from syn_public_tt1
*
ERROR at line 1:
ORA-00980: synonym translation is no longer valid
SCOTT@test> host oerr ora 980
00980, 00000, "synonym translation is no longer valid"
// *Cause: A synonym did not translate to a legal target object. This
// could happen for one of the following reasons:
// 1. The target schema does not exist.
// 2. The target object does not exist.
// 3. The synonym specifies an incorrect database link.
// 4. The synonym is not versioned but specifies a versioned
// target object.
// *Action: Change the synonym definition so that the synonym points at
// a legal target object.
SCOTT@test> create table tt as select * from dept ;
Table created.
SCOTT@test> select * from syn_public_tt1;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS1
30 SALES CHICAGO
40 OPERATIONS BOSTON
SCOTT@test> select * from syn_public_tt2;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS1
30 SALES CHICAGO
40 OPERATIONS BOSTON
--这个问题非常讨厌,如果不小心把表删除了,而对应的同一词依旧存在.
SCOTT@test> drop table tt purge;
Table dropped.
SCOTT@test> select * from dba_synonyms where owner='SCOTT';
OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK
------ ------------------------------ ------------------------------ -------------- --------------------
SCOTT SYN_PUBLIC_TT1 SCOTT TT
SCOTT PRODUCTS SH PRODUCTS
SCOTT SYN_PUBLIC_TT2 SCOTT SYN_PUBLIC_TT1
--也没有办法标识是否有效.而且麻烦的是同义词可以定义同义词的同义词,这样定位脚本要写的有点复杂,要进行一些递归.
SCOTT@test> column table_name format a20
SCOTT@test> select * from dba_synonyms where owner='SCOTT' and (table_owner,table_name) not in (select owner,table_name from dba_tables);
OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK
------ ------------------------------ ------------------------------ -------------------- --------------------
SCOTT SYN_PUBLIC_TT1 SCOTT TT
SCOTT SYN_PUBLIC_TT2 SCOTT SYN_PUBLIC_TT1
--视乎这样是对的,如果我建立表tt呢?
SCOTT@test> create table tt as select * from dept ;
Table created.
SCOTT@test> select * from dba_synonyms where owner='SCOTT' and (table_owner,table_name) not in (select owner,table_name from dba_tables);
OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK
------ ------------------------------ ------------------------------ -------------------- --------------------
SCOTT SYN_PUBLIC_TT2 SCOTT SYN_PUBLIC_TT1
--很明显这个脚本存在问题.
SELECT *
FROM dba_synonyms
WHERE owner = 'SCOTT'
AND (table_owner, table_name) NOT IN (SELECT owner, table_name
FROM dba_tables)
AND (table_owner, table_name) NOT IN (SELECT owner, synonym_name
FROM dba_synonyms);
SCOTT@test> create synonym syn_public_tt3 for syn_public_tt2;
Synonym created.
SCOTT@test> drop synonym syn_public_tt1 ;
Synonym dropped.
SELECT *
FROM dba_synonyms
WHERE owner = 'SCOTT'
AND (table_owner, table_name) NOT IN (SELECT owner, table_name FROM dba_tables)
AND (table_owner, table_name) NOT IN (SELECT owner, synonym_name FROM dba_synonyms);
OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK
------ ------------------------------ ------------------------------ -------------------- --------------------
SCOTT SYN_PUBLIC_TT2 SCOTT SYN_PUBLIC_TT1
--^_^,又漏掉了SYN_PUBLIC_TT3,好在这种级联定义应该不会太多.
--写sql语句不是我的强项.估计要使用connect by之类的东西.
--这个问题,我最近在整理一个数据库,里面无效的同义词太多.这才有上面的测试.还好开发一般不会选择定义同义词的同义词.删除这些
--无效的同义词还是很容易的.
