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

  删除含有物化视图的表空间将会因遭遇ORA-23515错误而无法删除，这是一个事实！提醒在此。

1.初始化环境
1）创建表空间TBS_SEC_D
sec@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/sec
Connected.
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          1
         2          2          2
         2          2          2

4）创建物化视图日志
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_D
T                              TABLE              TBS_SEC_D

sec@ora10g> conn / as sysdba
Connected.
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_D
I_SNAP$_MV_T                   INDEX              TBS_SEC_D
MV_T                           TABLE              TBS_SEC_D
T                              TABLE              TBS_SEC_D

2.测试删除包含物化视图的表空间TBS_SEC_D
sec@ora10g> conn / as sysdba
Connected.
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 tablespace

ORA-23515报错信息已经呈现在我们面前。这个错误内容提示的信息描述得很清楚：被删除的表空间中含有物化视图（本例中的“MV_T”）或物化视图的索引（本例中的“I_SNAP$_MV_T”）。

3.处理方法
既然知道导致系统报错的原因是表空间上含有物化视图和物化视图索引，因此处理该问题仅需将表空间上的物化视图以及物化视图索引删除掉即可。
基于本例的处理方法如下。
sys@ora10g> conn sec/sec
Connected.
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_D
T                              TABLE              TBS_SEC_D

sec@ora10g> conn / as sysdba
Connected.
sys@ora10g> drop tablespace TBS_SEC_D including contents and datafiles;

Tablespace dropped.

此时表空间便可以删除成功。

4.小结
  本文给出了一种表空间无法删除的特例。物化视图作为Oracle的优化手段，关于物化视图的注意事项我们需要重点了解。

Good luck.

secooler
11.06.03

-- The End --

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/519536/viewspace-697184/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/519536/viewspace-697184/