http://space.itpub.net/10834762/viewspace-507991
今天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/23590362/viewspace-662130/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/23590362/viewspace-662130/