This content has been marked as final. Show 8 replies
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