Home » Server Options » Replication » Meterialized view (oracle 9i)
Meterialized view [message #519155] Wed, 10 August 2011 02:17 Go to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
Hi,

Can anybody clear as why I am getting the difference in the below query output?

SQL> select count(*) from user_objects where object_type='MATERIALIZED VIEW';

  COUNT(*)
----------
        56

SQL> select count(*) from user_mviews;

  COUNT(*)
----------
       232
Re: Meterialized view [message #519159 is a reply to message #519155] Wed, 10 August 2011 02:24 Go to previous messageGo to next message
Michel Cadot
Messages: 67465
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post your version wth 4 decimals.
Who is current user?

Regards
Michel
Re: Meterialized view [message #519163 is a reply to message #519159] Wed, 10 August 2011 02:47 Go to previous messageGo to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
SQL> show user;
USER is "SMART_M"
SQL> select count(*) from user_objects where object_type='MATERIALIZED VIEW';

  COUNT(*)
----------
        56

SQL> show user;
USER is "SMART_M"
SQL> select count(*) from user_mviews;

  COUNT(*)
----------
       232

SQL> select * from V$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
PL/SQL Release 9.2.0.6.0 - Production
CORE    9.2.0.6.0       Production
TNS for HPUX: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production

[Updated on: Wed, 10 August 2011 02:47]

Report message to a moderator

Re: Meterialized view [message #519166 is a reply to message #519163] Wed, 10 August 2011 02:58 Go to previous messageGo to next message
Michel Cadot
Messages: 67465
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I have no 9.2.0.6.
You have to investigate by yourself.
List all objects from first query, all from the second one, make the difference and investigate the "real" type of those that are in one and not in the other one.

Regards
Michel
Re: Meterialized view [message #519168 is a reply to message #519166] Wed, 10 August 2011 03:07 Go to previous messageGo to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
I have found the objects which are differing but I need to know why it is differing? also when i check some of the DDL of the differing objects
it showing as "CREATE OR REPLACE FORCE VIEW ....." , if this is a view then why it is listing under the user_mviews ?

Am really confused!!

Pls shower your thoughts on this..


Re: Meterialized view [message #519170 is a reply to message #519168] Wed, 10 August 2011 03:17 Go to previous messageGo to next message
luc_tran
Messages: 31
Registered: October 2010
Location: Viet Nam
Member

please post the complete DDL of the one with "CREATE OR REPLACE FORCE VIEW .....". May be we can have a better thought about it
Re: Meterialized view [message #519171 is a reply to message #519170] Wed, 10 August 2011 03:23 Go to previous messageGo to next message
luc_tran
Messages: 31
Registered: October 2010
Location: Viet Nam
Member

Also, query the differing objects in the user_objects to see what type of object they are.
Re: Meterialized view [message #519178 is a reply to message #519168] Wed, 10 August 2011 03:52 Go to previous messageGo to next message
Michel Cadot
Messages: 67465
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
YOU can see but WE can't.
We ONLY know what you post.
And what you post does not allow us to know anything.
So either investigate by yourself, either post sufficient information for us to know what happens.

Regards
Michel
Re: Meterialized view [message #519184 is a reply to message #519171] Wed, 10 August 2011 04:21 Go to previous messageGo to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
I checked the object type of differing objects. it is VIEW only. Now the big question is why VIEW listing under USER_MVIEWS?

SQL> select mview_name from user_mviews where mview_name='ACCESS_RIGHTS';

MVIEW_NAME
------------------------------
ACCESS_RIGHTS

SQL> select dbms_metadata.get_ddl('VIEW','ACCESS_RIGHTS','SMART_M') from dual;

DBMS_METADATA.GET_DDL('VIEW','ACCESS_RIGHTS','SMART_M')
--------------------------------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW "SMART_M"."ACCESS_RIGHTS" ("USER_ID", "ACCESS_COD
E", "ACCESS_LEVEL") AS
  select "USER_ID","ACCESS_CODE","ACCESS_LEVEL" from "SMART_M"."SNAP$_ACCESS_RIG
HTS" with read only



SQL> select dbms_metadata.get_ddl('MATERIALIZED_VIEW','ACCESS_RIGHTS','SMART_M') from dual;
ERROR:
ORA-31603: object "ACCESS_RIGHTS" of type MATERIALIZED_VIEW not found in schema "SMART_M"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA_INT", line 3209
ORA-06512: at "SYS.DBMS_METADATA_INT", line 3594
ORA-06512: at "SYS.DBMS_METADATA_INT", line 4483
ORA-06512: at "SYS.DBMS_METADATA", line 326
ORA-06512: at "SYS.DBMS_METADATA", line 410
ORA-06512: at "SYS.DBMS_METADATA", line 449
ORA-06512: at "SYS.DBMS_METADATA", line 615
ORA-06512: at "SYS.DBMS_METADATA", line 1221
ORA-06512: at line 1

no rows selected

[Updated on: Wed, 10 August 2011 04:31]

Report message to a moderator

Re: Meterialized view [message #519189 is a reply to message #519184] Wed, 10 August 2011 04:45 Go to previous messageGo to next message
Michel Cadot
Messages: 67465
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So this seems a bug you have to upgrade to the latest patchset.

Regards
Michel
Re: Meterialized view [message #519216 is a reply to message #519189] Wed, 10 August 2011 07:09 Go to previous message
luc_tran
Messages: 31
Registered: October 2010
Location: Viet Nam
Member

Hi Sathik,

My initial thought is that ACCESS_RIGHTS view is a normal view which base on a Mview, so it is considered as a Mview and listed in the user_mviews, basically it's a normal view so its object type should be "VIEW". Not sure if it is right, just my opinion

Regards,
Luc

[Updated on: Wed, 10 August 2011 07:10]

Report message to a moderator

Previous Topic: GoldenGate 11.1.1.1 Encrypted TableSpace
Next Topic: dbms_job
Goto Forum:
  


Current Time: Sun Oct 25 12:25:10 CDT 2020