oracle11g Rename user causes materialized view invalidation processing

In the previous article, the issue of renaming the database caused the materialized view to fail in the schema. On the surface, the materialized view is deleted and deleted, which of course cannot be rebuilt. The following are the experimental procedures:

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.

Now that you've removed the materialized view, try rebuilding it to see

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

Although the command to delete the materialized view above has been executed successfully, please see there is a record in dba_objects, that is, there is no real deletion.

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

The problem is because there is summary information about the materialized view

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; <------------that's the key to solving the problem.

Next verify if it's clear

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.

So far, it has been settled successfully.

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, it took to fully refresh the summary ';
Dba_summaries. Increfreshtim is ' The time, 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 are unusable, the build was deferred ';
Dba_summaries. Restricted_syntax is ' This summary contains restrictive SYNTAX ';
Dba_summaries. Inc_refreshable is ' This summary was not a restricted from being incrementally refreshed ';

oracle11g Rename user causes materialized view invalidation processing