Re: ORACLE UPGRADE 10.2.0.2 ... INVALID MATERIALIZED VIEW

"Magnus Bengtsson" <mbeng90_at_yahoo.se> schreef in bericht news:66seluF2m3f3jU1_at_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
>
>

Ok, thank you for this correction. This explains another situation we had recently where a developer copied a schema manually from one db to another, where MV's were 'replaced' with tables. It seems only the table part was copied then. A tool like pl/sql developer shows the MV as a table, with no reference to being a MV.

Regards,

Shakespeare Received on Sat Apr 19 2008 - 03:40:12 CDT