Invalid Package Body status for DBMS_AQADM_SYS

Question

Was trying to refresh a database which failed when dropping the AQ table 'SYSTEM.DEF$_AQCALL' with this error.

//------------

1. SqlBRuntimeException: Dropping Tables failed after 6/179 items (1 errors)

2. SqlBException: Fatal error executing resource: clean\tables\def$_aqcall

3. QueryException: Error executing resource (delimiter = '/'): clean\tables\def$_aqcall

4. QueryException: Error executing SQL statement (errcode=24005, sqlstate=99999: ORA-24005: Inappropriate utilities used to perform DDL on AQ table SYSTEM.DEF$_AQCALL):

DROP TABLE def$_aqcall CASCADE CONSTRAINTS

5. SQLException: errorcode=24005, sqlstate=99999, line=-1: ORA-24005: Inappropriate utilities used to perform DDL on AQ table SYSTEM.DEF$_AQCALL

//------------

Then tried manually stopping the queue and dropping the queue table even with the 'Force' option, but no luck.

When stopping the queue this is the error I'm getting.

//---------------------------

Error starting at line 5 in command:
BEGIN
   DBMS_AQADM.STOP_QUEUE(
      queue_name        => 'SYSTEM.DEF$_AQCALL');
END;

Error report:
ORA-04063: package body "SYS.DBMS_AQADM_SYS" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_AQADM_SYS"
ORA-06512: at "SYS.DBMS_AQADM", line 464
ORA-06512: at line 2
04063. 00000 -  "%s has errors"
*Cause:    Attempt to execute a stored procedure or use a view that has
           errors.  For stored procedures, the problem could be syntax errors
           or references to other, non-existent procedures.  For views,
           the problem could be a reference in the view's defining query to
           a non-existent table.
           Can also be a table which has references to non-existent or
           inaccessible types.
*Action:   Fix the errors and/or create referenced objects as necessary.

//--------

Checking on the 'DBMS_AQADM_SYS' package using;

select owner,object_name,object_type,status from dba_objects where object_name='DBMS_AQADM_SYS';

shows that the Status of the 'Package Body' is 'INVALID' and I assume that this could be the cause of the above error.

Next step was to recompile this package to fix any issues in the package.

I recompiled the package as SYSDBA;

EXECUTE UTL_RECOMP.RECOMP_SERIAL();

as per http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/u_recomp.htm#i1000051

This completed without any errors, but still the Status of the Package Body is 'Invalid'.

Tried using

sqlplus / as sysdba @?/rdbms/admin/utlrp 

as per DBMS_METADATA and other packages invalid but still no luck.

Checked few forums and everyone suggests to recompile the package.

Appreciate if you have any insight on this issue.

Thanks.

Answer 1

Here are some ideas, in the order I would attempt them:

1. Try to figure out why it's invalid. If you're lucky the error message will give you a clue: select * from dba_errors where name = 'DBMS_AQADM_SYS';
2. Look for other invalid objects that might cause the problem: select * from dba_objects where status <> 'VALID';
3. Run utlrp multiple times. (That's an official Oracle recommendation in some processes; do the same thing multiple times.)
4. Manually recompile objects like this: alter package sys.dbms_aqadm_sys compile;. Recompiling objects may invalidate others, you may need to manually recompile them in a specific order.
5. Talk to your DBAs and find out if there was any recent maintenance on the servers lately. The only time I've had to do step #4 was after an upgrade.
6. Contact Oracle support.