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

天萃荷净

运维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表的恢复过程