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.
Best Answer
One possible cause for the recursive SQL error is triggers. You might have run into this scenario:
To get a list of all triggers, you can use
(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.