Oracle 11g: ORA-00604: error occurred at recursive SQL level 1

Question

I executed the script below and it works:

BEGIN
   FOR cur_rec IN (SELECT object_name, object_type
                 FROM user_objects
                WHERE object_type IN
                         ('TABLE',
                          'VIEW',
                          'PACKAGE',
                          'PROCEDURE',
                          'FUNCTION',
                          'SEQUENCE'
                         ))
 LOOP
  BEGIN
     IF cur_rec.object_type = 'TABLE'
     THEN
        EXECUTE IMMEDIATE    'DROP '
                          || cur_rec.object_type
                          || ' "'
                          || cur_rec.object_name
                          || '" CASCADE CONSTRAINTS';
     ELSE
        EXECUTE IMMEDIATE    'DROP '
                          || cur_rec.object_type
                          || ' "'
                          || cur_rec.object_name
                          || '"';
     END IF;
  EXCEPTION
     WHEN OTHERS
     THEN
        DBMS_OUTPUT.put_line (   'FAILED: DROP '
                              || cur_rec.object_type
                              || ' "'
                              || cur_rec.object_name
                              || '"'
                             );
  END;
   END LOOP;
END;
/

But the problem is, after this, I cant grant, create or drop etc. in my database even I'm using a sysdba user.

I am getting the error:

ORA-00604: error occurred at recursive SQL level 1 ORA-00942: table or view does not exist

Please help. Thanks.

Please tell me you didn't run that script as the SYS or SYSTEM user... — Frank Schmitt
no. i use a different user on running this script which is ICBI. — beautifulmonster

Frank Schmitt Frank Schmitt · Accepted Answer · 2014-07-23T07:58:11

One possible cause for the recursive SQL error is triggers. You might have run into this scenario:

you have a trigger that fires for every DDL statement
this trigger tries to insert records into some kind of audit/log table
you audit/log table was dropped by your cleanup script

To get a list of all triggers, you can use

select * from dba_triggers
where trigger_type not in ('BEFORE EACH ROW','AFTER EACH ROW')

(you can exclude row-level triggers because they conceptually belong to the table and would have been automatically dropped when you dropped the table). After you've identified the offending trigger, you can either disable or drop it.

Oracle 11g: ORA-00604: error occurred at recursive SQL level 1

1 Answers