drop 一张3.7T的含lob的分区表

   今天drop 了一张3.7T的表,这张表为分区本身的数据只有23G,两个lob字段和lob index加起来占到3.7T,下面处理过程:

1.因为表特别大,开始尝试用以下方式来做:
 drop index ...;
truncate table ... reuse storage;
alter table ... deallocate unused keep 2000000m;
alter table ... deallocate unused keep 1500000m;
alter table ... deallocate unused keep 1000000m;
alter table ... deallocate unused keep 500000m;
alter table ... deallocate unused keep 0;
drop table ...;

结果做完truncate table ... reuse storage;之后做alter table ... deallocate unused keep 2000000m;时一直报
alter table EAI_CT_BUILD.EA_CROSS_SYS_DATA_TBL_BAK deallocate unused keep 10000M
*
ERROR at line 1:
ORA-03230: segment only contains 77 blocks of unused space above high water
mark

问了一下oracle,推荐我用下以下方法来做:

1.先truncate所有的subpartition:
truncate table EAI_CT_BUILD.EA_CROSS_SYS_DATA_TBL_BAK  subpartition **;......

2.truncate所有的partition
truncate table EAI_CT_BUILD.EA_CROSS_SYS_DATA_TBL_BAK  partition **;......

3.drop table EAI_CT_BUILD.EA_CROSS_SYS_DATA_TBL_BAK  ;

大概在truncate subpartition时花了25分钟,之后的就快了,drop table时只发了1分29秒.

 

附:

How To Efficiently Drop A Table With Many Extents
	Doc ID:	Note:68836.1	Type:	BULLETIN
	Last Revision Date:	12-JUN-2008	Status:	PUBLISHED

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

转载于:http://blog.itpub.net/10834762/viewspace-507991/