在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>