dbms_mview.unregister_mview fails with ORA-12020 (Doc ID 1631030.1)

Last updated on JULY 06, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.4 and later
Information in this document applies to any platform.

Symptoms

 

1. The materialized view does not exist at the materialized view site (OR the materialized view site might not exist any more.)

SQL>select * from user_mviews where mview_name='VM1'

no rows selected

SQL>Select object_id, owner,object_name, object_type from dba_objects where object_name ='VT1VERW';
OBJECT_ID OWNER OBJECT_NAME OBJECT_TYPE
---------- ------------------------------ ---------------------------------------- -------------------
34952 TEST VM1 TABLE

NOTE:Please note that the object_type,it clearly shows that the materialized view does not exist.

 


2. dbms_mview.unregister_mview fails with ORA-12020

>>The following query returns rows for the corresponding materialized view

SQL>select * from sys.reg_snap$ where SNAPNAME=’VM1’;


SOWNER SNAPNAME SNAPSITE SNAPSHOT_ID FLAG REP_TYPE COMMENT$ QUERY_TXT
------------------------------ ------------------------------ -------------------------- ---- ----------- ---------- ---------- ------------------------------ ------------------------------
TEST VM1 TEST1 549 2097253 2 SELECT...

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms