How to solve SQL Error ORA-00604 error occurred at recursive SQL level 1

Question

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

User · Answer

I was able to solve  ORA-00604  error  by Droping with purge   DROP TABLE tablename PURGE

User · Answer

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

User · Answer

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  lt trigger name gt  disable    and try re-running your DROP TABLE statement

[oracle] How to solve : SQL Error: ORA-00604: error occurred at recursive SQL level 1

Examples related to oracle

Examples related to oracle11g

Examples related to drop-table