Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: View getting invalidated
Yash R. Ganthe wrote:
>
> Hi,
> I have 2 tables A and B. The schema of both is identical. A view V is
> defined as ( select * from A union all select * from B).
> If I drop a particular column from both A and B, the view V is left
> invalidated. The Oracle documentation also says so. Any view that depends on
> a table that has been altered, remains invalidated till it is recompiled.
>
> So I did a : alter view V compile.
> This command succeeded.But the view did not get validated.
>
> Can anyone guess why this happens? One would expect the query to be
> reevaluated after recompilation. Since there is a select *, it should work
> fine. But it doesn't. Can someone suggest something?
>
> Thanks
> Yash
select '*' gets transformed into the column names - you can see this from the definition in USER_VIEWS, thus the problem after dropping a column. Its best to explicitly list the columns in a view to avoid this.
hth
connor
-- ============================== Connor McDonald http://www.oracledba.co.uk "Some days you're the pigeon, some days you're the statue..."Received on Fri Apr 26 2002 - 14:50:41 CDT