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

Thread: Invalid Objects

  1. #1
    Join Date
    Dec 2001
    Posts
    337
    Hello all,

    What are invalid objects? how do you detect them and how do you resolve them?

    Thanks
    Chucks_k

  2. #2
    Join Date
    Oct 2000
    Location
    Charlotte, USA
    Posts
    330
    Hi Chucks,
    select the status of the objects from dba_objects....by
    select object_name,object_type from dba_objects where status like 'INVALID';
    if you have invalid objects you will get a list otherwise you get no row selected...
    if you have invalid objects...
    try this script....as sys


    set heading off
    set pagesize 0
    set linesize 79
    set verify off
    set echo off

    spool comp_all.tmp

    select
    decode( OBJECT_TYPE, 'PACKAGE BODY',
    'alter package ' || OWNER||'.'||OBJECT_NAME || ' compile body;',
    'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || ' compile;' )
    from
    dba_objects A,
    sys.order_object_by_dependency B
    where
    A.OBJECT_ID = B.OBJECT_ID(+) and
    STATUS = 'INVALID' and
    OBJECT_TYPE in ( 'PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE',
    'TRIGGER', 'VIEW' )
    order by
    DLEVEL DESC,
    OBJECT_TYPE,
    OBJECT_NAME;

    spool off

    thanks.
    Thanigaivasan.


  3. #3
    Join Date
    Dec 2001
    Posts
    337
    Thanks for the script Thani...

    Now i have to find out why we getting these invalid objects..


  4. #4
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    If a table is referenced by a procedure or triggers is dropped then the procedure or trigger becomes invalid.

    regards
    anandkl
    anandkl

  5. #5
    Join Date
    Dec 2001
    Posts
    337
    Thanks Anand..
    Just found out that the sys.order_object_by_dependency is obselete in 8.1.7..even though it exists when a desc is done on it...Hence, to recomiple invalid objects (only procedures and packages) run the utlrp.sql script.

    Thanks
    Chucks_k

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