Problem with Materialized View compile state [message #441723] |
Tue, 02 February 2010 21:41  |
vikram1780
Messages: 222 Registered: January 2007 Location: Bangalore
|
Senior Member |
|
|
Hi,
The state of materialized view in dba_objects and dba_mviews is invalid.
But the base view on which materialized view built is valid. Even the materialized is refreshing successfully with out any issues.
Queried the status of materialized view in dba_snapshots. The status is valid.
The materialized view MV_V_CDRO_DMAN_ACTUALS_445 is built on view
V_CDRO_DMAN_ACTUALS_445 which is valid.
checked the sql of view V_CDRO_DMAN_ACTUALS_445 and found its valid.
Below are the sql executed.
select count(1) from MV_V_CDRO_DMAN_ACTUALS_445;
count:1032468
select count(1) from V_CDRO_DMAN_ACTUALS_445;
count:1032468
select * from dba_mviews t where t.mview_name='MV_V_CDRO_DMAN_ACTUALS_445';
OWNER CM_DCP_REPORTING_OWNER
MVIEW_NAME MV_V_CDRO_DMAN_ACTUALS_445
CONTAINER_NAME MV_V_CDRO_DMAN_ACTUALS_445
QUERY <Long>
QUERY_LEN 38
UPDATABLE N
UPDATE_LOG
MASTER_ROLLBACK_SEG
MASTER_LINK
REWRITE_ENABLED N
REWRITE_CAPABILITY NONE
REFRESH_MODE DEMAND
REFRESH_METHOD COMPLETE
BUILD_MODE IMMEDIATE
FAST_REFRESHABLE NO
LAST_REFRESH_TYPE COMPLETE
LAST_REFRESH_DATE 2/2/2010 3:43:48 PM
STALENESS COMPILATION_ERROR
AFTER_FAST_REFRESH COMPILATION_ERROR
UNKNOWN_PREBUILT N
UNKNOWN_PLSQL_FUNC N
UNKNOWN_EXTERNAL_TABLE N
UNKNOWN_CONSIDER_FRESH N
UNKNOWN_IMPORT N
UNKNOWN_TRUSTED_FD N
COMPILE_STATE COMPILATION_ERROR
USE_NO_INDEX N
STALE_SINCE
select * from dba_snapshots t where t.name='MV_V_CDRO_DMAN_ACTUALS_445';
OWNER CM_DCP_REPORTING_OWNER
NAME MV_V_CDRO_DMAN_ACTUALS_445
TABLE_NAME MV_V_CDRO_DMAN_ACTUALS_445
MASTER_VIEW
MASTER_OWNER SYS
MASTER OBJ$
MASTER_LINK
CAN_USE_LOG NO
UPDATABLE NO
REFRESH_METHOD COMPLEX
LAST_REFRESH 2/2/2010 3:43:29 PM
ERROR 0
FR_OPERATIONS REGENERATE
CR_OPERATIONS VALID
TYPE COMPLETE
NEXT SYSDATE+10000
START_WITH 6/20/2037 3:23:27 PM
REFRESH_GROUP 4027
UPDATE_TRIG
UPDATE_LOG
QUERY <Long>
MASTER_ROLLBACK_SEG
STATUS VALID
REFRESH_MODE PERIODIC
PREBUILT NO
select * from dba_objects t where t.object_name='MV_V_CDRO_DMAN_ACTUALS_445' and t.object_type='MATERIALIZED VIEW';
OWNER CM_DCP_REPORTING_OWNER
OBJECT_NAME MV_V_CDRO_DMAN_ACTUALS_445
SUBOBJECT_NAME
OBJECT_ID 196350
DATA_OBJECT_ID
OBJECT_TYPE MATERIALIZED VIEW
CREATED 2/2/2010 3:43:48 PM
LAST_DDL_TIME 2/2/2010 3:43:48 PM
TIMESTAMP 2010-02-02:15:43:48
STATUS INVALID
TEMPORARY N
GENERATED N
SECONDARY N
select * from dba_objects t where t.object_name='V_CDRO_DMAN_ACTUALS_445' and t.object_type='VIEW';
OWNER CM_DCP_REPORTING_OWNER
OBJECT_NAME V_CDRO_DMAN_ACTUALS_445
SUBOBJECT_NAME
OBJECT_ID 186619
DATA_OBJECT_ID
OBJECT_TYPE VIEW
CREATED 1/30/2010 6:46:43 PM
LAST_DDL_TIME 2/2/2010 3:19:11 PM
TIMESTAMP 2010-01-30:18:46:43
STATUS VALID
TEMPORARY N
GENERATED N
SECONDARY N
Could anyone help me on this why the status of materialized view is invalid inspite of proper sql on which it built.
why the status of mview in dba_mviews and dba_snapshot is difference?
Any help really apprecaited
Thanks in Advance
|
|
|
|
Re: Problem with Materialized View compile state [message #441750 is a reply to message #441726] |
Wed, 03 February 2010 00:28  |
vikram1780
Messages: 222 Registered: January 2007 Location: Bangalore
|
Senior Member |
|
|
Thanks for your reply.
Could you please go through explanation and code pasted.
Yes it has given compilation error. But it was successfully refreshing without any issues.
The view is valid on which the materialized view is created.
You can see the query which is checking for status of view.
Materialized view created successfully, refresing successfully only the status is INVALID in dba_mviews.
Tried with complete refresh. Even though the same status.
Thanks in advance
|
|
|