Recreating mat. view in Oracle failing due to dba_summaries entry, how to prevent?

Question

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..

Answer 1

If drop materialized view is leaving an entry in dba_summaries, then you are hitting a bug - perhaps the one described here

As you do not have a support contract, you can't raise a TAR. However the first question Oracle Support would ask you is whether you are running the latest patchset, 11.2.0.3 - I suggest you consider this option first as the root problem is an Oracle bug. Note that "it is a full installation"

If that doesn't solve the problem, you'll need to try and work around it because as far as I know there is no alternative to sysdba for drop summary. The linked article suggested dropping indexes before dropping the MV which would perhaps be a good place to start.

Any pointers to documentation and if somebody can explain what the logic behind DBA_SUMMARIES

dba_summaries and drop summary are undocumented.

Answer 2

I had the same problem. Found in Metalink and it worked for me:

• Create Materialized View Results in ORA-955 But Drop Materialized View Does Not Succeed Either (Doc ID 1358255.1)
• connect /as sysdba
• drop summary owner.mv_name;
• connect owner/pwd
• create materialized view ...;

It will work this time.

Thanks, M.

Answer 3

In one previous experience, in addition to using DROP SUMMARY, I also had to use DROP TABLE <MAT_VIEW>.

After using DROP SUMMARY, I did not see the mat view name when querying DBA_SUMMARIES, but I still saw it in the DBA_OBJECTS. I had to also use DROP TABLE to completely drop it. This resolved all of my issues in the aforementioned blog post.