Oracle之處理synonym同義詞無效物件

Oracle之處理synonym同義詞無效物件

---

ORA-00980: synonym translation is no longer valid

1. 舉例，在sys使用者下建一個t表
[oracle@testdb1 ~]:testdb1> sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 14 09:33:29 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table t (x number);

Table created.

SQL> insert into t values (1);

1 row created.

SQL> commit;

Commit complete.



2. 再建立一個sec使用者

SQL> create user sec identified by sec;

User created.


3. 使用sec使用者，建立同名表syn_t
SQL> conn sec/sec
ERROR:
ORA-01045: user SEC lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
SQL> grant dba to sec;
SP2-0640: Not connected
SQL> conn /as sysdba;
Connected.
SQL> grant dba to sec;
Grant succeeded.

SQL> conn sec/sec
Connected.
SQL> create synonym syn_t for sys.t;     
Synonym created.

SQL> select * from syn_t;

         X
----------
         1
4. 刪除sys使用者下的t表
SQL> conn /as sysdba
Connected.
SQL> drop table t purge;

Table dropped.

SQL> conn sec/sec
SQL> conn sec/sec
Connected.

+++++++++++++++++++++++++++++++++++++++++++++++++++++
SQL> select * from syn_t;
select * from syn_t
*
ERROR at line 1:
ORA-00980: synonym translation is no longer valid
SQL>
+++++++++++++++++++++++++++++++++++++++++++++++++++++

#產生了無效同名

導致“ORA-00980”錯誤的原因：
1.刪除了資料庫物件，但是忘記刪除同名；
2.刪除一個使用者，但是忘記刪除與此使用者中相關的同名；
3.建立同名時，同名對應的資料庫物件已經被刪除掉了


表t刪除，但是同名還可以建立
SQL> show user;
USER is "SEC"

SQL> create synonym syn_public_t for sys.t;     

Synonym created.

SQL> create synonym syn_tt for sys.t;

Synonym created.


報錯：
SQL> desc syn_tt;
ERROR:
ORA-04043: object "SYS"."T" does not exist



查詢無效物件：

SQL> set linesize 1000
SQL>  SELECT OBJECT_NAME, OBJECT_TYPE, OWNER, CREATED, LAST_DDL_TIME, TIMESTAMP FROM dba_objects WHERE status = 'INVALID';

OBJECT_NAME                                                     OBJECT_TYPE         OWNER     CREATED              LAST_DDL_TIME      
-------------------------------------------------------------- ------------------- ------------------------------ ------------------- ------------------- -------------------
/53ad31cb_TagsLocator                                            JAVA CLASS          EXFSYS    2014-10-22 14:10:56 2014-11-12 14:40:46 2014-10-22:14:10:56
/357abd97_XPathFilter                                            JAVA CLASS          EXFSYS    2014-10-22 14:10:56 2014-11-12 14:40:46 2014-10-22:14:10:56
SYN_T                                                            SYNONYM             SYS       2014-11-14 09:40:47 2014-11-14 09:40:47 2014-11-14:09:40:47

SQL>


處理辦法：
先採用方法一，再採用方法二。
方法一：
ALTER SYNONYM  SYN_T  compile;
SQL> ALTER SYNONYM  SYN_T  compile;

Synonym altered.

SQL> SELECT OBJECT_NAME, OBJECT_TYPE, OWNER, CREATED, LAST_DDL_TIME, TIMESTAMP FROM dba_objects WHERE status = 'INVALID';

OBJECT_NAME                                            OBJECT_TYPE         OWNER     CREATED              LAST_DDL_TIME       TIMESTAMP
----------------------------------------------- ------------------- ------------------------------ ------------------- ------------------- -------------------
/53ad31cb_TagsLocator                                    JAVA CLASS          EXFSYS    2014-10-22 14:10:56 2014-11-12 14:40:46 2014-10-22:14:10:56
/357abd97_XPathFilter                                    JAVA CLASS          EXFSYS    2014-10-22 14:10:56 2014-11-12 14:40:46 2014-10-22:14:10:56
SYN_T                                                      SYNONYM             SYS       2014-11-14 09:40:47 2014-11-14 09:40:47 2014-11-14:09:40:47

SQL> SELECT COUNT(*) FROM dba_objects WHERE status = 'INVALID' AND object_name NOT LIKE 'BIN$%' AND object_type NOT IN ('MATERIALIZED VIEW');

  COUNT(*)
----------
         3

不能清理。




方法二：直接刪除
查詢指令碼：
select 'drop '
       || decode (s.owner,
                  'PUBLIC', 'public synonym ',
                  'synonym ' || s.owner || '.')
       || s.synonym_name
       || ';' as "Dropping invalid synonyms:"
  from dba_synonyms s
 where table_owner not in ('SYSTEM', 'SYS') and db_link is null
   and not exists
          (select null
             from dba_objects o
            where s.table_owner = o.owner
              and s.table_name = o.object_name)
/


SQL> select 'drop '
  2         || decode (s.owner,
  3                    'PUBLIC', 'public synonym ',
  4                    'synonym ' || s.owner || '.')
  5         || s.synonym_name
  6         || ';' as "Dropping invalid synonyms:"
  7    from dba_synonyms s
  8   where table_owner not in ('SYSTEM') and db_link is null
  9     and not exists
 10            (select null
 11               from dba_objects o
 12              where s.table_owner = o.owner
 13                and s.table_name = o.object_name)
 14  /

Dropping invalid synonyms:
---------------------------------------------------------------------------
drop synonym SYS.SYN_T;
drop synonym SEC.SYN_PUBLIC_T;
drop synonym SEC.SYN_TT;




SQL> drop synonym SEC.SYN_TT;
SQL> drop synonym SEC.SYN_PUBLIC_T;
SQL> drop synonym SYS.SYN_T;