Invalid Db Objects
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Invalid Db Objects

  1. #1
    Join Date
    Nov 2003
    Location
    Kuwait
    Posts
    16

    Invalid Db Objects

    Hello Gurus,

    I have many invalid objects that belongs to system and sys. I could not recompile them as some says insufficient privilege and other says other objects are missing. I want to recompile them to enable me to proceed with my work. How can I do this. I am afraid to execute the catalog.sql as it may ruin my existing DB.

    Can you please advise me. I have attached a list of invalid objects in this post.
    Attached Files Attached Files

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    no worries is running catalog and catproc just make sure no users are connected to make sure.

    Its pefectly safe as long as your db is up and running normally.

    Have you recently done a migration or something?

  3. #3
    Join Date
    Oct 2003
    Posts
    312
    log in as SYS and run $ORACLE_HOME\rdbms\admin\utlrp.sql

  4. #4
    Join Date
    Nov 2003
    Location
    Kuwait
    Posts
    16
    I tried executing the catalog.sql and I ended up with a corrupted database. I ended up deleting the DB and recreating it. We cannot take that risk now as the DB is used by many users and is critical to our projects.

  5. #5
    Join Date
    Oct 2003
    Posts
    65
    You must have used utlrp.sql to compile objects...how did you got all these objects invalid???

  6. #6
    Join Date
    Nov 2003
    Location
    Kuwait
    Posts
    16
    Hello Gurus,

    I have executed catproc, catrep, and utlrp to no avail. The same number and set of objects are still invalid. I am afraid to execute the catalog.sql as I had experience with it crashing an instance I have made. I eventually ended with recreating the DB.

    Can you please teach me a work around on this problem please?

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    what types of objects are invalid & try finding root cause.

    have u done any migration (from 8.x to 9.x)?

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  8. #8
    Join Date
    Nov 2003
    Location
    Kuwait
    Posts
    16
    Hello Gurus,

    We did not migrate from 8.x to 9.x. We have been trying to setup replication in this database instance. Finding the root cause is one that I would like to know how as this will lead me to being able to compile successfully.

  9. #9
    Join Date
    Nov 2003
    Location
    Kuwait
    Posts
    16
    Hello Gurus,

    We have executed the catalog.sql, catproc.sql,and utlrp.sql but we still have many system invalid objects. Can you please help us recompile them successfully. Attached is the list of system objects that are invalid and a sample compilation output.

    Thank you for your help.
    Attached Files Attached Files

  10. #10
    Join Date
    Sep 2001
    Posts
    120
    hi,

    Run this script from sqlplus.
    Spool the file

    declare
    begin
    for i in (select owner,OBJECT_NAME,OBJECT_TYPE from dba_objects where OBJECT_TYPE in ('PACKAGE','PACKAGE BODY') and status='INVALID') loop
    if(i.OBJECT_TYPE = 'PACKAGE BODY')
    then
    execute immediate 'ALTER PACKAGE '||i.owner||'.'||i.OBJECT_NAME||' COMPILE BODY';
    else
    execute immediate 'ALTER PACKAGE '||i.owner||'.'||i.OBJECT_NAME||' COMPILE';
    end if;
    dbms_output.put_line(i.OBJECT_TYPE||i.object_name||'COMPILED');
    end loop;
    end;
    /

    U can add more object type in the in clause...

    Please let me know if this workd for as it did for me...

    regards
    saurabh
    Saurabh Garg
    OCP 9i

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