ORA-04020 When Dropping Materialized View with a Spatial Index

While upgrading our GIS development database from 11gR1 to 11gR2 we found that we were getting deadlocks when dropping materialized views that had spatial indexes. The deadlock always occurred on the first attempt to drop the view and any further attempt to drop the view would report success. However; an attempt to recreate the materialized view would result in an ORA-00955.

This post will detail the errors and the work around we have found. We have been able to recreate this problem using Oracle 11gR2 on Linux, Windows and AIX systems. Oracle Support has created Bug 11870418 – DROP MATERIALIZED VIEW W/SPATIAL INDEX=ORA-4020; CREATE MATERIALIZED VIEW=ORA-95 to address this issue.

First we need to insert metadata into USER_SDO_GEOM_METADATA for our test materialized view.

SQL> INSERT INTO USER_SDO_GEOM_METADATA(TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
  2  values('SPATIAL_MV_TEST1','GEOM',SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X',-180,180,0.05), SDO_DIM_ELEMENT('Y',-90,90,0.05)),4269);

1 row created.

SQL> COMMIT;

Commit complete.

SQL>

Next we create the materialized view that contains a spatial data type and the spatial index on that column.

SQL> CREATE MATERIALIZED VIEW SPATIAL_MV_TEST1 AS
  2  SELECT ROWNUM AS ID, SDO_GEOMETRY(2001,4269,NULL,SDO_ELEM_INFO_ARRAY(1,1,1),SDO_ORDINATE_ARRAY(-82,37)) AS GEOM FROM DUAL;

Materialized view created.

SQL> CREATE INDEX SIDX_SMVT1 ON SPATIAL_MV_TEST1(GEOM) INDEXTYPE IS MDSYS.SPATIAL_INDEX;

Index created.

SQL>

Now we drop the materialized view and get the ORA-04020 error.

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 SPATIAL_MV_TEST1;

Materialized view dropped.

SQL>

If we attempt to re-create the materialized view an ORA-00955 error is raised.

SQL> CREATE MATERIALIZED VIEW SPATIAL_MV_TEST1 AS
  2  SELECT ROWNUM AS ID, SDO_GEOMETRY(2001,4269,NULL,SDO_ELEM_INFO_ARRAY(1,1,1),SDO_ORDINATE_ARRAY(-82,37)) AS GEOM FROM DUAL;
SELECT ROWNUM AS ID, SDO_GEOMETRY(2001,4269,NULL,SDO_ELEM_INFO_ARRAY(1,1,1),SDO_ORDINATE_ARRAY(-82,37)) AS GEOM FROM DUAL
                     *
ERROR at line 2:
ORA-00955: name is already used by an existing object


SQL>

A quick inspection of DBA_OBJECTS will show that drop did not complete successfully as both the table and materialized view are still present.

SQL> select object_name, object_id, object_type, status      
  2  from dba_objects
  3  where object_name = 'SPATIAL_MV_TEST1' and owner = 'SDODATA';

OBJECT_NAME                       OBJECT_ID  OBJECT_TYPE         STATUS
--------------------------------- ---------- ------------------- -------
SPATIAL_MV_TEST1                       74999 TABLE               VALID
SPATIAL_MV_TEST1                       75004 MATERIALIZED VIEW   INVALID


SQL>

We are going to have to do some cleanup that was missed when ORA-04020 was raised during the original drop. First we will start with the SPATIAL_MV_TEST1 table.

SQL> drop table spatial_mv_test1;

Table dropped.

SQL>

What about the index, SIDX_SMVT1, created on the view, was it dropped? Looking at DBA_INDEXES we see that SIDX_SMVT1 was dropped.

SQL> select index_name               
  2  from dba_indexes 
  3  where table_name = 'SPATIAL_MV_TEST1' and owner = 'SDODATA';

no rows selected

SQL>

After dropping the table all that remains in DBA_OBJECTS is the materialized view that has a status of invalid.

SQL> select object_name, object_id, object_type, status      
  2  from dba_objects
  3  where object_name = 'SPATIAL_MV_TEST1' and owner = 'SDODATA';

OBJECT_NAME                       OBJECT_ID  OBJECT_TYPE         STATUS
--------------------------------- ---------- ------------------- -------
SPATIAL_MV_TEST1                       75004 MATERIALIZED VIEW   INVALID


SQL>

Since the view is invalid you might think compiling the view would resolve the problem.

SQL> alter materialized view spatial_mv_test1 compile;
alter materialized view spatial_mv_test1 compile
*
ERROR at line 1:
ORA-12003: materialized view "SDODATA"."SPATIAL_MV_TEST1" does not exist


SQL>                       

An ORA-12003 is raised because the materialized view does not exist in direct contradiction to query against DBA_OBJECTS above. The problem is that there is still a summary remaining that has the same OBJECT_ID. As a user with access to the SYS $ tables execute a query against SYS.SUM$ similar to the one below.

SQL> select containerobj#, containertype, containernam
  2  from sys.sum$
  3  where obj# = 75004;

CONTAINEROBJ# CONTAINERTYPE CONTAINERNAM
------------- ------------- ------------------------------
        74999             2 SPATIAL_MV_TEST1

SQL> 

Here we see that a summary does exist for the OBJECT_ID associated with the materialized view. Note that the value of SYS.SUM$.CONTAINEROBJ$ is the OBJECT_ID of the table SPATIAL_MV_TEST1 dropped earlier.

In order to complete the removal of the materialized view the summary has to be removed. While it would be possible to find the entries in the data dictionary and remove them, to do so without the guidance of Oracle Support is not supported and could leave your database in an unusable state.
Instead of editing the data dictionary the summary can be removed using the command DROP SUMMARY . as SYSDBA.

SQL> drop summary sdodata.spatial_mv_test1;

Summary dropped.

SQL> 

After dropping the summary we now see that the materialized view no longer appears in DBA_OBJECTS.

SQL> select object_name, object_id, object_type, status      
  2  from dba_objects
  3  where object_name = 'SPATIAL_MV_TEST1' and owner = 'SDODATA';

no rows selected

SQL>

The materialized view and its spatial index can now be re-created.

SQL> CREATE MATERIALIZED VIEW SPATIAL_MV_TEST1 AS
  2  SELECT ROWNUM AS ID, SDO_GEOMETRY(2001,4269,NULL,SDO_ELEM_INFO_ARRAY(1,1,1),SDO_ORDINATE_ARRAY(-82,37)) AS GEOM FROM DUAL;

Materialized view created.

SQL> CREATE INDEX SIDX_SMVT1 ON SPATIAL_MV_TEST1(GEOM) INDEXTYPE IS MDSYS.SPATIAL_INDEX;

Index created.

SQL>

In our investigation of this issue we found that the deadlock does not occur if the spatial index is dropped before the materialized view.

SQL> drop index sidx_smvt1;

Index dropped.

SQL> drop materialized view spatial_mv_test1;

Materialized view dropped.

SQL>

Until a patch is provided that fixes this bug you should consider changing your process to drop the spatial index first.