Oracle Database Discussions

SQL> drop materialized view MV_SONG;

Materialized view dropped.

SQL> drop materialized view MV_SONG;

Materialized view dropped.

SQL> drop materialized view MV_SONG;

Materialized view dropped.

Very weird, I need to drop this MV in order to create a new one.  I am not able to drop it.  Says Materialized view dropped, but it is not dropped.

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

CORE    11.2.0.3.0      Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

SQL> select object_name, object_type, created,status  from user_objects where object_name = 'MV_SONG';

OBJECT_NAM OBJECT_TYPE         CREATED   STATUS

---------- ------------------- --------- -------

MV_SONG    MATERIALIZED VIEW   29-JUL-11 INVALID

Here is more info:

SQL> drop materialized view mv_song;

Materialized view dropped.

SQL> select * from mv_song;

select * from mv_song

              *

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> create materialized view MV_SONG refresh force on demand as select sysdate from dual;

create materialized view MV_SONG refresh force on demand as select sysdate from dual

           *

ERROR at line 1:

ORA-00955: name is already used by an existing object

I queried oracle support, can't seem to find an article on the subject.

Need advise.

Thanks,

Steeve