I have run into the issue described here several times in our current project and I would like to understand how it happens and how to prevent it in the future.

• What is the meaning of DBA_SUMMARIES and why are entries created there for the MVs of a user?
• Is there a way to remove the entries there as a standard user, so that I can recreate the MV without running into above problem?

The main issue is that SYSDBA privileges are required to remove the conflicting summary. Since we do not have a SYSDBA account on the target instance, we cannot recreate our MVs without having someone on the DB support team step in.

Any pointers to documentation and if somebody can explain what the logic behind DBA_SUMMARIES is and how I can prevent this issue from happening, that would be great.

The Oracle version on which we observed this is 11.2.0.1.0 (on 64bit Linux), we do not have a support contract and therefore no access to Oracle support.

Update / Solution

Thanks to Jack's answer below, I have been able to circumvent the issue, since it seems like we're really hitting the bug he mentioned. When I first drop all indexes on the MV, I do not get the error message and am able to recreate the MV as intended (it is not showing up in USER_OBJECTS anymore after having been dropped).

This is how I am doing it now (snipped for brevity):

SET serveroutput ON
SET echo ON

DECLARE
  CURSOR mv_indexes
  IS
    SELECT 'DROP INDEX '
      || index_name AS stmt
    FROM user_indexes
    WHERE table_name = 'MV_NAME'
    AND table_owner  = 'USER';
BEGIN
  FOR ix IN mv_indexes
  LOOP
    dbms_output.put_line('Executing: ' || ix.stmt);
    EXECUTE immediate ix.stmt;
  END LOOP;
END;
/

DROP materialized VIEW MV_NAME;

SELECT * FROM user_objects WHERE object_name = 'MV_NAME';

CREATE materialized VIEW MV_NAME ..

CREATE INDEX ix_someindex ON MV_NAME (..);
CREATE INDEX..

I have run into the issue described here several times in our current project and I would like to understand how it happens and how to prevent it in the future.

• What is the meaning of DBA_SUMMARIES and why are entries created there for the MVs of a user?
• Is there a way to remove the entries there as a standard user, so that I can recreate the MV without running into above problem?

The main issue is that SYSDBA privileges are required to remove the conflicting summary. Since we do not have a SYSDBA account on the target instance, we cannot recreate our MVs without having someone on the DB support team step in.

Any pointers to documentation and if somebody can explain what the logic behind DBA_SUMMARIES is and how I can prevent this issue from happening, that would be great.

The Oracle version on which we observed this is 11.2.0.1.0 (on 64bit Linux), we do not have a support contract and therefore no access to Oracle support.

Update / Solution

Thanks to Jack's answer below, I have been able to circumvent the issue, since it seems like we're really hitting the bug he mentioned. When I first drop all indexes on the MV, I do not get the error message and am able to recreate the MV as intended (it is not showing up in USER_OBJECTS anymore after having been dropped).

This is how I am doing it now (snipped for brevity):

SET serveroutput ON
SET echo ON

DECLARE
  CURSOR mv_indexes
  IS
    SELECT 'DROP INDEX '
      || index_name AS stmt
    FROM user_indexes
    WHERE table_name = 'MV_NAME'
    AND table_owner  = 'USER';
BEGIN
  FOR ix IN mv_indexes
  LOOP
    dbms_output.put_line('Executing: ' || ix.stmt);
    EXECUTE immediate ix.stmt;
  END LOOP;
END;
/

DROP materialized VIEW MV_NAME;

SELECT * FROM user_objects WHERE object_name = 'MV_NAME';

CREATE materialized VIEW MV_NAME ..

CREATE INDEX ix_someindex ON MV_NAME (..);
CREATE INDEX..

I have run into the issue described here several times in our current project and I would like to understand how it happens and how to prevent it in the future.

• What is the meaning of DBA_SUMMARIES and why are entries created there for the MVs of a user?
• Is there a way to remove the entries there as a standard user, so that I can recreate the MV without running into above problem?

The main issue is that SYSDBA privileges are required to remove the conflicting summary. Since we do not have a SYSDBA account on the target instance, we cannot recreate our MVs without having someone on the DB support team step in.

Any pointers to documentation and if somebody can explain what the logic behind DBA_SUMMARIES is and how I can prevent this issue from happening, that would be great.

The Oracle version on which we observed this is 11.2.0.1.0 (on 64bit Linux), we do not have a support contract and therefore no access to Oracle support.

I have run into the issue described here several times in our current project and I would like to understand how it happens and how to prevent it in the future.

• What is the meaning of DBA_SUMMARIES and why are entries created there for the MVs of a user?
• Is there a way to remove the entries there as a standard user, so that I can recreate the MV without running into above problem?

The main issue is that SYSDBA privileges are required to remove the conflicting summary. Since we do not have a SYSDBA account on the target instance, we cannot recreate our MVs without having someone on the DB support team step in.

Any pointers to documentation and if somebody can explain what the logic behind DBA_SUMMARIES is and how I can prevent this issue from happening, that would be great.

The Oracle version on which we observed this is 11.2.0.1.0 (on 64bit Linux), we do not have a support contract and therefore no access to Oracle support.

Update / Solution

Thanks to Jack's answer below, I have been able to circumvent the issue, since it seems like we're really hitting the bug he mentioned. When I first drop all indexes on the MV, I do not get the error message and am able to recreate the MV as intended (it is not showing up in USER_OBJECTS anymore after having been dropped).

This is how I am doing it now (snipped for brevity):

SET serveroutput ON
SET echo ON

DECLARE
  CURSOR mv_indexes
  IS
    SELECT 'DROP INDEX '
      || index_name AS stmt
    FROM user_indexes
    WHERE table_name = 'MV_NAME'
    AND table_owner  = 'USER';
BEGIN
  FOR ix IN mv_indexes
  LOOP
    dbms_output.put_line('Executing: ' || ix.stmt);
    EXECUTE immediate ix.stmt;
  END LOOP;
END;
/

DROP materialized VIEW MV_NAME;

SELECT * FROM user_objects WHERE object_name = 'MV_NAME';

CREATE materialized VIEW MV_NAME ..

CREATE INDEX ix_someindex ON MV_NAME (..);
CREATE INDEX..