oracle彻底删除物化视图,无法删除带有物化视图的表空间

SYS@PROD5> create tablespace test datafile '/u01/app/oracle/oradata/PROD5/test01.dbf' size 200m autoextend on;

Tablespace created.

SYS@PROD5> create user test identified by test default tablespace test;

User created.

SYS@PROD5> grant dba to test;

Grant succeeded.

SYS@PROD5> conn test/test

Connected.

TEST@PROD5>

TEST@PROD5> create table t(x number, y number, z number);

Table created.

TEST@PROD5> insert into t values(1,1,1);

1 row created.

TEST@PROD5> insert into t values(2,2,2);

1 row created.

TEST@PROD5> insert into t values(3,3,3);

1 row created.

TEST@PROD5> commit;

Commit complete.

TEST@PROD5> select * from t;

X    Y       Z

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

1    1       1

2    2       2

3    3       3

TEST@PROD5> create materialized view log on t with sequence, rowid(x,y,z) including new values;

Materialized view log created.

TEST@PROD5> create materialized view m_t build immediate refresh fast on commit

2  enable query rewrite as select x,y,z from t group by x,y,z;

Materialized view created.

TEST@PROD5> select segment_name,segment_type,tablespace_name from user_segments;

SEGMENT_NAME SEGMENT_TYPE    TABLESPACE_NAME

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

I_MLOG$_T INDEX    TEST

I_SNAP$_M_T INDEX    TEST

MLOG$_T TABLE    TEST

M_T TABLE    TEST

T TABLE    TEST

TEST@PROD5>

TEST@PROD5> col SEGMENT_NAME for a20

TEST@PROD5> /

SEGMENT_NAME     SEGMENT_TYPETABLESPACE_NAME

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

I_MLOG$_T     INDEXTEST

I_SNAP$_M_T     INDEXTEST

MLOG$_T     TABLETEST

M_T     TABLETEST

T     TABLETEST

TEST@PROD5> drop tablespace test including contents and datafiles;

drop tablespace test including contents and datafiles

*

ERROR at line 1:

ORA-23515: materialized views and/or their indices exist in the tablespace

TEST@PROD5> drop materialized view m_t;

Materialized view dropped.

TEST@PROD5> select segment_name,segment_type,tablespace_name from user_segments;

SEGMENT_NAME     SEGMENT_TYPETABLESPACE_NAME

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

I_MLOG$_T     INDEXTEST

MLOG$_T     TABLETEST

T     TABLETEST

TEST@PROD5> drop tablespace test including contents and datafiles;

Tablespace dropped.

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