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