Simpler Navigation coming for Servers and Operating Systems
Coming soon: a much simpler Servers and Operating Systems section of the Community. We will combine many of the older boards, and you won't have to click through so many levels to get at the information you need. If you are looking for an older board and do not find it, check the consolidated boards, as the posts are still there.
cancel
Showing results for 
Search instead for 
Did you mean: 

why storage procedure turned invaild?

faust2004
Regular Advisor

why storage procedure turned invaild?

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



5 REPLIES
Dave Chamberlin
Trusted Contributor

Re: why storage procedure turned invaild?

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
Honored Contributor

Re: why storage procedure turned invaild?

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
fiat lux
faust2004
Regular Advisor

Re: why storage procedure turned invaild?

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
Regular Advisor

Re: why storage procedure turned invaild?

hi , Dave
how to unpin the pinned storage procedure?
Printaporn_1
Esteemed Contributor

Re: why storage procedure turned invaild?

Hi Sunny

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



enjoy any little thing in my life