Home » SQL & PL/SQL » SQL & PL/SQL » Problem with Materialized View compile state
Problem with Materialized View compile state [message #441723] Tue, 02 February 2010 21:41 Go to next message
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 #441726 is a reply to message #441723] Tue, 02 February 2010 22:21 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Quote:
COMPILE_STATE COMPILATION_ERROR

Take a look at the Note 264036.1.
see this too
Why Mview invalid.
Please read the complete thread.

sriram Smile
Re: Problem with Materialized View compile state [message #441750 is a reply to message #441726] Wed, 03 February 2010 00:28 Go to previous message
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
Previous Topic: Union
Next Topic: IMPLICIT CONVERSION
Goto Forum:
  


Current Time: Sat Oct 01 16:13:21 CDT 2016

Total time taken to generate the page: 0.08009 seconds