Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00604: error occurred at recursive SQL level 1 (11g r2, Server 2003)
ORA-00604: error occurred at recursive SQL level 1 [message #417894] Wed, 12 August 2009 01:05 Go to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hi all,

I am facing problem as described below.
Please help me in solving that.

I was having THRD_PARTY_SITES table in my schema.
I DROP that table with CASCADE CONSTRAINTS option.
After it, I am creating that table again with some modification and getting following problem.

SQL> drop table thrd_party_sites cascade constraints;

Table dropped.

SQL> purge recyclebin;

Recyclebin purged.

XLNTSEO@gold12>CREATE TABLE THRD_PARTY_SITES
  2  (
  3    WEBSITE_PK NUMBER(38, 0) NOT NULL,
  4    OWNER_FK NUMBER(38) NOT NULL,
  5    WEBMASTER_FK NUMBER(38, 0) NOT NULL,
  6    DOMAIN_FK NUMBER(38) NOT NULL,
  7    REMARKS VARCHAR2(1000),
  8    LAST_LOGIN_FK NUMBER NOT NULL,
  9    LAST_MODIFIED TIMESTAMP(6) NOT NULL,
 10    LAST_MC VARCHAR2(50) NOT NULL,
 11    LAST_IP VARCHAR2(20) NOT NULL
 12  , CONSTRAINT THRD_PARTY_SITES_PK PRIMARY KEY
 13    (
 14      WEBSITE_PK
 15    )
 16    ENABLE
 17  )
 18  ;
CREATE TABLE THRD_PARTY_SITES
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00001: unique constraint (SYS.I_OBJ1) violated


I have searched on Google and this forum as well, but unable to solve this problem.
Thanks in advance.

regards,
Delna
Re: ORA-00604: error occurred at recursive SQL level 1 [message #417903 is a reply to message #417894] Wed, 12 August 2009 01:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
11gR2! Where did you get it?

Shutdown, restart the database and retry.

Regards
Michel
Re: ORA-00604: error occurred at recursive SQL level 1 [message #417907 is a reply to message #417894] Wed, 12 August 2009 01:39 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Sorry sir for R2

XLNTSEO@gold12>select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 64-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production


I have already restarted the database.
Still getting same error.

regards,
Delna
Re: ORA-00604: error occurred at recursive SQL level 1 [message #417911 is a reply to message #417894] Wed, 12 August 2009 01:44 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Moreover, as shown in error message,

ORA-00001: unique constraint (SYS.I_OBJ1) violated


I have searched for I_OBJ1 constraint but it does not exist.

SYS@gold12>select * from all_constraints
  2  where constraint_name like 'I_OBJ%';

no rows selected


regards,
Delna
Re: ORA-00604: error occurred at recursive SQL level 1 [message #417913 is a reply to message #417911] Wed, 12 August 2009 01:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
It is a unique index on sys.obj$.

Try what I mentionned.
You have a corruption either in dictionary or in row cache, so try it.

Regards
Michel
Re: ORA-00604: error occurred at recursive SQL level 1 [message #417918 is a reply to message #417913] Wed, 12 August 2009 02:01 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Thank you Michel sir for suggestion, but problem continues.

SYS@gold12>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.


XLNTSEO@gold12>CREATE TABLE THRD_PARTY_SITES
  2  (
  3    WEBSITE_PK NUMBER(38, 0) NOT NULL,
  4    OWNER_FK NUMBER(38) NOT NULL,
  5    WEBMASTER_FK NUMBER(38, 0) NOT NULL,
  6    DOMAIN_FK NUMBER(38) NOT NULL,
  7    REMARKS VARCHAR2(1000),
  8    LAST_LOGIN_FK NUMBER NOT NULL,
  9    LAST_MODIFIED TIMESTAMP(6) NOT NULL,
 10    LAST_MC VARCHAR2(50) NOT NULL,
 11    LAST_IP VARCHAR2(20) NOT NULL
 12  , CONSTRAINT THRD_PARTY_SITES_PK PRIMARY KEY
 13    (
 14      WEBSITE_PK
 15    )
 16    ENABLE
 17  )
 18  ;
CREATE TABLE THRD_PARTY_SITES
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel


XLNTSEO@gold12>connect xlntseo@gold12
Enter password: *******
Connected.
XLNTSEO@gold12>CREATE TABLE THRD_PARTY_SITES
  2  (
  3    WEBSITE_PK NUMBER(38, 0) NOT NULL,
  4    OWNER_FK NUMBER(38) NOT NULL,
  5    WEBMASTER_FK NUMBER(38, 0) NOT NULL,
  6    DOMAIN_FK NUMBER(38) NOT NULL,
  7    REMARKS VARCHAR2(1000),
  8    LAST_LOGIN_FK NUMBER NOT NULL,
  9    LAST_MODIFIED TIMESTAMP(6) NOT NULL,
 10    LAST_MC VARCHAR2(50) NOT NULL,
 11    LAST_IP VARCHAR2(20) NOT NULL
 12  , CONSTRAINT THRD_PARTY_SITES_PK PRIMARY KEY
 13    (
 14      WEBSITE_PK
 15    )
 16    ENABLE
 17  )
 18  ;
CREATE TABLE THRD_PARTY_SITES
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00001: unique constraint (SYS.I_OBJ1) violated


regards,
Delna
Re: ORA-00604: error occurred at recursive SQL level 1 [message #417920 is a reply to message #417918] Wed, 12 August 2009 02:04 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Sounds to me like it's time for Metalink. Search, and if you don't find anything, log a TAR.

[Updated on: Wed, 12 August 2009 02:04]

Report message to a moderator

Re: ORA-00604: error occurred at recursive SQL level 1 [message #417921 is a reply to message #417918] Wed, 12 August 2009 02:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
So the corruption is in dictionary.
You have to contact Oracle support that will help you to fix it.

Regards
Michel
Re: ORA-00604: error occurred at recursive SQL level 1 [message #417943 is a reply to message #417894] Wed, 12 August 2009 04:05 Go to previous message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Thank you all.

regards,
Delna
Previous Topic: BLOB to TIF
Next Topic: Locking complete table
Goto Forum:
  


Current Time: Tue Feb 11 01:39:50 CST 2025