在升级11.2.0.3的过程中，发现system用户下面有无效物化视图？

删除system中mv$$_Materialized Views 无效对象

How To Remove Invalid MV$$_ Materialized Views in the SYSTEM Schema (文档 ID 1571344.1)

In this Document

Oracle Database - Enterprise Edition - Version 11.2.0.3 and later

---

 

APPLIES TO:

Information in this document applies to any platform.

GOAL

You want to delete Invalid Materialized Views in the System schema.

Sample Of Invalid Mviews:

SYSTEM MV$$_0DF40121 MATERIALIZED VIEW INVALID
SYSTEM MV$$_0DF40122 MATERIALIZED VIEW INVALID
SYSTEM MV$$_0DF40123 MATERIALIZED VIEW INVALID
SYSTEM MV$$_0DF40124 MATERIALIZED VIEW INVALID
SYSTEM MV$$_0DF40126 MATERIALIZED VIEW INVALID
SYSTEM MV$$_0DF40127 MATERIALIZED VIEW INVALID
SYSTEM MV$$_0DF40128 MATERIALIZED VIEW INVALID

SOLUTION

Based on the format of the Materialized View name (MV$$_)  it shows  that DBMS_ADVISOR is being used. You can query dba_ADVISOR_LOG to see if there is a task that did not complete.

Check for any task that did not complete and has a name like SQLACCESS<nnnnnnnn>

Example output:

select OWNER, TASK_ID, TASK_NAME, STATUS from DBA_ADVISOR_LOG where STATUS <> 'COMPLETED';

OWNER                             TASK_ID TASK_NAME                      STATUS
------------------------------ ---------- ------------------------------ -----------
SH                                3572 SQLACCESS14590                 FATAL ERROR

Then you can delete this ask.

Use DBMS_ADVISOR.DELETE_TASK to remove this job.

exec DBMS_ADVISOR.DELETE_TASK(:task_name); -- this will remove these MV$$_ mviews.

Note: You need to log in as the task owner in order to delete the task otherwise you may get this error:

ERROR at line 1:
ORA-13605: The specified task or object SH.SQLACCESS14590 does not exist for the current user.
ORA-06512: at "SYS.PRVT_ADVISOR", line 2365
ORA-06512: at "SYS.DBMS_ADVISOR", line 172
ORA-06512: at line 1 

For more information on how to use the DBMS_ADVISOR to tune Mviews, see Note  NOTE:249993.1.

REFERENCES

NOTE:249993.1 - Using Dbms_Advisor.Tune_Mview To Optimize Materialized Views For Fast Refresh