Saturday, March 5, 2011

How to recreate DUAL table in Oracle after deleting it ?



We should never drop a dual table, it will have serious impact on the database functionality .If we have dropped the dual table from sys user and startup the database next time it will be not open. To open the database we have follow the below steps .Therefore it is highly recommended not to delete the dual table .

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)
NAMESPACE                                            NUMBER
EDITION_NAME                                        VARCHAR2(30)

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 d:\oracle\diag\rdbms\god\god\trace\god_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
ORA-4063  :encountered when generating server alert SMG-3503 

SQL> shut immediate;                               
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 d:\oracle\diag\rdbms\god\god\trace\god_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...

Now we will create pfile  and add the below parameter and startup database.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='c:\initgod.ora';
 

Now database is open but it throws the error while creating the dummt table .

SQL>create table "SYS"."DUAL" ("DUMMY"  VARCHAR2(1)) ;
 CREATE TABLE "SYS"."DUAL" ( "DUMMY" VARCHAR2(1))
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01775: looping chain of synonyms

Again Check the Alertlog file


 Errors in file d:\oracle\diag\rdbms\god\god\trace\god_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='c:\initgod.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> create table  "SYS"."DUAL" ("DUMMY"    VARCHAR2(1)) ;
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 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                02-APR-10
PUBLIC              DUAL               SYNONYM          02-APR-10

Hence, Dual table is recreated.



Enjoy          J J J


3 comments:

Nandkishor Wagh said...

interesting piece of information, I had come to know about your web-page from my friend pramod, jaipur,i have read atleast eight posts of yours by now, and let me tell you, your blog gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new posts, once again hats off to you! Thanks a million once again, Regards, Synonyms In Oracle 11g

NEERAJ VISHEN said...

Thanks Alot NandKishor !!!!!!!!!!!


Have Good Time Ahead and Enjoy :)

Anil Kumar said...

Thanks Neeraj for sharing knowledge.

Hats off to you for your efforts.