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

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

--节前在做数据库同义词整理时,写了一篇blog

--[20150430]同义词使用问题.txt

<a href="http://blog.itpub.net/267265/viewspace-1612866/">http://blog.itpub.net/267265/viewspace-1612866/</a>

--在家想如何能快速找到无效的同义词,剔除出去.

--我想最简单方法就是执行一次看看是否报错.

SCOTT@test01p&gt;  desc tt

ERROR:

ORA-04043: object tt does not exist

SCOTT@test01p&gt; create synonym syn_public_tt1 for tt;

Synonym created.

SCOTT@test01p&gt; create synonym syn_public_tt2 for syn_public_tt1;

SCOTT@test01p&gt;

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&gt; spool d:\a.txt

SCOTT@test01p&gt; @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

SCOTT@test01p&gt; spool off

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

d:\&gt;grep -B3 ORA-00980 a.txt | grep "^select" | cut -f4 -d" "

SCOTT.SYN_PUBLIC_TT1

SCOTT.SYN_PUBLIC_TT2

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