Re: ORACLE UPGRADE 10.2.0.2 ... INVALID MATERIALIZED VIEW
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