Re: Procedure Invalid - Urgent

>>

In Prod
create table emp(
col1 VARCHAR2(4000 CHAR),
col 2 VARCHAR2(4000 CHAR),
col 3 VARCHAR2(4000 CHAR)
)

In QA
create table emp(
col1 VARCHAR2(4000),
col 2 VARCHAR2(4000),
col 3 VARCHAR2(4000)
)
>>

suggestion: describe the table in prod and qa and compare the output.

>>

It look like this materialized view become invalid at each refresh and hence all procedure using this MV become invalid.
>>

some thoughts: you can compile the MV to see if you get any errors, may be the base table's definition is not in sync, try a manual refresh to see what errors you get, any errors in dump dest?

-Arul

On 10/11/06, Sanjay Mishra <smishra_97_at_yahoo.com> wrote:
>
> Raj
>
> These are Application Procedure who become invalid and yes it is 10g. IS
> the following definition are different
>
> In Prod
> create table emp(
> col1 VARCHAR2(4000 CHAR),
> col 2 VARCHAR2(4000 CHAR),
> col 3 VARCHAR2(4000 CHAR)
> )
>
> In QA
> create table emp(
> col1 VARCHAR2(4000),
> col 2 VARCHAR2(4000),
> col 3 VARCHAR2(4000)
> )
>
> I have Materialized view on these tables on both QA and Prod. It look like
> this materialized view become invalid at each refresh and hence all
> procedure using this MV become invalid. This is my initial finding. I am
> thankfull if somebody correct that both above ddl are same
>
> TIA
> Sanjay
>
>
> *rjamya <rjamya_at_gmail.com>* wrote:
>
> since you say recyclebin, I guess you are on 10g, so are those
> procedures owned by SYS? if so it is normal then due to AWR activity.
> There is periodic partition add/drop activity and that causes some SYS
> packages to go invalid. This will NOT affect your production
> applications. It is documented as such on Metablink.
>
> Or, you are talking about procedures in non SYS schema?
> HTHs
> Raj
>
> On 10/11/06, Sanjay Mishra wrote:
> > Peter
> >
> > I checked and no procedure/Package is dropping any objects. Recylebin is
> > also empty.
> >
> > Thanks for help
> > Sanjay
> >
> >
> > Peter McLarty wrote:
> >
> > Hi
> >
> > Look for something dropping an object these use.
> > I had an application that did a batch load and the developers had
> decided to
> > drop a table as part of the clean up of the staging point before loading
> new
> > data. It always invalidated the procedures that were called to load the
> data
> > to its eventual location
> > I had them alter the clean up to truncate of the staging tables
> >
> > HTH
> >
> > Peter McLarty
> > Technical Consultant
> > Service Delivery
> > Technology One
> >
> > ________________________________________
> > From: oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org]
> > On Behalf Of Sanjay Mishra
> > Sent: Thursday, 12 October 2006 9:16 AM
> > To: oracle-l_at_freelists.org
> > Subject: Procedure Invalid - Urgent
> >
> > I have one application in production where several Procedure become
> invalid
> > at the same time. Can somebody suggest as how I can check as why all
> become
> > invalid. Currently I am running utlrp every 5 minutes so that
> application is
> > not affected. This is the new aplication went live on the weekend,
> >
> > TIA
> > Sanjay
> >
> > ________________________________________
> > Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone calls. Great
> rates
> > starting at 1¢/min.
> >
> >
> >
> > ________________________________
> > Stay in the know. Pulse on the new Yahoo.com. Check it out.
> >
> >
>
>
> --
> ----------------------------------------------
> Got RAC?
>
>
> ------------------------------
> Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone calls. Great rates
> starting at 1¢/min.
> <http://us.rd.yahoo.com/mail_us/taglines/postman7/*http://us.rd.yahoo.com/evt=39666/*http://messenger.yahoo.com>
>
>

--
http://www.freelists.org/webpage/oracle-l