Oracle11G删除物化视图,oracle11g rename user导致物化视图失效的处理

在上一篇文章中，已经点到了数据库改名时，引起该schema下物化视图会失效的问题。从表面上看，该物化视图是删也删不掉，那当然就无法重建了。以下是实验过程：

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SQL> conn yyf123/yyf123

SQL> select s.owner,s.object_name,s.object_type,status from dba_objects  s  where object_name=‘EMP‘ ;

OWNER       OBJECT_NAME     OBJECT_TYPE STATUS

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

SCOTT EMP  TABLE VALID

SCOTT       EMP     MATERIALIZED VIEW   VALID

YYF123 EMP  TABLE VALID

YYF123       EMP     MATERIALIZED VIEW   INVALID

SQL> drop table emp purge;

ERROR at line 1:

ORA-12083: must use DROP MATERIALIZED VIEW to drop "YYF123"."EMP"

SQL> drop materialized view emp;

Materialized view dropped.

既然提示已经删除了物化视图，那尝试着重建一下看看

SQL>CREATE MATERIALIZED VIEW  emp

ON PREBUILT TABLE

REFRESH FORCE ON DEMAND

START WITH sysdate  NEXT SYSDATE + 10/1442

as

SELECT * FROM [email protected]_to_scott;

*

ERROR at line 1:

ORA-00955: name is already used by an existing object

虽然上面删除物化视图的命令已经成功执行，但是请看dba_objects里面还有记录，也就是说并没有真正的删除。

select s.owner,s.object_name,s.object_type,status from dba_objects  s  where object_name=‘EMP‘ ;

OWNER       OBJECT_NAME     OBJECT_TYPE STATUS

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

YYF123       EMP     TABLE VALID

YYF123       EMP     MATERIALIZED VIEW   INVALID

问题来了，就是因为存在对该物化视图的summary信息

SQL> select t.owner,t.summary_name,t.container_owner,t.container_name from dba_summaries t where owner=‘YYF123‘ ;

OWNER       SUMMARY_NAME     CONTAINER_OWNER    CONTAINER_NAME

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

YYF123       EMP     YYF123    EMP

SQL> conn / as sysdba

SQL> drop summary yyf123.emp;    

接下来验证下是否已经清楚

SQL> select t.owner,t.summary_name,t.container_owner,t.container_name from dba_summaries t where owner=‘YYF123‘ ;

no rows selected

SQL>CREATE MATERIALIZED VIEW  emp

ON PREBUILT TABLE

REFRESH FORCE ON DEMAND

START WITH sysdate  NEXT SYSDATE + 10/1442

as

SELECT * FROM [email protected]_to_scott;

Materialized view created.

SQL> select count(*) from emp;

COUNT(*)

----------

14

SQL> exec dbms_mview.refresh(‘emp‘,‘c

PL/SQL procedure successfully completed.

到此为止，已经顺利解决。

reference:

http://dba.stackexchange.com/questions/4235/why-in-oracle-11gr2-i-cant-drop-the-materialized-view-with-the-same-user-that-c

DBA_SUMMARIES is ‘Description of the summaries accessible to dba‘;

DBA_SUMMARIES.OWNER is ‘Owner of the summary‘;

DBA_SUMMARIES.SUMMARY_NAME is ‘Name of the summary‘;

DBA_SUMMARIES.CONTAINER_OWNER is ‘Owner of the container table‘;

DBA_SUMMARIES.CONTAINER_NAME is ‘Name of the container table for this summary‘;

DBA_SUMMARIES.LAST_REFRESH_SCN is ‘The SCN of the last transaction to refresh the summary‘;

DBA_SUMMARIES.LAST_REFRESH_DATE is ‘The date of the last refresh of the summary‘;

DBA_SUMMARIES.REFRESH_METHOD is ‘User declared method of refresh for the summary‘;

DBA_SUMMARIES.SUMMARY is ‘Indicates the presence of either aggregation or a GROUP BY‘;

DBA_SUMMARIES.FULLREFRESHTIM is ‘The time that it took to fully refresh the summary‘;

DBA_SUMMARIES.INCREFRESHTIM is ‘The time that it took to incrementally refresh the summary‘;

DBA_SUMMARIES.CONTAINS_VIEWS is ‘This summary contains views in the FROM clause‘;

DBA_SUMMARIES.UNUSABLE is ‘This summary is unusable, the build was deferred‘;

DBA_SUMMARIES.RESTRICTED_SYNTAX is ‘This summary contains restrictive syntax‘;

DBA_SUMMARIES.INC_REFRESHABLE is ‘This summary is not restricted from being incrementally refreshed‘;

原文：http://www.cnblogs.com/yiyuf/p/4103915.html