Before dropping view, query on all_objects table. Applies to: Oracle Database - Enterprise Edition - Version 8.1.7.4 to 11.2.0.2 [Release 8.1.7 to 11.2] Oracle Database Cloud Schema Service - Version N/A and later Oracle Database Exadata Express Cloud Service - Version N/A and later 3) CASCADE CONSTRAINT Third, if a view has any constraint, you must specify the CASCADE CONSTRAINT clause to drop all referential integrity constraints that refer to primary key and unique keys in the view. SQL> DROP MATERIALIZED VIEW SPATIAL_MV_TEST1; DROP MATERIALIZED VIEW SPATIAL_MV_TEST1 * ERROR at line 1: ORA-04020: deadlock detected while trying to lock object SDODATA.SPATIAL_MV_TEST1 SQL> In either the current session or a new session when we make a second attempt to drop the materialized view the result will be a success. SQL> drop materialized view lf; Materialized view dropped. I have tried truncating the log tables and MV table, and then dropping the MV, but it's the same. And dropping them was taking a long time, as it tries to drop the data in both source and destination DB. This is a bug in Oracle. DROP MATERIALIZED VIEW LOG ON order_lines; CREATE MATERIALIZED VIEW LOG ON order_lines WITH ROWID, SEQUENCE(order_id, line_qty, total_value) INCLUDING NEW VALUES; Materialized View I tracked the drop comment and I saw that Oracle was trying to delete some tables: sys.mvref$_stmt_stats sys.mvref$_change_stats sys.mvref$_stats sys.mvref$_run_stats drop materialized view foo; Now the logical flow I am looking at is as follows: 1) Oracle Scheduler fires every thirty (30) minutes. If I try to create it, it says Name already exists. If a view is referenced by other views, materialized views, or synonyms, Oracle will mark these objects INVALID, but does not remove them. create materialized view foo parallel nologging never refresh as select * from long_running_query; and subsequently . Optimize How To Truncate Materialized View Log (Doc ID 457070.1) Last updated on NOVEMBER 03, 2019. SQL> select * From lf; no rows selected Use on prebuilt table option: These are fast refreshed from a remote database over a link from one master db. There's an entry in dba_summaries for the MV. So I decided to drop MV and I just realized that something else is also wrong because after 5 minutes, drop statement failed and even my session has lost. 2) New materialized view is generated with arbitrary name (bar) I faced similar issue when i tried to drop materialized view it says View doesn't exist. Oracle 10 Standard: I have a number of materialized views built on pre-built tables. All indexes and keys for the MV are dropped as well. ]; DROP MATERIALIZED VIEW scott.mv_acctcommon; All data in the mv table is automatically deleted as part of the dropping process. Create a table which will be used as a "target" of the materialized view's query; it'll hold data: SQL> create table lf as select * From dept where 1 = 2; Table created. Oracle Database - Enterprise Edition - Version 11.2.0.1 and later: Drop Materialized View Is Hanging With No Error Recently I had to drop a couple of large Materialized View. In Source DB it tries to purge the mview log and at destination mview itself. For real-time materialized views to work we must have materialised view logs on all the tables the materialized view is based on. Drop DROP MATERIALIZED VIEW [schema. When I try to drop either the MV itself or logs associated with constituent tables, it starts the process but never comes back. They've generally been working fine, but sometimes the view needs re-creating when a database server is bounced. I have a materialized view that's fast refreshable, joining two tables.