oracle无法删除物化视图日志,【MV】删除含有物化视图的表空间会遇到ORA-23515错误而无法删除...

删除含有物化视图的表空间将会因遭遇ORA-23515错误而无法删除，这是一个事实！提醒在此。1.初始化环境1)创建表空间TBS_SEC_Dsec@ora10g> create tablespace TBS_SEC_D datafile '/oracle/ora10gR2/oradata/ora10g/tbs_sec_d_01.dbf' size 10m;Tablespace created.2)创建测试用户SEC并授权sys@ora10g> create user sec identified by sec default tablespace TBS_SEC_D;User created.sys@ora10g> grant connect,resource,create materialized view to sec;Grant succeeded.3)创建物化视图基表T并初始化数据sys@ora10g> conn sec/secConnected.sec@ora10g> create table t (x int, y int, z int) tablespace TBS_SEC_D;Table created.sec@ora10g> insert into t values (1,1,1);sec@ora10g> insert into t values (2,2,2);sec@ora10g> insert into t values (2,2,2);sec@ora10g> commit;sec@ora10g> select * from t;X          Y          Z---------- ---------- ----------1          1          12          2          22          2          24)创建物化视图日志sec@ora10g> create materialized view log on t with sequence, rowid (x,y,z) including new values;Materialized view log created.特别说明：当在表空间上仅创建了物化视图日志的情况下，表空间允许被删除，测试如下。sec@ora10g> select segment_name,segment_type,tablespace_name from user_segments;SEGMENT_NAME                   SEGMENT_TYPE       TABLESPACE_NAME------------------------------ ------------------ ---------------MLOG$_T                        TABLE              TBS_SEC_DT                              TABLE              TBS_SEC_Dsec@ora10g> conn / as sysdbaConnected.sys@ora10g> drop tablespace TBS_SEC_D including contents and datafiles;Tablespace dropped.5)创建物化视图sec@ora10g> create materialized view mv_t build immediate refresh fast on commit enable query rewrite as select x,y,z,count(*) from t group by x,y,z;Materialized view created.sec@ora10g> select segment_name,segment_type,tablespace_name from user_segments;SEGMENT_NAME                   SEGMENT_TYPE       TABLESPACE_NAME------------------------------ ------------------ ---------------MLOG$_T                        TABLE              TBS_SEC_DI_SNAP$_MV_T                   INDEX              TBS_SEC_DMV_T                           TABLE              TBS_SEC_DT                              TABLE              TBS_SEC_D2.测试删除包含物化视图的表空间TBS_SEC_Dsec@ora10g> conn / as sysdbaConnected.sys@ora10g> drop tablespace TBS_SEC_D including contents and datafiles;drop tablespace TBS_SEC_D including contents and datafiles*ERROR at line 1:ORA-23515: materialized views and/or their indices exist in the tablespaceORA-23515报错信息已经呈现在我们面前。这个错误内容提示的信息描述得很清楚：被删除的表空间中含有物化视图(本例中的“MV_T”)或物化视图的索引(本例中的“I_SNAP$_MV_T”)。3.处理方法既然知道导致系统报错的原因是表空间上含有物化视图和物化视图索引，因此处理该问题仅需将表空间上的物化视图以及物化视图索引删除掉即可。基于本例的处理方法如下。sys@ora10g> conn sec/secConnected.sec@ora10g> drop materialized view mv_t;Materialized view dropped.sec@ora10g> select segment_name,segment_type,tablespace_name from user_segments;SEGMENT_NAME                   SEGMENT_TYPE       TABLESPACE_NAME------------------------------ ------------------ ---------------MLOG$_T                        TABLE              TBS_SEC_DT                              TABLE              TBS_SEC_Dsec@ora10g> conn / as sysdbaConnected.sys@ora10g> drop tablespace TBS_SEC_D including contents and datafiles;Tablespace dropped.此时表空间便可以删除成功。4.小结本文给出了一种表空间无法删除的特例。物化视图作为Oracle的优化手段，关于物化视图的注意事项我们需要重点了解。Good luck.secooler11.06.03-- The End --