玉面飞龙的BLOG

Invalid Synonyms in 10g

September 19th, 2007 | Categories: Boring | Tags:

10g以前，同义词在dba_objects中的状态始终是VALID(Synonyms never become invalid),不管其所引用的对象是否存在。可以使用这个脚本查找无效的同义词，转贴如下

rem
rem Exludes SYS and SYSTEM users
rem
select 'drop '||decode (s.owner,'PUBLIC','PUBLIC SYNONYM ',
'SYNONYM'||s.owner||'.')||s.synonym_name||';'
from dba_synonyms s
where table_owner not in('SYSTEM','SYS')
and db_link is null
and not exists
   (select 1
   from dba_objects o
   where s.table_owner=o.owner
   and s.table_name=o.object_name)
/

在10g中，当同义词所引用的对象不存在时，同义词在dba_objects的状态将变为INVALID,测试如下

首先看9i的测试

SQL> create table binzhang_tab as select * from dba_objects;
Table created.

SQL> create public synonym binzhang_syn for binzhang_tab;
Synonym created.

SQL> SELECT *
   FROM DBA_DEPENDENCIES
   WHERE name like 'BINZHANG%' or
   REFERENCED_NAME like 'BINZHANG%';

no rows selected

SQL> select status from dba_objects where object_name='BINZHANG_SYN';
STATUS
-------
VALID

SQL> drop table BINZHANG_TAB;
Table dropped.

SQL> select status from dba_objects where object_name='BINZHANG_SYN';
STATUS
-------
VALID

从9i的测试中看到，同义词和表之间没有依赖关系(DEPENDENCIES).

而在10g中，则不同。同义词HARD REFERENCE表。对表作DDL操作，将会Invalid同义词。

SQL> SELECT *
   FROM DBA_DEPENDENCIES
   WHERE name like 'BINZHANG%' or
   REFERENCED_NAME like 'BINZHANG%';

OWNER NAME TYPE REFERENCE REFERENCED_NAME REFERENCED REFER DEPEN
--------- --------------- ---------- --------- --------------- ---------- ----- -----
PUBLIC BINZHANG_SYN SYNONYM ORACLE BINZHANG_TAB TABLE HARD

SQL> select status from dba_objects where object_name='BINZHANG_SYN';
STATUS
-------
VALID

SQL> alter table binzhang_tab add a number;
Table altered.

SQL> select status from dba_objects where object_name='BINZHANG_SYN';
STATUS
-------
INVALID

SQL> select count(*) from BINZHANG_SYN;
COUNT(*)
----------
   29012

SQL> select status from dba_objects where object_name='BINZHANG_SYN';
STATUS
-------
VALID

SQL> drop table binzhang_tab;
Table dropped.

SQL> select status from dba_objects where object_name='BINZHANG_SYN';
STATUS
-------
INVALID

当在事务频繁的对象上作某些DDL操作的时候，这因为同义词增加的dependency就会增加改动后编译的风险(library cache pin&lock,dead lock等).