Meterialized view [message #519155] |
Wed, 10 August 2011 02:17 |
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 #519163 is a reply to message #519159] |
Wed, 10 August 2011 02:47 |
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 #519168 is a reply to message #519166] |
Wed, 10 August 2011 03:07 |
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 #519184 is a reply to message #519171] |
Wed, 10 August 2011 04:21 |
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 #519216 is a reply to message #519189] |
Wed, 10 August 2011 07:09 |
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
|
|
|