[20150503]关于同义词问题2.txt

--节前在做数据库同义词整理时,写了一篇blog
--[20150430]同义词使用问题.txt
http://blog.itpub.net/267265/viewspace-1612866/

--在家想如何能快速找到无效的同义词,剔除出去.
--我想最简单方法就是执行一次看看是否报错.

SCOTT@test01p>  desc tt
ERROR:
ORA-04043: object tt does not exist

SCOTT@test01p> create synonym syn_public_tt1 for tt;
Synonym created.

SCOTT@test01p> create synonym syn_public_tt2 for syn_public_tt1;
Synonym created.

SCOTT@test01p>
select 'select * from '||decode(owner,'PUBLIC','',owner||'.')||synonym_name||' where 1=0;' c60  from dba_synonyms where table_owner='SCOTT'
C60
------------------------------------------------------------
select * from SCOTT.SYN_PUBLIC_TT1 where 1=0;
select * from SCOTT.SYN_PUBLIC_TT2 where 1=0;

--将以上结果记录到1个文本文件aa.sql中:

SCOTT@test01p> spool d:\a.txt
SCOTT@test01p> @d:\aa.sql
select * from SCOTT.SYN_PUBLIC_TT1 where 1=0
                    *
ERROR at line 1:
ORA-00980: synonym translation is no longer valid

select * from SCOTT.SYN_PUBLIC_TT2 where 1=0
                    *
ERROR at line 1:
ORA-00980: synonym translation is no longer valid

SCOTT@test01p> spool off

--在使用shell工具编辑一下,很容易过滤有问题的同义词,再使用vim编辑删除就ok了.

d:\>grep -B3 ORA-00980 a.txt | grep "^select" | cut -f4 -d" "
SCOTT.SYN_PUBLIC_TT1
SCOTT.SYN_PUBLIC_TT2

--估计写PL/SQL也可以,一次性的东西还是放弃....