How to solve: SQL error: ORA-00604: error at recursive SQL level 1
When I try to drop a table I get the 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.
source to share
I noticed the following line due to an error.
exact fetch returns more than requested number of rows
Then I thought. Oracle was expecting a single row, but it was getting multiple rows. And only a double table has this characteristic that only returns one row.
And, I remember I made a few changes to the double table and when I did the double table. Then several lines were found.
Now I am truncating the table dual
and only inserting the row whose value is X
. And everything works fine.
source to share
One possible explanation is a database trigger that fires for every statement DROP TABLE
. To find a trigger, search for dictionary words _TRIGGERS
:
select * from all_triggers
where trigger_type in ('AFTER EVENT', 'BEFORE EVENT')
disable any suspicious trigger with
alter trigger <trigger_name> disable;
and try to re-execute the statement DROP TABLE
source to share