Invalid Object
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Invalid Object

  1. #1
    Join Date
    Jan 2002
    Posts
    96

    Exclamation Invalid Object

    Hi Guys

    I have invalid Package body's namely:

    SQL> select object_type, owner, object_name from dba_objects where status like 'INVALID';

    OBJECT_TYPE OWNER OBJECT_NAME
    ------------------ ------------------------------ ---------------------------------------------------
    ----------------------------
    PACKAGE BODY SYS DBMS_DDL
    PACKAGE BODY SYS DBMS_UTILITY
    PACKAGE BODY SYS DBMS_ALERT
    PACKAGE BODY SYS DBMS_PRVTAQIM
    PACKAGE BODY SYS DBMS_SHARED_POOL


    tried the following:

    Alter PACKAGE (PACKAGE_NAME) compile body;

    and got the following error with all of them:

    Warning: Package Body altered with compilation errors.


    When I do a select name, text from dba_errors they all show:

    PLS-00103: Encountered the symbol "SOURCE" when expecting one of the following:

    is as compress compiled wrapped


    What I want to know is seeing that I can't recompile the package body, do I have to recreate the package and if so, how do i do that for the sys.DBMS_(packages) that aren't valid? Or is there another solution to make them valid?

    Thanks in advance,
    Charlton
    Last edited by charlton; 06-09-2006 at 07:54 AM.

  2. #2
    Join Date
    Jun 2006
    Posts
    259
    RUN:
    $ORACLE_HOME/rdbms/admin/utlrp.sql

  3. #3
    Join Date
    Jan 2004
    Posts
    22
    I have seen some time by giving select and execute permission to the user for sys_dbms,sys_alert etc had resolved the problem.

  4. #4
    Join Date
    Jan 2002
    Posts
    96
    Hi Guys
    Thanks for the replies. I checked the user and the user already as permissions on dbms_alert and dbms_utility.
    I want to try the utlrp.sql script but I have a few concerns.
    As i understand the code for the utlrp.sql script, it only recompiles packages. Will it recompile the package bodies as well? And if i run the utlrp.sql script, i'm scared it might just make more packages invalid than fix it.

  5. #5
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    No, dont get scared, it would not do other way. Go ahead and run the utlrp.sql as sys user.
    "What is past is PROLOGUE"

  6. #6
    Join Date
    Jun 2006
    Posts
    259
    Run: utlrp.sql
    See the documentation for what this does.

    If the packages come back as invalid. Then you'll probably need to run catalog.sql and catproc.sql. This will re-install your catalog and procedures. Then they should be valid. These can take a long time to run, thats why you should try to re-compile first using utlrp. If you have to run catalog/catproc, make sure you've booted the users out or start the system up in "restricted" mode.

  7. #7
    Join Date
    Jan 2002
    Posts
    96
    OK, I tried the following and I still have the same Inalid Objects:

    I recreated all the packages that i could :
    @utlrp.sql
    @dbmsstat.sql,
    @dbmsspool.sql,
    @dbmsutil.sql


    Tried recompiling manually as well. but no luck

    I need to try running catalog and catproc.sql

    But do i need to shutdown and start the DB in restrict mode. As this is a 24x7 database.

    And why wouldn't the recreate scripts sort them out?

  8. #8
    Join Date
    Jan 2002
    Posts
    96

    Thumbs up

    WORKING!!!!
    Just ran catproc.sql and utlrp.sql to recompile and no more errors.
    Thanks for the advise, Ixion and the rest of the guys who gave there input.

  9. #9
    Join Date
    Jun 2006
    Posts
    259
    Glad it worked!

    Ken

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width