不说了，清理1000个interval分区-凯发app官方网站

测试表
create table "test"."tmp_part_day2"
( "id" number(20,0) not null enable,
"name" varchar2(1000),
"memo" varchar2(1000),
"create_time" date
) pctfree 10 pctused 40 initrans 1 maxtrans 255
storage(
buffer_pool default flash_cache default cell_flash_cache default)
tablespace "users"
partition by range ("create_time") interval (numtodsinterval(1, 'day'))
(partition "sys_p928" values less than (to_date(' 2021-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian')) segment creation immediate
pctfree 10 pctused 40 initrans 1 maxtrans 255
nocompress logging
storage(initial 8388608 next 1048576 minextents 1 maxextents 2147483645
pctincrease 0 freelists 1 freelist groups 1
buffer_pool default flash_cache default cell_flash_cache default)
tablespace "users" );
插入几条数据：
insert into tmp_part_day2 values(1,'aaa','bbb',sysdate -100);
insert into tmp_part_day2 values(1,'aaa','bbb',sysdate -90);
insert into tmp_part_day2 values(1,'aaa','bbb',sysdate -88);
insert into tmp_part_day2 values(1,'aaa','bbb',sysdate -12);
commit;
找原因，看表定义
set long 1000
select dbms_metadata.get_ddl ('table','tmp_part_day2','test') from dual;
找规律，准备清理
col table_name for a20
col partition_name for a20
col high_value for a55
select table_name,partition_name,high_value,partition_position,tablespace_name,num_rows from dba_tab_partitions where table_name='tmp_part_day2';
拼脚本，然后执行
select 'alter table test.tmp_part_day2 drop partition '||partition_name||' update indexes; ' c
from dba_segments where segment_name='tmp_part_day2' and partition_name<='sys_p950';

然后即可执行生成的脚本，对不想要的分区清理，如果partition_name比较乱，没有规律，那么partition_position条件也可以考虑（and partition_position<900）。

阅读(948) | 评论(0) | 转发(0) |

上一篇：dcd 坏连接检测

下一篇：时钟不同步导致ora.crsd无法启动

给主人留下些什么吧！~~

| | | | |

感谢所有关心和支持过chinaunix的朋友们