After DML on the Master Table(s) of Local Materialized View, USER_MVIEWS.COMPILE_STATE becomes 'NEEDS_COMPILE' and USER_OBJECTS.STATUS becomes 'INVALID' (Doc ID 264036.1)

APPLIES TO:

Oracle Database - Enterprise Edition - Version 9.0.1.0 and later

Information in this document applies to any platform.

SYMPTOMS

After executing a DML statement on base table(s) of MV that is on the same database as its master table(s) (i.e. a local MV), USER_OBJECTS.STATUS becomes INVALID for the MV.  Also, USER_MVIEWS.COMPILE_STATE shows status "NEEDS_COMPILE'.

Example :

SQL> select object_name, object_type, status from user_objects

2 where object_type = 'MATERIALIZED VIEW';

Output :

OBJECT_NAME                    OBJECT_TYPE        STATUS

------------------------------ ------------------ -------

EMP_MV_PK                      MATERIALIZED VIEW  VALID

SQL> select mview_name, compile_state from user_mviews;

Output :

MVIEW_NAME                     COMPILE_STATE

------------------------------ -------------------

EMP_MV_PK                      VALID

SQL> update emp set empno = empno where empno = 605;

SQL> commit;

SQL> select object_name, object_type, status from user_objects

2 where object_type = 'MATERIALIZED VIEW';

Output :

OBJECT_NAME                    OBJECT_TYPE        STATUS

------------------------------ ------------------ -------

EMP_MV_PK                      MATERIALIZED VIEW  INVALID

SQL> select mview_name, compile_state from user_mviews;

Output :

MVIEW_NAME                      COMPILE_STATE

------------------------------ -------------------

EMP_MV_PK                       NEEDS_COMPILE

CHANGES

DML was executed and committed on master table of the MV since the MV was last refreshed.

CAUSE

This is expected behavior. Dependencies related to MVs are automatically maintained to ensure correct operation. When an MV is created, the materialized view depends on the master tables referenced in its definition. Any DML operation, such as an INSERT, or DELETE, UPDATE, or DDL operation on any dependency in the materialized view will cause it to become invalid.

SOLUTION

Though the status is INVALID, the MV can still be queried.  However, the query on MV will not return the latest data in master table unless the MV is refreshed.

Example :

SQL> select object_name, object_type, status from user_objects

2 where object_type = 'MATERIALIZED VIEW';

Output :

OBJECT_NAME                    OBJECT_TYPE        STATUS

------------------------------ ------------------ -------

EMP_MV_PK                      MATERIALIZED VIEW  INVALID

SQL> select mview_name, compile_state from user_mviews;

Output :

MVIEW_NAME                     COMPILE_STATE

------------------------------ -------------------

EMP_MV_PK                      NEEDS_COMPILE

SQL> select count(*) from emp_mv_pk;

Output :

COUNT(*)

----------

2686976

- On next refresh, the status becomes VALID.

Example :

SQL> execute dbms_mview.refresh('emp_mv_pk');

SQL> select object_name, object_type, status from user_objects

2 where object_type = 'MATERIALIZED VIEW';

Output :

OBJECT_NAME                    OBJECT_TYPE        STATUS

------------------------------ ------------------ -------

EMP_MV_PK                      MATERIALIZED VIEW  VALID

SQL> select mview_name, compile_state from user_mviews;

Output :

MVIEW_NAME                     COMPILE_STATE

------------------------------ -------------------

EMP_MV_PK                      VALID

"REFRESH ON COMMIT" MVs are the exception to this behavior because they are refreshed at the same time of DML.

Dictionary

SCAN addresses - Single Client Access Name addresses

CSS - Cluster Synchronization Services
CRS - Cluster Ready Services
EVM - Event Manager
ONS - Oracle Notification Service
FAN - Fast Application Notification
FCF - Fast Connection Failover
GSD - Global Services Daemon
GCS - Global Cache Service

ANSWERS TO "WHY ARE MY JOBS NOT RUNNING ?"

http://forums.oracle.com/forums/thread.jspa?threadID=646581

This is one of the most common Scheduler questions asked. 
Here we list some of the common problems and their solutions.

1) job_queue_processes may be too low (this is the most common problem)
The value of job_queue_processes limits the total number of dbms_scheduler and dbms_job jobs that can be running at a given time.

To check whether this is the case check the current value of job_queue_processes with


SQL> select value from v$parameter where name='job_queue_processes';


Then check the number of running jobs


SQL> select count(*) from dba_scheduler_running_jobs;
SQL> select count(*) from dba_jobs_running;

If this is the problem you can increase the parameter using


SQL> alter system set job_queue_processes=1000;

Turn off the DBMS Scheduler

Turn off the DBMS Scheduler by running (for version 10.1+ RDBMS) the following command:

SQL> exec dbms_scheduler.set_scheduler_attribute ('SCHEDULER_DISABLED','TRUE');

How to check deprecated parameters in Oracle 11g

SQL> SELECT name FROM v$parameter WHERE isdeprecated = 'TRUE'

  2* order by name;

NAME

-------------------------------------------------------------------------

active_instance_count

background_dump_dest

buffer_pool_keep

buffer_pool_recycle

commit_write

cursor_space_for_time

fast_start_io_target

global_context_pool_size

instance_groups

lock_name_space

log_archive_local_first

log_archive_start

max_enabled_roles

parallel_automatic_tuning

parallel_io_cap_enabled

parallel_server

parallel_server_instances

plsql_debug

plsql_v2_compatibility

remote_os_authent

resource_manager_cpu_allocation

serial_reuse

sql_trace

standby_archive_dest

user_dump_dest

25 rows selected.

What’s new in Oracle 11g R2 – DEFERRED SEGMENT CREATION

http://jiri.wordpress.com/2010/02/25/what’s-new-in-oracle-11g-r2-–-deferred-segment-creation/

This is really small but very useful feature. It was published in ASK TOM (please read it, it’s very interesting as most of Tom’s posts), Tom did not mention the exclusions, so if you follow his samples and create tables in SYSTEM schema or add partitions, you can be surprised either with the fact that the initial segment was created or if you try to force in on table DDL level with error ORA-14223: Deferred segment creation is not supported for this table