(转)开发][Oracle]Ora-00980错误分析
来源:互联网 发布:windows.h头文件 编辑:程序博客网 时间:2024/05/02 02:00
实验来给大家展示一种常见的导致“ORA-00980”错误发生的情况,然后给出一些其他可能导致这个错误发生的原因,最后给出一个可以自动化获得删除所有无效同名的SQL脚本。
一、先来模拟一下这个“ORA-00980: synonym translation is no longer valid”错误。
1.在secooler用户中创建一张表t
secooler@ora10g> create table t (x number);
Table created.
secooler@ora10g> insert into t values (1);
1 row created.
secooler@ora10g> commit;
Commit complete.
2.切换到sec用户,创建同名syn_t引用secooler用户下的t表
secooler@ora10g> conn sec/sec
Connected.
sec@ora10g> create synonym syn_t for secooler.t;
Synonym created.
sec@ora10g> select * from syn_t;
X
----------
1
3.回到secooler用户中将表t删除掉
sec@ora10g> conn secooler/secooler
Connected.
secooler@ora10g> drop table t purge;
Table dropped.
4.此时,我们再次使用同名syn_t查看表t时就会见到这个“ORA-00980”错误了
secooler@ora10g> conn sec/sec
Connected.
sec@ora10g> select * From syn_t;
select * From syn_t
*
ERROR at line 1:
ORA-00980: synonym translation is no longer valid
二、导致“ORA-00980”错误的原因小结
1.删除了数据库对象,但是忘记删除同名;
2.删除一个用户,但是忘记删除与此用户中相关的同名;
3.创建同名时,同名对应的数据库对象已经被删除掉了(这种情况很有意思)。
针对第三点,进一步演示一下,前面我们已经删除了secooler用户下t表的情况。此时我们尝试使用secooler用户下被删除的t表再创建两个同名,有意思的是虽然表已经不存在了,但同名还是可以创建成功的。需要清醒的认识到此时被创建出来的同名是无效的,从后面的脚本执行结果中可以更清晰的得出这个结论。
sec@ora10g> create synonym syn_public_t for secooler.t;
Synonym created.
sec@ora10g> create synonym syn_tt for secooler.t;
Synonym created.
三、隆重推出自动化生成删除无效同名的脚本
1.脚本如下
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)
/
2.针对上面的场景,真实演示一下
sys@ora10g> 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', 'SYS') 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 SEC.SYN_TT;
drop synonym SEC.SYN_PUBLIC_T;
drop synonym SEC.SYN_T;
3.使用脚本生成的SQL脚本即可轻松完成无效同名的清理任务。
sys@ora10g> drop synonym SEC.SYN_TT;
Synonym dropped.
sys@ora10g> drop synonym SEC.SYN_PUBLIC_T;
Synonym dropped.
sys@ora10g> drop synonym SEC.SYN_T;
Synonym dropped.
四、小结
在DBA的日常维护中需要不断的思考一个问题:如何才能更加高效的应对故障的发生?
带着这个问题,它就会不断的指引我们向自动化的道路迈进。
编写一些顺手易用的脚本是一名优秀DBA必须具有的基本技能之一。
希望这个小文儿能给大家带来一点点思考。
Good luck.
-- The End --
ORA-00980 synonym translation is no longer valid
Cause: The synonym used is based on a table, view, or synonym that no longer exists.
Action: Replace the synonym with the name of the object it references or re-create the synonym so that it refers to a valid table, view, or synonym.
一、先来模拟一下这个“ORA-00980: synonym translation is no longer valid”错误。
1.在secooler用户中创建一张表t
secooler@ora10g> create table t (x number);
Table created.
secooler@ora10g> insert into t values (1);
1 row created.
secooler@ora10g> commit;
Commit complete.
2.切换到sec用户,创建同名syn_t引用secooler用户下的t表
secooler@ora10g> conn sec/sec
Connected.
sec@ora10g> create synonym syn_t for secooler.t;
Synonym created.
sec@ora10g> select * from syn_t;
X
----------
1
3.回到secooler用户中将表t删除掉
sec@ora10g> conn secooler/secooler
Connected.
secooler@ora10g> drop table t purge;
Table dropped.
4.此时,我们再次使用同名syn_t查看表t时就会见到这个“ORA-00980”错误了
secooler@ora10g> conn sec/sec
Connected.
sec@ora10g> select * From syn_t;
select * From syn_t
*
ERROR at line 1:
ORA-00980: synonym translation is no longer valid
二、导致“ORA-00980”错误的原因小结
1.删除了数据库对象,但是忘记删除同名;
2.删除一个用户,但是忘记删除与此用户中相关的同名;
3.创建同名时,同名对应的数据库对象已经被删除掉了(这种情况很有意思)。
针对第三点,进一步演示一下,前面我们已经删除了secooler用户下t表的情况。此时我们尝试使用secooler用户下被删除的t表再创建两个同名,有意思的是虽然表已经不存在了,但同名还是可以创建成功的。需要清醒的认识到此时被创建出来的同名是无效的,从后面的脚本执行结果中可以更清晰的得出这个结论。
sec@ora10g> create synonym syn_public_t for secooler.t;
Synonym created.
sec@ora10g> create synonym syn_tt for secooler.t;
Synonym created.
三、隆重推出自动化生成删除无效同名的脚本
1.脚本如下
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)
/
2.针对上面的场景,真实演示一下
sys@ora10g> 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', 'SYS') 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 SEC.SYN_TT;
drop synonym SEC.SYN_PUBLIC_T;
drop synonym SEC.SYN_T;
3.使用脚本生成的SQL脚本即可轻松完成无效同名的清理任务。
sys@ora10g> drop synonym SEC.SYN_TT;
Synonym dropped.
sys@ora10g> drop synonym SEC.SYN_PUBLIC_T;
Synonym dropped.
sys@ora10g> drop synonym SEC.SYN_T;
Synonym dropped.
四、小结
在DBA的日常维护中需要不断的思考一个问题:如何才能更加高效的应对故障的发生?
带着这个问题,它就会不断的指引我们向自动化的道路迈进。
编写一些顺手易用的脚本是一名优秀DBA必须具有的基本技能之一。
希望这个小文儿能给大家带来一点点思考。
Good luck.
-- The End --
ORA-00980 synonym translation is no longer valid
Cause: The synonym used is based on a table, view, or synonym that no longer exists.
Action: Replace the synonym with the name of the object it references or re-create the synonym so that it refers to a valid table, view, or synonym.
关于DUAL的“ORA-00980”错误
今天有人发现本地的测试数据库有问题。
SQL> select sysdate from dual;
select sysdate from dual
ORA-00980: synonym translation is no longer valid
这种问题也比较常见,一般都是在sys下面重建就可以。
但是仔细一看, 原来是有人在自己的用户下面,建了同名的dual。
删了,一切正常。 估计是有人在上面做什么测试。
SQL> drop synonym dual;
Synonym dropped
SQL> desc dual;
Name Type Nullable Default Comments
----- ----------- -------- ------- --------
DUMMY VARCHAR2(1) Y
SQL> select * from dual;
DUMMY
-----
X
X
ORA-00980 synonym translation is no longer valid 特殊情况
我在使用DBlink的时候发生ORA-00980 synonym translation is no longer valid错误。我很疑惑,如果了解请告诉我,谢谢!
情况:
1)在PL/SQL 中写SQL语句insert into table (.....) select .... from view@dblink没有问题,能够正常执行,并且数据也可以插入。
2)在Procedure里把上述SQL写入后,编译时报错,ORA-00980 synonym translation is no longer valid。
3)我在本地数据库建立一个view, create or replace view XXX as
select .....from from view@dblink,成功。
4)在procedure里的SQL修改为如下insert into table (.....) select .... from XXX,能够成功编译。
为什么?难道在procedure里不能用dblink来读取view吗?
权限不足,在过程里使用可能需要显性授权.
一个Oracle难题的解决
问题描述:
1、运行一个java程序不断报错: ... Details : java.sql.SQLException: ORA-00980: synonym translation is no longer valid
2、用system用户联入数据库,运行命令报错:
SQL> desc dba_indexes; SP2-0749: Cannot resolve circular path of synonym "dba_indexes"
3、使用dba身份登陆,conn / as sysdba , 可以查询dba_indexes了,因此实际问题原因找到,用户system失去了访问data dictionary的权限
问题解决:
在一个同事的帮助下,重建所有data dictionary解决了这个问题:
conn / as sysdba
@$ORACLE_HOME/rdbms/admin/catalog.sql
@$ORACLE_HOME/rdbms/admin/catproc.sql
这两个脚本就是重建data dictionary的脚本,之后再用system登陆以后,可以访问所有的data dictionary了!java程序也不再报错!
ORA-00980 synonym translation is no longer valid 特殊情况
我在使用DBlink的时候发生ORA-00980 synonym translation is no longer valid错误。我很疑惑,如果了解请告诉我,谢谢!
情况:
1)在PL/SQL 中写SQL语句insert into table (.....) select .... from view@dblink没有问题,能够正常执行,并且数据也可以插入。
2)在Procedure里把上述SQL写入后,编译时报错,ORA-00980 synonym translation is no longer valid。
3)我在本地数据库建立一个view, create or replace view XXX as
select .....from from view@dblink,成功。
4)在procedure里的SQL修改为如下insert into table (.....) select .... from XXX,能够成功编译。
为什么?难道在procedure里不能用dblink来读取view吗?
权限不足,在过程里使用可能需要显性授权.
一个Oracle难题的解决
问题描述:
1、运行一个java程序不断报错: ... Details : java.sql.SQLException: ORA-00980: synonym translation is no longer valid
2、用system用户联入数据库,运行命令报错:
SQL> desc dba_indexes; SP2-0749: Cannot resolve circular path of synonym "dba_indexes"
3、使用dba身份登陆,conn / as sysdba , 可以查询dba_indexes了,因此实际问题原因找到,用户system失去了访问data dictionary的权限
问题解决:
在一个同事的帮助下,重建所有data dictionary解决了这个问题:
conn / as sysdba
@$ORACLE_HOME/rdbms/admin/catalog.sql
@$ORACLE_HOME/rdbms/admin/catproc.sql
这两个脚本就是重建data dictionary的脚本,之后再用system登陆以后,可以访问所有的data dictionary了!java程序也不再报错!
- (转)开发][Oracle]Ora-00980错误分析
- oracle数据库:ORA-04091、ORA-06512、ORA-04088错误分析
- Oracle ORA-00903错误具体原因分析
- [Oracle] ORA-03113错误分析与解决
- Oracle ORA-07445 [evaopn3()+384] 错误 分析
- Oracle ORA-07445 [0000000000000000] 错误分析
- 【Oracle】ORA-03113错误分析与解决
- 开发随笔-oracle ora-01092 ora-18008 的错误
- Oracle查询错误分析:ORA-01791:不是SELECTed表达式
- [Oracle数据库] 深入分析ora-600 2662错误系列一
- ORA-00265:修改oracle归档模式错误分析
- ORA-00265:修改oracle归档模式错误分析
- Oracle错误:ORA-01033
- oracle ora - 日期错误
- oracle ORA-03113 错误
- Oracle ORA-22922 错误
- Oracle: ORA-错误
- oracle ORA 1555错误
- Java初记
- 一派胡言
- 新的挑战
- 读取数组中某一个数据
- itcast.com传智播客java
- (转)开发][Oracle]Ora-00980错误分析
- 嵌入式工程师是怎样炼成的(五)---- 踏实工作
- k,
- Java线程总结
- Thread类
- spring,MVC
- 2010年3月18日星期四
- wndclass
- 短信应用系统