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