3

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.
Ravi
  • 30,829
  • 42
  • 119
  • 173
  • 1
    did you try `purge dba_recyclebin`? It looks like you're hitting some Oracle bug. – ibre5041 May 27 '15 at 09:21
  • It seems like I can not run `purge dba_recyclebin`. – Ravi May 27 '15 at 09:23
  • purge recyclebin if you don't have DBA role – Rene May 27 '15 at 09:24
  • That's what I tried and I mentioned on my post – Ravi May 27 '15 at 09:28
  • When you do not have DBA privs. maybe you should contact your DBAs. Such a command should not fail. You can also try to set tracing on your session to see which recursive query was called from that purge command. There is quite high chance, that you will not be able to fix this without DBA privs. – ibre5041 May 27 '15 at 09:43
  • What does "show recyclebin" tell you? Did you perhaps try to (re-)drop a BIN$ table name? Which shouldn't be allowed, just trying to think how it could have got to an invalid state... You may need to raise a service request with Oracle. – Alex Poole May 27 '15 at 10:08
  • You changed the question significantly - are you getting that error dropping a table (and if so a normal table or one that is already in the recyclebin), or when you try to purge? – Alex Poole May 27 '15 at 10:08
  • I was getting error when I was trying to purge table. Now, when I tried to drop normal table. I got same error. So, I changed the question and made it in simple word. – Ravi May 27 '15 at 10:10
  • 1
    But if the drop errors only started after the purge errors, they are probably related. *Did* you try to re-drop a BIN$ table before the issue began? You need to contact Oracle support anyway, I think, or at least your DBA who may be able to fix it or may have to contact support. – Alex Poole May 27 '15 at 10:15

3 Answers3

1

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

Frank Schmitt
  • 30,195
  • 12
  • 73
  • 107
  • It is totally new user. I just created first object and trying to drop it. – Ravi May 27 '15 at 10:15
  • If it's a trigger, it might have been created in another schema. But I agree with the other comments - you should definitely contact your DBA and - if necessary - Oracle support. – Frank Schmitt May 27 '15 at 11:30
1

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.

Ravi
  • 30,829
  • 42
  • 119
  • 173
  • You must have been SYS to modify dual, which is a really bad idea ([weird things can and will happen](https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1562813956388)), so odd you couldn't see the DBA recyclebin. Modifying anything directly in any built-in schema is dangerous. – Alex Poole May 28 '15 at 07:44
  • Correct. It can be dangerous if we play with `dual` table. But, right now, I didn't had any choice – Ravi May 28 '15 at 14:06
0

I know the post is old and solved, but maybe someone is facing or will face my situation, so I want to leave the aquired knowledge here, after deal with the error for a week. I was facing the error: "ORA-00604: error occurred at recursive SQL level 1" , but with the internal error: " ORA-06502: error: character string buffer too smal numeric or value", this happened only when I try to logon the database, and using an specific driver, trying to connect from an Visual Studio C# application, the weirdest thing on that moment was that I connect from SQLDeveloper or TOAD and everything worked fine. Later I discovered that my machine name had this format "guillermo-aX474b5", then I proceed to rename it like this "guillermo" without the "-" and the other stuff, and it worked!! Looks like in some drivers and situations, ORACLE Database does'nt like the "-" in the LogOn connection.

Hope it helps!

Guillermo Alvarado
  • 1
  • 2