sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

【案例】Oracle报错ORA-01775 数据库误删除dual表的恢复过程

时间:2016-07-29 22:32   来源:Oracle研究中心   作者:惜分飞   点击:

天萃荷净 运维DBA反映Oracle数据库报错ORA-01775: looping chain of synonyms,分析原因为误删除dual表导致,恢复过程详见文章内容

1.10G中删除dual表恢复

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> select object_type,owner from dba_objects where object_name='DUAL';

OBJECT_TYPE         OWNER
------------------- ------------------------------
TABLE               SYS
SYNONYM             PUBLIC

SQL> drop table sys.dual;

Table dropped.

SQL> select object_type from dba_objects where object_name='DUAL';

OBJECT_TYPE
-------------------
SYNONYM

SQL> SELECT SYSDATE FROM dual;
SELECT SYSDATE FROM dual
                    *
ERROR at line 1:
ORA-01775: looping chain of synonyms


SQL> CREATE TABLE XFF AS SELECT * from dba_objects;

Table created.

SQL> drop table xff purge;
drop table xff purge
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01775: looping chain of synonyms


设置10046跟踪会话发现,在每次删除表操作的时候发现如下错误
select dummy from dual where  ora_dict_obj_type = 'TABLE'

其实这里错误都很明显,是因为dual表不存在了,表对应的同义词还存在,当查询dual的时候,会去查询同义词,然后该同义词去找表,而表不存在,所以出现上述的ORA-01775: looping chain of synonyms错误

2.解决方法

SQL> CREATE TABLE "SYS"."DUAL" 
  2     (       "DUMMY" VARCHAR2(1)
  3     ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  4    STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  5    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  6    TABLESPACE "SYSTEM" ;

Table created.

SQL> GRANT SELECT ON "SYS"."DUAL" TO PUBLIC WITH GRANT OPTION;

Grant succeeded.

SQL> insert into dual values('X');

1 row created.

SQL> COMMIT;

Commit complete.

--编译对象
SQL> @?/rdbms/admin/utlrp.sql

3.测试结果

SQL> select sysdate from dual;

SYSDATE
------------
13-MAR-12

SQL> drop table xff purge;

Table dropped.

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle报错ORA-01775 数据库误删除dual表的恢复过程

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/913.html

Oracle研究中心

关键词:

Oracle报错ORA-01775

数据库误删除dual表的恢复过程