RE: Re-compile SYS.DBMS_ASYNCRPC_PUSH and SYS.DBMS_DEFER_SYS_PART

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Tue, 03 Apr 2001 15:02:11 -0700
Message-ID: <F001.002E0963.20010403150105@fatcity.com>

see answer below.

> -----Original Message-----
> From: Guang Mei [mailto:zlmei_at_hotmail.com]
>
> Oracle 8.0.5 on Sun.
>
> I am trying to re-compile some invalid objects owned by SYS in our db.
>
> But when I tried I got some error which I am not sure how to
> resolve (see
> code below).
>
> I also tried things such as
>
> select text
> from dba_source
> where owner='SYS'
> and name = 'DBMS_ASYNCRPC_PUSH'
> and type = 'PACKAGE BODY'
> order by line;
>
> to look at the package code, but not much luck there.
>
> So, what could I do to make these objects valid?
>
> --------------------
> SQL> select owner, object_name,object_type from all_objects

>   2  where status='INVALID'
>   3  and owner = 'SYS'
>   4  order by owner desc, object_type, object_name;

>
> OWNER                          OBJECT_NAME                   
> OBJECT_TYPE
> ------------------------------ ------------------------------
> ---------------
> SYS                            DBMS_ASYNCRPC_PUSH            
> PACKAGE BODY
> SYS                            DBMS_DEFER_SYS_PART1          
> PACKAGE BODY
>
>
> SQL> alter package sys.DBMS_ASYNCRPC_PUSH compile body;
>
> Warning: Package Body altered with compilation errors.
>
> SQL> show errors;
> Errors for PACKAGE BODY SYS.DBMS_ASYNCRPC_PUSH:
>
> LINE/COL ERROR
> --------
> -----------------------------------------------------------------
> 103/27   PL/SQL: Statement ignored
> 103/49   PLS-00302: component 'RESULT_STARTUP_SECONDS' must
> be declared
> SQL> alter package sys.DBMS_DEFER_SYS_PART1 compile body;
>
> Warning: Package Body altered with compilation errors.
>
> SQL> show errors;
> Errors for PACKAGE BODY SYS.DBMS_DEFER_SYS_PART1:
>
> LINE/COL ERROR
> --------
> -----------------------------------------------------------------
> 306/7    PL/SQL: Statement ignored
> 306/56   PLS-00302: component 'MISSINGUSER_NUM' must be declared
> 328/5    PL/SQL: Statement ignored
> 328/20   PLS-00302: component 'DELETE_ERROR' must be declared

Without doing a lot of research into the matter: Have you tried looking in $ORACLE_HOME/*/admin for a ".sql" script that defines the components that are missing? For example, missinguser_num is defined by $ORACLE_HOME/rdbms/admin/dbmsdefr.sql (see find command below)

$ find $ORACLE_HOME/*/admin -name "*.sql" -exec grep -il missinguser_num {} \; /oracle/ora32/product/8.1.6/rdbms/admin/dbmsdefr.sql

Also some of the Oracle packages have source text that is "wrapped", meaning encrypted. It looks like DBMS_ASYNCRPC_PUSH has "wrapped" source (at least it does in my 8.1.6 database).

---

Jacques R. Kilchoer
(949) 754-8816
Quest Software, Inc.
8001 Irvine Center Drive
Irvine, California 92618
U.S.A.
http://www.quest.com Received on Tue Apr 03 2001 - 17:02:11 CDT