删除表空间：USERS

在drop USERS 表空间时，前后遇到了默认永久表空间问题，存在基表、索引、物化视图问题，一一解决后删除成功。

drop USERS 表空间。

SQL>drop tablespace users including contents and datafiles;
drop tablespace users including contents and datafiles
*
ERROR at line 1:
ORA-12919: Can not drop the default permanent tablespace

把默认的永久表空间改为system

SQL> alter database default tablespace system;

Database altered.

SQL> drop tablespace users including contents and datafiles
  2  ;
drop tablespace users including contents and datafiles
*
ERROR at line 1:
ORA-23515: materialized views and/or their indices exist in the tablespace

查看有哪些对象：

select owner||'.'||segment_name from dba_segments
where tablespace_name='USERS' and segment_name in
(select  distinct segment_name from  dba_segments where tablespace_name='USERS');

SCOTT.PK_DEPT
SCOTT.DEPT
SCOTT.EMP
SCOTT.PK_EMP
SCOTT.BONUS
SCOTT.SALGRADE
SCOTT.MLOG$_EMP
SCOTT.MV_EMP_PK
SCOTT.PK_EMP1
SCOTT.B
SCOTT.SYS_C005290
SCOTT.C
SCOTT.SYS_C005291
SCOTT.A
SCOTT.MLOG$_A
SCOTT.MLOG$_B
SCOTT.MLOG$_C
SCOTT.MV_ABC
SCOTT.MLOG$_MV_ABC
SCOTT.I_SNAP$_MV_MV_ABC
SCOTT.MV_CAPABILITIES_TABLE
SCOTT.MV_MV_ABC

移动表和索引（其中包括视图）

select 'alter'||
 case segment_type
    when 'TABLE' then  ' table '||owner||'.'||segment_name||' move data1;'
 when 'INDEX' then  ' index '||owner||'.'||segment_name||' rebuild tablespace data1;'
 else 'not'
 end
from dba_segments
where tablespace_name='USERS' and segment_name in
(select  distinct segment_name from  dba_segments where tablespace_name='USERS');

SQL> alter table SCOTT.MLOG$_A move data1
                               *
ERROR at line 1:
ORA-14133: ALTER TABLE MOVE cannot be combined with other operations

发现是由于少写了个tablespace

重新检查

SQL> select owner||'.'||segment_name from dba_segments
wher  2  e tablespace_name='USERS' and segment_name in
  3  (select  distinct segment_name from  dba_segments where tablespace_name='USERS');

OWNER||'.'||SEGMENT_NAME
--------------------------------------------------------------------------------
SCOTT.DEPT
SCOTT.EMP
SCOTT.BONUS
SCOTT.SALGRADE
SCOTT.MLOG$_EMP
SCOTT.MV_EMP_PK
SCOTT.B
SCOTT.C
SCOTT.A
SCOTT.MLOG$_A
SCOTT.MLOG$_B
SCOTT.MLOG$_C
SCOTT.MV_ABC
SCOTT.MLOG$_MV_ABC
SCOTT.MV_CAPABILITIES_TABLE
SCOTT.MV_MV_ABC

16 rows selected.

重写移动表和索引的语句

--这里有物化视图，是按基表来移动的。
select 'alter'||
 case segment_type
    when 'TABLE' then  ' table '||owner||'.'||segment_name||' move tablespace data1;'
 when 'INDEX' then  ' index '||owner||'.'||segment_name||' rebuild tablespace data1;'
 else 'not'
 end
from dba_segments
where tablespace_name='USERS' and segment_name in
(select  distinct segment_name from  dba_segments where tablespace_name='USERS');

再次检查有哪些对象：

SQL> select owner||'.'||segment_name from dba_segments
where table  2  space_name='USERS' and segment_name in
(  3  select  distinct segment_name from  dba_segments where tablespace_name='USERS');

no rows selected

再次尝试drop表空间：

SQL> drop tablespace users including contents and datafiles;

Tablespace dropped.

SQL>