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