CODEWITHSUNDEEP
sqloracle-databaseplsqloracle10goracle9i
Volodymyr Bezuglyy
Volodymyr Bezuglyy

Reputation: 16825

Cannot stop Oracle Queue - Could not find program unit being called: "SYS.DBMS_ASSERT"

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.

Upvotes: 1

Views: 3369

Answers (2)

dpbradley
dpbradley

Reputation: 11925

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).

Upvotes: 1

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50057

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.

Upvotes: 3

Related Questions