Re: Procedure Invalid - Urgent
- From: Sanjay Mishra <smishra_97@xxxxxxxxx>
- To: Arul Ramachandran <contactarul@xxxxxxxxx>
- Date: Wed, 11 Oct 2006 20:23:31 -0700 (PDT)
Output is already as per the Create table script. I modify the table using
ALTER table command so as to look like QA and even recreate the MV but the
errors are still the same that each MV refresh make the Procedure invalid
ANy idea or direction
Sanjay
Arul Ramachandran <contactarul@xxxxxxxxx> wrote:
>>
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@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
> On Behalf Of Sanjay Mishra
> Sent: Thursday, 12 October 2006 9:16 AM
> To: oracle-l@xxxxxxxxxxxxx
> 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.
---------------------------------
How low will we go? Check out Yahoo! Messenger?s low PC-to-Phone call rates.
Other related posts:
