Invalid Package Body status for DBMS_AQADM_SYS

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.

Solution

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

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';
Look for other invalid objects that might cause the problem: select * from dba_objects where status <> 'VALID';
Run utlrp multiple times. (That's an official Oracle recommendation in some processes; do the same thing multiple times.)
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.
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.
Contact Oracle support.