This content has been marked as final. Show 8 replies
Apologies if I went wrong in posting thread format.
Try to do these first against the tables:
truncate table b;
truncate table a;
Edited by: HuaMin Chen on Nov 19, 2012 12:00 AM
I tried truncating tables also. But truncating table will keep table structure intact. Even after truncating table dropping them fails.
I am new to DBA practices and recently I was performing some operations on my test schema. While dropping unwanted tables I faced following error.
I tried to drop them by sys user but still got the same message.
SQL> drop table b;
drop table b
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01422: exact fetch returns more than requested number of rows
You could have system triggers or auditing in effect that are causing that error. A common cause of that error is a select INTO that is returning too many rows. Since a DROP, in and of itself, does not do a SELECT there could be a SELECT that is part of your auditing or system triggers that is raising an exception.
You need to check whether auditing is enabled for that table or globally and disable any auditing or system triggers that reference the table(s) you are trying to drop.
Two recommendations :
1. Make the parameter : systemtrig_enabled=FALSE* resolves the problem.
2. Follow the steps :
a. select * from dual; (You will get one row)
b. create table my_dual as select * from dual; (table will be created)
c. select * from my_dual; (It will show two rows)
d. delete from dual where dummy = 'X' and rownum=1;
e. Now TRY TO DROP THE TABLE
Hey, LaserSoft, good advice. I see you have access to MOS and have read note ID 453528.1.