How to recreate DUAL table in Oracle after deleting it.

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............:)