Invalid Objects
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Invalid Objects

  1. #1
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763

    Question

    Hi all,

    I little question for your. You know sometimes in the database are invalid objects, and when you make alter .... compile; this objects continue invalid.

    My question:

    Does anybody know where Oracle store the sql code definition for this invalid objects?

    Thanks and best regards

    Angel

  2. #2
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    What type of objects are you asking about?
    David Knight
    OCP DBA 8i, 9i, 10g

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    sometimes the order of compilation affects the status of the object, dependencies and such

  4. #4
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    Originally posted by dknight
    What type of objects are you asking about?
    In this case I have invalid views.

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    well look dba_views for view definitions

  6. #6
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    Originally posted by pando
    sometimes the order of compilation affects the status of the object, dependencies and such
    Yes Pando I know, normally I compile invalid objects 2/3 times with:

    select 'ALTER ' || OBJECT_TYPE || ' '||OWNER||'.' || OBJECT_NAME || ' COMPILE;'
    FROM DBA_OBJECTS
    WHERE STATUS = 'INVALID'
    AND OBJECT_TYPE <> 'PACKAGE BODY'
    UNION
    select 'ALTER PACKAGE ' ||OWNER||'.'|| OBJECT_NAME || ' COMPILE BODY;'
    FROM DBA_OBJECTS
    WHERE STATUS = 'INVALID'
    AND OBJECT_TYPE = 'PACKAGE BODY';

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    well that script doesnt compile the objects in dependency tree order does it...

  8. #8
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by aarroyob
    Hi all,

    I little question for your. You know sometimes in the database are invalid objects, and when you make alter .... compile; this objects continue invalid.

    My question:

    Does anybody know where Oracle store the sql code definition for this invalid objects?

    Thanks and best regards

    Angel
    PROCEDURE RECOMPILE IS
    cur Number;
    I Number;
    sqlpac_cur integer;
    begin
    I := 0;
    loop -- infinite
    select curnt(*) into cur
    from User_Objects
    where object_type in ('FUNCTION','PROCEDURE','TRIGGER','PACKAGE BODY','PACKAGE','VIEW')
    and object_name!='RECOMPILE'
    and STATUS = 'INVALID';
    If cur = 0 OR I > 10 then return; end if;
    I := I +1;
    for FPCur in (select distinct object_name,object_type
    from user_objects
    where object_type in ('FUNCTION','PROCEDURE','TRIGGER','PACKAGE BODY','PACKAGE','VIEW')
    and object_name!='RECOMPILE'
    and STATUS = 'INVALID'
    order by object_type desc
    ) Loop
    begin
    sqlpac_cur:=dbms_sql.open_cursor;
    if FPCur.Object_type = 'PACKAGE BODY' then FPCur.Object_type := 'PACKAGE'; end if;
    dbms_sql.parse(sqlpac_cur,'alter '||FPCur.Object_type||' '||FPCur.object_name||' compile',dbms_sql.v7);
    dbms_output.put_line('processed: '||FPCur.Object_type||' '||FPCur.object_name);
    dbms_sql.close_cursor(sqlpac_cur);
    EXCEPTION
    WHEN OTHERS THEN
    dbms_sql.close_cursor(sqlpac_cur);
    dbms_output.put_line('failed: '||FPCur.object_type||' '||FPCur.object_name||' '||sqlerrm);
    end;
    end loop;
    end loop;

    end;

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    well utlrp.sql does that trick but doesnt follow the dependency tree neither

  10. #10
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    I also just have to recompile invalid objects more than once.
    Do you guys have script to identify the dependency first before
    the creation of the script to recompile invalid objects?

    If such script is available, then compilation of invalid objects will only be done once, and that's great :-))

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