ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 10g synonym也可以invalid

10g synonym也可以invalid

原创 Linux操作系统 作者:v_fantasy 时间:2009-10-27 14:23:07 0 删除 编辑

10g之前,同义词从来不会invalid,因为没有依赖关系,10g中synonym和base之间有了依赖关系,具体可以查看视图DBA_DEPENDENCIES;

导致synonym失效的原因见如下几种:

This can happen for many reasons. Some of them are
1. You created a synonym on non-existing object by mistake. For example, you created a synonym on SCOTT.DEPT where either the SCOTT schema in not present or the DEPT table is missing.
2. You dropped an object but you did not drop the synonyms referencing the object.
3. You dropped a user, but you did not drop synonyms referencing the objects owned by that user.

4.synonym for partitioned table,在partition被add或drop之后也会变成invalid

When an object is dropped, synonyms referring to the object are not dropped. This script. generates DDL to drop synonyms whose translation is no longer valid.
USE WITH CAUTION.


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)
/

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16158219/viewspace-617502/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-10-07

  • 博文量
    98
  • 访问量
    178678