When I'm trying to drop table then I'm getting error
SQL Error: ORA-00604: error occurred at recursive SQL level 2 ORA-01422: exact fetch returns more than requested number of rows 00604. 00000 - "error occurred at recursive SQL level %s" *Cause: An error occurred while processing a recursive SQL statement (a statement applying to internal dictionary tables). *Action: If the situation described in the next error on the stack can be corrected, do so; otherwise contact Oracle Support.
This question is tagged with
~ Asked on 2015-05-27 09:15:18
I noticed following line from error.
exact fetch returns more than requested number of rows
That means Oracle was expecting one row but It was getting multiple rows. And, only dual table has that characteristic, which returns only one row.
Later I recall, I have done few changes in dual table and when I executed dual table. Then found multiple rows.
So, I truncated
dual table and inserted only row which
X value. And, everything working fine.
~ Answered on 2015-05-28 06:58:46
One possible explanation is a database trigger that fires for each
DROP TABLE statement. To find the trigger, query the
_TRIGGERS dictionary views:
select * from all_triggers where trigger_type in ('AFTER EVENT', 'BEFORE EVENT')
disable any suspicious trigger with
alter trigger <trigger_name> disable;
and try re-running your
DROP TABLE statement
~ Answered on 2015-05-27 10:14:05