Oracle 11g：ORA-00604：递归 SQL 级别 1 发生错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24901645/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle 11g: ORA-00604: error occurred at recursive SQL level 1
提问by beautifulmonster
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.
但问题是，在此之后，即使我使用的是 sysdba 用户，我也无法在我的数据库中授予、创建或删除等。
I am getting the error:
我收到错误：
ORA-00604: error occurred at recursive SQL level 1 ORA-00942: table or view does not exist
ORA-00604: 递归 SQL 级别 1 发生错误 ORA-00942: 表或视图不存在
Please help. Thanks.
请帮忙。谢谢。
回答by Frank Schmitt
One possible cause for the recursive SQL error is triggers. You might have run into this scenario:
递归 SQL 错误的一种可能原因是触发器。您可能遇到过这种情况：
- 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
- 你有一个触发器为每个 DDL 语句触发
- 此触发器尝试将记录插入某种审计/日志表
- 您的清理脚本删除了您的审计/日志表
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.
（您可以排除行级触发器，因为它们在概念上属于该表，并且会在您删除该表时自动删除）。确定违规触发器后，您可以禁用或删除它。
