Why does a stored proc become invalid
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Why does a stored proc become invalid

Hybrid View

  1. #1
    Join Date
    Jan 2001
    Posts
    216
    Hi,

    Periodically, I observe that some of the stored procedures become invalid. There have been no changes to the tables, or to the stored procedure. Why does this happen ? Is there a way to see "why" a stored procedure is invalid ?

    Appreciate your help

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Typically, if a dependant object has been altered, it will invalidate the stored procedure. This includes stored procedures, packages, tables, types, etc.

    In Oracle 7 (And maybe in 8) a procedure could go "INVALID" if it wasn't accessed for a while. I'm not sure if this is still true.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Jan 2001
    Posts
    216
    Is there a way to see the reason why the stored procedure got invalid (something like show errors).

  4. #4
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    Hi,

    Try looking at DBA_ERRORS

  5. #5
    Join Date
    May 2001
    Location
    San Francisco, California
    Posts
    510
    A stored procedure becomes invalid typically when a referenced object is altered or becomes invalid. Here is a script to find out how procedures are invalid and to recompile them automatically.

    set heading off;
    set feedback on;
    set pages 10000;

    prompt ****** NOTE: Append a .sql when entering spoolfile name

    spool /temp/&&spoolfile

    select 'alter '||object_type, object_name
    ||' compile;'
    from user_objects
    where status = 'INVALID'
    and object_type != 'PACKAGE BODY'
    union
    select 'alter package '||NULL, object_name
    ||' compile body;'
    from user_objects
    where status = 'INVALID'
    and object_type = 'PACKAGE BODY';

    spool off

    @/temp/&&spoolfile

    host rm /temp/&&spoolfile del \temp\&&spoolfile

    undefine spoolfile;

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    you dont need any script, just run $ORACLE_HOME/rdbms/admin/utlrp.sql, it compiles all invalid objects (views, procs, packages, functions, triggers etc)

  7. #7
    Join Date
    Jan 2001
    Posts
    216
    When I looked into the utilrp.sql script, it does the following

    1. invalidate all pl/sql modules and recompile standard and dbms_standard
    2. recompile all invalid pl/sql modules

    and they say you have to connect as internal to do it.

    Does this script invalidate and recompile "ALL" the pl/sql modules in the database ? If so, isnt it an overhead ?

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Well, Pando sugested UTLRP.SQL, not UTLIRP.SQL.

    UTLRP.SQL only recompiles object which are invalid. So no overhead here.

    UTLIRP.SQL in turn first calls UTLIP.SQL which invalidates *all* object in the database, then it calls UTLRP.SQL which will recompile *all* database objects, which has just be invalidated by previous script. So yes, there is an overhead with this combination, but it has its purpose: it is ment to use after database migration where all objects should be recompiled.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #9
    Join Date
    Jan 2001
    Posts
    216
    Thanks so much for clarifying, I would NEVER have guessed there were two DIFFERENT scripts utlrp.sql and utilrp.sql !!

    I will use the utlrp.sql script to validate all invalid objects.

    Thanks Pando !

    Neelima

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