How to resolve ORA-14117: partition resides in offlined tablespace

on
SQL> drop tablespace SUMM_DATA07;
drop tablespace SUMM_DATA07
                *
ERROR at line 1:
ORA-14404: partitioned table contains partitions in a different tablespace appears.

SQL> alter table CCDAWORK.S_ACCT_FIX drop partition P6;
alter table CCDAWORK.S_ACCT_FIX drop partition P6
                                               *
ERROR at line 1:
ORA-14117: partition resides in offlined tablespace

这样就处于了一个很尴尬的境地,面对着一个无法online的表空间,既不能将这个表空间drop了重建,也不能将这个表空间中包含的表分区删除掉。

解决方法是:通过exchange partition的方法,将位于offline表空间中的表分区置换到一个普通表中,这是数据字典的操作,不会检查表空间是否处于online状态,然后再将普通表删除,之后就可以将表空间删除了。

1. 将离线表空间中的所有表分区都选择出来,对于subpartition也同理操作。

select owner,segment_name, partition_name,segment_type
from dba_segments
where segment_type='TABLE PARTITION' and 
tablespace_name in(list of the offlined tablespces in upper case)
order by segment_name; 

2. 对于所有的表分区循环执行2.1到2.3的步骤。
2.1. 创建一个临时表,表结构同分区表一样,OWNER.SEGMENT_NAME都是从第一步的SQL中获得的。

create table TEMP as select * from OWNER.SEGMENT_NAME 
where 1=2; 

2.2. 置换表分区。

alter table OWNER.SEGMENT_NAME 
exchange partition PARTITION_NAME with table TEMP; 

2.3. 删除临时表。

drop table TEMP;

3. 当所有的表分区都已经置换出来以后,就可以删除表空间了。

6 Comments Add yours

  1. 圆梦之旅 says:

    辛苦辛苦,我在旁边都、、、

  2. Kamus says:

    @圆梦之旅
    😀 😀
    也挺有意思的。

  3. lunar says:

    回头给我找点活干,挣点外块,呵呵:)

    还有呀,你着这个“submit comment”特别不好 ,tuning一下吧 😀

  4. 村民 says:

    不知为何,岩岩的空间打不开了。

  5. Kamus says:

    @村民
    确实是。。再等等,据说微软msn space这几天是有些问题

  6. 村民 says:

    哦,谢谢你!

Leave a Reply to 村民 Cancel reply

Your email address will not be published. Required fields are marked *