why storage procedure turned invaild?

[faust2004]

Hi, friend,

My oracle server (7.3.4 on dec 4.0D)is 7*24
, when i compile storage procedure , some other procedure will turn invaild and could not be compiled, sometimes I have to restart the server to compile them ,
such thing have comfused us for a long time ,why? how to solve it ?


Thanks
Sunny

[Dave Chamberlin]

Stored packages and procedures become invalid when the objects that they refer to are modified. If you have item A that references item B inside it, and item B becomes invalid, so does item A. If the item is pinned in the SGA, many packages etc are pinned to help performance - you have to unpin it to recompile it.
To see if your item is pinned do:
select * from v$db_object_cache and look for your item with kept=yes to indicate pinning.

[Jean-Luc Oudart]

1st I would say please know your "invalid" objects in the database :
select * dba_objects where status='INVALID'
Therefore when something goes wrong you check the new list of invalid objects.

To recompile if you have a big list you may want to automate the "recomp" procedure.
cf. attachment for some example

Jean-Luc

[faust2004]

I use the script to find the invaild storage procedure
select 'alter procedure '||owner||'.'||object_name||' compile;' from dba_ob
jects
where object_type='PROCEDURE' and status='INVALID' and owner not in('SYS
','SYSTEM')

[faust2004]

hi , Dave
how to unpin the pinned storage procedure?

[Printaporn_1]

Hi Sunny

use
show errors
in SQL prompt
after complie error to see what is the exact error message.