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;
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29500582/viewspace-1332715/，如需转载，请注明出处，否则将追究法律责任。
转播
