| ORA-00604 problem [message #242047] |
Thu, 31 May 2007 08:45  |
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 
|
|
|
|
| Re: ORA-00604 problem [message #242057 is a reply to message #242047] |
Thu, 31 May 2007 09:09  |
Frank
Messages: 7901
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 |
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 #242090 is a reply to message #242047] |
Thu, 31 May 2007 10:12 |
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 #242132 is a reply to message #242104] |
Thu, 31 May 2007 14:15 |
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 #242180 is a reply to message #242071] |
Thu, 31 May 2007 23:32 |
Frank
Messages: 7901
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 #242289 is a reply to message #242047] |
Fri, 01 June 2007 08:42 |
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 #242352 is a reply to message #242047] |
Fri, 01 June 2007 11:32 |
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 |
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 #242461 is a reply to message #242047] |
Sat, 02 June 2007 06:17 |
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 #242463 is a reply to message #242462] |
Sat, 02 June 2007 06:38 |
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 #242465 is a reply to message #242047] |
Sat, 02 June 2007 06:53 |
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 |
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 |
 |
Barbara Boehmer
Messages: 9100
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
