Re: ORACLE UPGRADE 10.2.0.2 ... INVALID MATERIALIZED VIEW

From: Magnus Bengtsson <mbeng90_at_yahoo.se>
Date: Fri, 18 Apr 2008 22:29:46 +0200
Message-ID: <66seluF2m3f3jU1@mid.individual.net>

"Shakespeare" <whatsin_at_xs4all.nl> skrev i meddelandet news:4808cf84$0$14357$e4fe514c_at_news.xs4all.nl...
>
> "DA Morgan" <damorgan_at_psoug.org> schreef in bericht
> news:1208536299.522086_at_bubbleator.drizzle.com...
>> aman.oracle.dba wrote:
>>> I have upgrade oracle from 9.2.0.2 to 10.2.0.2 on SunOS.
>>>
>>> SQL> alter MATERIALIZED VIEW MVIEWUSER.PCATTYPE compile;
>>>
>>> Materialized view altered.
>>>
>>> SQL> select status,object_type from dba_objects where
>>> owner='MVIEWUSER' AND object_name='PCATTYPE';
>>>
>>> STATUS OBJECT_TYPE
>>> ------- -------------------
>>> VALID TABLE
>>> INVALID MATERIALIZED VIEW
>>>
>>> Can someone kindly help in it.
>>>
>>> Thanks
>>
>> Your statement is logically impossible.
>>
>> SQL> CREATE MATERIALIZED VIEW servers
>> 2 TABLESPACE uwdata
>> 3 NOCACHE
>> 4 LOGGING
>> 5 NOCOMPRESS
>> 6 NOPARALLEL
>> 7 BUILD IMMEDIATE
>> 8 REFRESH FORCE ON DEMAND
>> 9 WITH ROWID AS
>> 10 SELECT * FROM servers;
>> SELECT * FROM servers
>> *
>> ERROR at line 10:
>> ORA-00955: name is already used by an existing object
>>
>> SQL>
>> --
>> Daniel A. Morgan
>> Oracle Ace Director & Instructor
>> University of Washington
>> damorgan_at_x.washington.edu (replace x with u to respond)
>> Puget Sound Oracle Users Group
>> www.psoug.org
>
> That's what I meant 3 days ago.
>
> Shakespeare

Maybe I missed a post in this thread, but the OP never said that's how the mview was created, did he?

If you get that impression because of the two rows in dba_objects, that is simply how materialized views work.
CREATE MATERIALIZED VIEW... will create both a TABLE and a MATERIALIZED VIEW entry in *_OBJECTS.

Example:
alopex$ uname -a
SunOS alopex 5.11 snv_86 i86pc i386 i86pc alopex$ sqlplus /

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Apr 18 21:33:58 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

ops$magnus_at_MBS102> select object_name, object_type, status from dba_objects where object_name='TESTMVIEW';

no rows selected

ops$magnus_at_MBS102> create materialized view TESTMVIEW as select * from all_objects;

Materialized view created.

ops$magnus_at_MBS102> select object_name, object_type, status from dba_objects where object_name='TESTMVIEW';

OBJECT_NAME



OBJECT_TYPE

STATUS

TESTMVIEW
TABLE
VALID TESTMVIEW
MATERIALIZED VIEW
VALID To the OP:
You usually get more information from DBA_MVIEWS. Check the columns COMPILE_STATE and STALENESS. What happens when you select from the materialized view? What happens if you refresh the materialized view?

/Magnus Received on Fri Apr 18 2008 - 15:29:46 CDT

Original text of this message