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/,如需转载,请注明出处,否则将追究法律责任。