在升级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:
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.
Note: You need to log in as the task owner in order to delete the task otherwise you may get this error:
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