无法停止 Oracle 队列 - 找不到正在调用的程序单元：“SYS.DBMS_ASSERT”
无法停止和删除 Oracle 队列。
以下代码
BEGIN
DBMS_AQADM.STOP_QUEUE (
queue_name => 'TEST_QUEUE');
DBMS_AQADM.DROP_QUEUE(
queue_name => 'TEST_QUEUE');
END;
/
产生以下错误：
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04065: not executed, altered or dropped stored procedure "SYS.DBMS_ASSERT"
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_ASSERT"
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 3365
ORA-06512: at "SYS.DBMS_AQADM", line 167
ORA-06512: at line 5
此问题的根本原因是什么？
更新：
SQL> SELECT * FROM USER_TAB_PRIVS where table_name='DBMS_ASSERT' and GRANTEE='TEST_USER'
...
GRANTEE=TEST_USER
OWNER=SYS
TABLE_NAME=DBMS_ASSERT
GRANTOR=SYS
PRIVILEGE=EXECUTE
GRANTABLE=NO
HIERARCHY=NO
SQL> SELECT * FROM USER_TAB_PRIVS where table_name='DBMS_AQADM' and GRANTEE='TEST_USER'
...
GRANTEE=TEST_USER
OWNER=SYS
TABLE_NAME=DBMS_AQADM
GRANTOR=SYSTEM
PRIVILEGE=EXECUTE
GRANTABLE=NO
HIERARCHY=NO
我已经检查了一些模式中的 USER_TAB_PRIVS 表，我可以看到表名为“DBMS_ASSERT”的记录仅存在于 TEST_USER 模式中。
用户具有 EXECUTE 权限。
Cannot stop and drop oracle Queue.
Following code
BEGIN
DBMS_AQADM.STOP_QUEUE (
queue_name => 'TEST_QUEUE');
DBMS_AQADM.DROP_QUEUE(
queue_name => 'TEST_QUEUE');
END;
/
produces following errors:
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04065: not executed, altered or dropped stored procedure "SYS.DBMS_ASSERT"
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_ASSERT"
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 3365
ORA-06512: at "SYS.DBMS_AQADM", line 167
ORA-06512: at line 5
What can be the root cause of this problem?
UPDATE:
SQL> SELECT * FROM USER_TAB_PRIVS where table_name='DBMS_ASSERT' and GRANTEE='TEST_USER'
...
GRANTEE=TEST_USER
OWNER=SYS
TABLE_NAME=DBMS_ASSERT
GRANTOR=SYS
PRIVILEGE=EXECUTE
GRANTABLE=NO
HIERARCHY=NO
SQL> SELECT * FROM USER_TAB_PRIVS where table_name='DBMS_AQADM' and GRANTEE='TEST_USER'
...
GRANTEE=TEST_USER
OWNER=SYS
TABLE_NAME=DBMS_AQADM
GRANTOR=SYSTEM
PRIVILEGE=EXECUTE
GRANTABLE=NO
HIERARCHY=NO
I’ve checked the table USER_TAB_PRIVS in a few our schemas and I can see that record with table name 'DBMS_ASSERT' exists in schema with in TEST_USER only.
User have EXECUTE privilege.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
在我看来，要么 DBMS_ASSERT 包不存在（不太可能，但我认为可能），要么您用来登录数据库的用户没有执行权限。通常，PUBLIC 被授予对 DBMS_ASSERT 的 EXECUTE 访问权限，但可能在您的站点上已更改。检查 DBMS_ASSERT 和 DBMS_AQADM 上的 EXECUTE 权限授予。
It looks to me like either the DBMS_ASSERT package doesn't exist (unlikely but I suppose possible), or the user you used to log into the database doesn't have execute rights on it. Typically PUBLIC is granted EXECUTE access to DBMS_ASSERT but perhaps it was changed at your site. Check EXECUTE permission grants on DBMS_ASSERT and DBMS_AQADM.
如果您之前进行过此调用而没有任何问题，那么 ORA-04068 错误会让我认为调用链中的某些内容已失效。您最近是否对安装应用了任何升级或补丁？
Oracle 在 $ORACLE_HOME/rdbms/admin 中提供了一个脚本 utlrp，它将重新编译所有包并报告任何剩余的无效包。让您的管理员运行它（作为 SYS）。
If you've made this call before without any problems, then the ORA-04068 error makes me think that something in the calling chain has been invalidated. Have you applied any upgrades or patches to the installation recently?
Oracle supplies a script, utlrp in $ORACLE_HOME/rdbms/admin, that will recompile all of the packages and report on any remaining invalid. Have your administrator run that (as SYS).