Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00604 problem
ORA-00604 problem [message #242047] Thu, 31 May 2007 08:45 Go to next message
gunsbong
Messages: 44
Registered: May 2007
Member
help guys...

we have a backup server for 9i version 9.2.0.1.0

recently i had encountered this error messages when dropping a table

SQL> drop table corttex.vatrate cascade constraint;
drop table corttex.vatrate cascade constraint
*
ERROR at line 1:
ORA-00604: error occured ar recursive SQL level 1
ORA-00942: table or view does not exist


when i create a table, its creating but when i drop it immediately same message appears.when you select the tables on dba_tables..they are all there..but i could not drop any of them

sdo_geom_metadata_table is there...i tried running catmd.sql which was a advice from a guro here...but unfortunately same error occurs when it encounters tables...

i am logged in as sysdba...so previledge issue is not applicable...

pls. help... its my 3rd time already in recreating the whole database and tables because imports are not working...

tanx Embarassed

Re: ORA-00604 problem [message #242057 is a reply to message #242047] Thu, 31 May 2007 09:09 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What happens if you login as corttex and drop it?
What (exact!) statement did you use to create the table?
What can you see in dba_objects?
Re: ORA-00604 problem [message #242071 is a reply to message #242057] Thu, 31 May 2007 09:33 Go to previous messageGo to next message
gunsbong
Messages: 44
Registered: May 2007
Member
a)What happens if you login as corttex and drop it?
b)What (exact!) statement did you use to create the table?
c)What can you see in dba_objects?

a)* same error message ---
b)* correction--tablespaces not tables... im just importing the tables from the daily zip archive files of our live db...
c)* what do you mean about this?all file from corttex is not missing there(dba_objects)
Re: ORA-00604 problem [message #242077 is a reply to message #242047] Thu, 31 May 2007 09:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You likely have a wrong trigger on DDL (maybe it tries to log the DDL).
Check it in DBA_CONSTRAINTS.

Regards
Michel
Re: ORA-00604 problem [message #242090 is a reply to message #242047] Thu, 31 May 2007 10:12 Go to previous messageGo to next message
gunsbong
Messages: 44
Registered: May 2007
Member
You likely have a wrong trigger on DDL (maybe it tries to log the DDL).
Check it in DBA_CONSTRAINTS.

Regards
Michel


i dont really got your point sir....

pls.elaborate more clearly...im a newbies

tanx
Re: ORA-00604 problem [message #242104 is a reply to message #242090] Thu, 31 May 2007 11:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry I meant DBA_TRIGGERS.
Something like:
SQL> desc dba_triggers
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 OWNER                                     VARCHAR2(30)
 TRIGGER_NAME                              VARCHAR2(30)
 TRIGGER_TYPE                              VARCHAR2(16 CHAR)
 TRIGGERING_EVENT                          VARCHAR2(227 CHAR)
 TABLE_OWNER                               VARCHAR2(30)
 BASE_OBJECT_TYPE                          VARCHAR2(16 CHAR)
 TABLE_NAME                                VARCHAR2(30)
 COLUMN_NAME                               VARCHAR2(4000)
 REFERENCING_NAMES                         VARCHAR2(128)
 WHEN_CLAUSE                               VARCHAR2(4000)
 STATUS                                    VARCHAR2(8 CHAR)
 DESCRIPTION                               VARCHAR2(4000)
 ACTION_TYPE                               VARCHAR2(11 CHAR)
 TRIGGER_BODY                              LONG

SQL> select owner,trigger_name,trigger_type,triggering_event
  2  from dba_triggers
  3  where trigger_type like '%EVENT'
  4    and triggering_event like '%DROP%'
  5  order by 1,2
  6  /
OWNER                          TRIGGER_NAME                   TRIGGER_TYPE     TRIGGERING_EVENT
------------------------------ ------------------------------ ---------------- --------------------
EXFSYS                         EXPFIL_DROPOBJ_MAINT           BEFORE EVENT     DROP
EXFSYS                         EXPFIL_DROPUSR_MAINT           AFTER EVENT      DROP
LBACSYS                        LBAC$AFTER_DROP                AFTER EVENT      DROP
MDSYS                          SDO_DROP_USER                  AFTER EVENT      DROP
MDSYS                          SDO_DROP_USER_BEFORE           BEFORE EVENT     DROP
MDSYS                          SDO_TOPO_DROP_FTBL             BEFORE EVENT     DROP
SYS                            AW_DROP_TRG                    AFTER EVENT      DROP
SYS                            CDC_DROP_CTABLE_BEFORE         BEFORE EVENT     DROP
SYS                            NO_VM_DROP                     BEFORE EVENT     DROP
SYS                            NO_VM_DROP_A                   AFTER EVENT      DROP
XDB                            XDB_PI_TRIG                    BEFORE EVENT     DROP OR TRUNCATE

One of the triggers returned by this query or the like is wrong.

Regards
Michel
Re: ORA-00604 problem [message #242132 is a reply to message #242104] Thu, 31 May 2007 14:15 Go to previous messageGo to next message
gunsbong
Messages: 44
Registered: May 2007
Member

SQL> select owner,trigger_name,trigger_type,triggering_event
2 from dba_triggers
3 where trigger_type like '%EVENT'
4 and triggering_event like '%DROP%'
5 order by 1,2;

OWNER TRIGGER_NAME TRIGGER_TYPE-------- ------------------------------ ----------------
TRIGGERING_EVENT
--------------------------------------------------------------------------------

MDSYS SDO_DROP_USER AFTER EVENT
DROP

SYS CDC_DROP_CTABLE_BEFORE BEFORE EVENT
DROP

SYS NO_VM_DROP BEFORE EVENT
DROP

XDB XDB_PI_TRIG BEFORE EVENT
DROP OR TRUNCATE


4 rows selected.

this is the result...now what is the problem with this sir?
Re: ORA-00604 problem [message #242137 is a reply to message #242132] Thu, 31 May 2007 14:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Same thing replacing DROP with DDL.
We are first searching a non system trigger, not owned by SYS, MDSYS, XDB...
Search in DBA_OBJECTS triggers with status INVALID.

I don't have your database by hand it is difficult to find like this. Don't you have a DBA at your site?

Regards
Michel
Re: ORA-00604 problem [message #242159 is a reply to message #242047] Thu, 31 May 2007 16:16 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
A shot in the dark, try putting double quotes around the table name when you try to delete it.
Re: ORA-00604 problem [message #242180 is a reply to message #242071] Thu, 31 May 2007 23:32 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
gunsbong wrote on Thu, 31 May 2007 16:33
a)What happens if you login as corttex and drop it?
b)What (exact!) statement did you use to create the table?
c)What can you see in dba_objects?

a)* same error message ---
b)* correction--tablespaces not tables... im just importing the tables from the daily zip archive files of our live db...
c)* what do you mean about this?all file from corttex is not missing there(dba_objects)

Ad b):

gunsbong wrote in his initial post
when i create a table, its creating but when i drop it immediately same message appears

Show us a copy-paste of that, the succesful creation of a table, followed by the drop-attempt.

Ad c)
I think pablolee may have a good point here, that is why I asked about the contents of dba_objects.
Do a query on dba_objects for object_name vatrate:
select owner, object_type, object_name from dba_objects where upper(object_name) = 'VATRATE';

[Updated on: Thu, 31 May 2007 23:32]

Report message to a moderator

Re: ORA-00604 problem [message #242190 is a reply to message #242180] Thu, 31 May 2007 23:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't think this a name problem as the most important part here is:
Quote:
ORA-00604: error occured ar recursive SQL level 1

If this was just a non existent table, you just get:
SQL> drop table non_existent;
drop table non_existent
           *
ERROR at line 1:
ORA-00942: table or view does not exist

without the recursive part.
This part leadds to 2 ways:
- an invalid trigger
- a dictionary corruption (someone delete a SYS table, for instance aud$ when audit on drop in on...)

Regards
Michel
Re: ORA-00604 problem [message #242289 is a reply to message #242047] Fri, 01 June 2007 08:42 Go to previous messageGo to next message
gunsbong
Messages: 44
Registered: May 2007
Member

sir frank,

this is the sample table i created...

SQL> create table test(test varchar2(20));

Table created.

SQL> drop table test cascade constraint;
drop table test cascade constraint
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist

Re: ORA-00604 problem [message #242293 is a reply to message #242289] Fri, 01 June 2007 08:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You didn't answer my last question.

Regards
Michel
Re: ORA-00604 problem [message #242342 is a reply to message #242293] Fri, 01 June 2007 11:07 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You're right Michel. Definitely a trigger or corruption..
Re: ORA-00604 problem [message #242352 is a reply to message #242047] Fri, 01 June 2007 11:32 Go to previous messageGo to next message
gunsbong
Messages: 44
Registered: May 2007
Member

SQL> select owner, object_type, object_name from dba_objects where upper(object_
name) = 'VATRATE';

OWNER OBJECT_TYPE
------------------------------ ------------------
OBJECT_NAME
--------------------------------------------------------------------------------

CORTTEX TABLE
VATRATE


1 row selected.
Re: ORA-00604 problem [message #242353 is a reply to message #242047] Fri, 01 June 2007 11:40 Go to previous messageGo to next message
gunsbong
Messages: 44
Registered: May 2007
Member

SQL> select owner,object_name from dba_objects
2 where status='INVALID' and
3 owner not in ('SYS','MDSYS');

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------

CTXSYS
DRIACC


1 row selected.
Re: ORA-00604 problem [message #242358 is a reply to message #242353] Fri, 01 June 2007 12:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post result of:
select owner,trigger_name,trigger_type,triggering_event
from dba_triggers
where trigger_type like '%EVENT'
  and triggering_event like '%DDL%'
order by 1,2
/

select trigger_type,triggering_event
from dba_triggers
where owner='CTXSYS'
  and trigger_name='DRIACC'
/

Regards
Michel
Re: ORA-00604 problem [message #242461 is a reply to message #242047] Sat, 02 June 2007 06:17 Go to previous messageGo to next message
gunsbong
Messages: 44
Registered: May 2007
Member
actualy,during importing the dump files...i have noticed that the importing dont continue due maybe of not recognizing the tables along the way...but this rarely happened...before,importting was smooth sailing that is until a few days ago...

to my shock...when i started dropping tables for another session of importing this error came out...

i have tried everything from my capacity but i failed pinpointing the exact problem...

I know that:
1-previledges is not an issue coz im using sysdba when dropping tables
2-the table is visible,but i cant drop it...i even created test table and then drop it as shown, but failed.
3-sdo_geom_metadata_table is visible and i have also tried to use catmd.sql to anyhow fix it, but it failed coz as i mentioned this error showed

today is my 4th time recreating everything from the start for another importing session...


this is my sample import script...

d:\imp system/standby fromuser=username1 touser=username2 file=file.dmp log=file.ilog

after this session i will really expect the same error like my previous attemps...

any suggestions pls.?
Re: ORA-00604 problem [message #242462 is a reply to message #242461] Sat, 02 June 2007 06:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Answer to my 2 previous queries.

Regards
Michel
Re: ORA-00604 problem [message #242463 is a reply to message #242462] Sat, 02 June 2007 06:38 Go to previous messageGo to next message
gunsbong
Messages: 44
Registered: May 2007
Member
i am trully sory sir michel but i had already recreated everything from the start...but if you insist,i would give you the answer once i import and be stuck to the same error again...
Re: ORA-00604 problem [message #242464 is a reply to message #242463] Sat, 02 June 2007 06:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
if you insist,i would give you the answer

I don't have any problem.
You have one.
I try to help you.
If you don't want I don't mind.
Do as you feel it.

Regards
Michel
Re: ORA-00604 problem [message #242465 is a reply to message #242047] Sat, 02 June 2007 06:53 Go to previous messageGo to next message
gunsbong
Messages: 44
Registered: May 2007
Member
sir,

i am sorry if my reply touched your ego...

i really dont mean any harm...

i am just exhausted from having this problem...

just wait for a while...im proccesing it...

tanx again...
Re: ORA-00604 problem [message #243855 is a reply to message #242047] Sun, 10 June 2007 01:14 Go to previous messageGo to next message
gunsbong
Messages: 44
Registered: May 2007
Member

drop table VTABLE$ cascade constraint
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist


drop table _default_auditing_options_ cascade constraint
*
ERROR at line 1:
ORA-00911: invalid character


SQL> select owner,trigger_name,trigger_type,triggering_event
2 from dba_triggers
3 where trigger_type like '%EVENT'
4 and triggering_event like '%DDL%'
5 order by 1,2;

no rows selected

SQL> select trigger_type,triggering_event
2 from dba_triggers
3 where owner='CTXSYS'
4 and trigger_name='DRIACC';

no rows selected

SQL>


sorry...ive been away for sometime... i had an personal emergency these past days...
Re: ORA-00604 problem [message #243878 is a reply to message #243855] Sun, 10 June 2007 15:31 Go to previous message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
As the others have explained, the most likely cause of your error is a database trigger that fires when you try to drop. It appears that some code within that trigger is trying to do something with a table that does not exist and that results in the combination of errors that you originally received. The solution is to find that trigger and fix the code. If you click on the link below, Tom Kyte gives a nice demonstration of how this happens and one way of fixing it. Although that example involves dropping a user, the same thing applies to dropping a table.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:13273199418308

In your situation, the trigger causing the problem could be one that fires either when you try to drop or when you try to do any type of ddl, including dropping. It appears that you do not have any general ddl triggers, but you do have the following triggers that fire when you try to drop, and you need to check the code within those triggers:

SYS.CDC_DROP_CTABLE_BEFORE

SYS.NO_VM_DROP

MDSYS.SDO_DROP_USER

XDB.XDB_PI_TRIG

Although you can drop the problem trigger or disable the problem trigger, this may cause other problems, so it is best to fix the code. I don't know what kind of system you are working on or what other staff is available or what privileges you have. If there is a DBA, you need to consult that person prior to dropping, disabling, or modifying a trigger. If this is just a test system that you can easily wipe out and replace as it sounds like you are doing, then try creating a test table as you did before, then disble the suspect triggers, then try dropping. If you are then able to drop, then that at least confirms where the problem is, and you can enable the trigger after you fix it. If you have trouble figuring out the problem with the trigger code, you can post it here.

[Updated on: Sun, 10 June 2007 15:49]

Report message to a moderator

Previous Topic: formating numbers
Next Topic: Materialised Views
Goto Forum:
  


Current Time: Fri Dec 09 23:41:03 CST 2016

Total time taken to generate the page: 0.12185 seconds