Incase you have dropped the dual table from sys user then there are few steps involved in recreating the DUAL object.
SQL> conn /as sysdba
SQL> drop table dual;
Table dropped.
SQL> desc dba_objects
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL> select sysdate from dual;
select sysdate from dual
*
ERROR at line 1:
ORA-01775: looping chain of synonyms
Check the alertlog file
Errors in file /oraeng/app/oracle/admin/mydb2/bdump/mydb2_j000_26455.trc:
ORA-12012: error on auto execute of job 21
ORA-01775: looping chain of synonyms
Fri May 28 00:39:03 2010
Errors in file /oraeng/app/oracle/admin/mydb2/bdump/mydb2_j000_26455.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01775: looping chain of synonyms
ORA-12012: error on auto execute of job 21
ORA-01775: looping chain of synonyms
conn u1/u1
SQL>purge recyclebin;
purge recyclebin
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-01775: looping chain of synonyms
Check the Alert logfile
Errors in file /oraeng/app/oracle/admin/mydb2/bdump/mydb2_j000_26505.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01775: looping chain of synonyms
ORA-12012: error on auto execute of job 21
ORA-01775: looping chain of synonyms
Fri May 28 00:40:26 2010
ORA-4063 encountered when generating server alert SMG-3503
SQL> shut immediate; went well
SQL> startup;
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1266392 bytes
Variable Size 113249576 bytes
Database Buffers 50331648 bytes
Redo Buffers 2924544 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
Checking the alert logfile
Errors in file /oraeng/app/oracle/admin/mydb2/udump/mydb2_ora_26538.trc:
ORA-01775: looping chain of synonyms
Error 1775 happened during db open, shutting down database
USER: terminating instance due to error 1775
Instance terminated by USER, pid = 26538
ORA-1092 signalled during: ALTER DATABASE OPEN...
Create a pfile from spfile and add the below line in the pfile
replication_dependency_tracking= false
Now start the database using pfile;
SQL> startup pfile='/oraeng/app/oracle/product/dbs/initmydb2.ora';
---went fine but create statement failed......
SQL> CREATE TABLE "SYS"."DUAL" ( "DUMMY" VARCHAR2(1)) PCTFREE 10 PCTUSED 4;
CREATE TABLE "SYS"."DUAL" ( "DUMMY" VARCHAR2(1)) PCTFREE 10 PCTUSED 4
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01775: looping chain of synonyms
Alertlog file
Fri May 28 00:43:23 2010
Errors in file /oraeng/app/oracle/admin/mydb2/bdump/mydb2_j000_26583.trc:
ORA-12012: error on auto execute of job 21
ORA-01775: looping chain of synonyms
SQL> shut immediate
Now open the database using upgrade mode
SQL> startup upgrade pfile='/oraeng/app/oracle/product/10.2.0/dbs/initmydb2.ora';
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1266392 bytes
Variable Size 113249576 bytes
Database Buffers 50331648 bytes
Redo Buffers 2924544 bytes
Database mounted.
Database opened.
SQL>
SQL> CREATE TABLE "SYS"."DUAL" ( "DUMMY" VARCHAR2(1)) PCTFREE 10 PCTUSED 4;
Table created.
SQL> Insert Into Dual Values ('X');
1 row created.
SQL> commit;
Commit complete.
SQL> Grant Select On Dual To Public;
Grant succeeded.
SQL> column OWNER format a15;
SQL> column OBJECT_NAME format a20;
SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE,CREATED from dba_objects where object_name='DUAL';
OWNER OBJECT_NAME OBJECT_TYPE CREATED
--------------- -------------------- ------------------- ---------
SYS DUAL TABLE 28-MAY-10
PUBLIC DUAL SYNONYM 30-JUN-05
SQL>
Now dual table is recreated.
Quite interesting............:)
