oracle添加分区语句_oracle增加分区的方法

在项目中经常要增加大量的分区，总结了增加分区的方法：

1.如果只增加一级分区，没有子分区，可以简单的用如下语句增加：

alter table TDW_12580_ORD_REL_TAB_M

add PARTITION M_TDW_12580_201005 VALUES (201005)

[@more@]

2.如果增加的分区有子分区，则需要先建一个一级分区和子分区，然后用下面的脚本来增加：

注意：表名用大写，日期格式为20100801 结束日期为下一个月的1号，如20100901

脚本：

declare

--初始化参数

v_table_name varchar2(30);

v_partition_name varchar2(30);

v_subpartition_name varchar2(30);

v_segment_type varchar2(30);

v_begin_date_str varchar2(10);

v_sql varchar2(1000);

v_begin_date date;

v_end_date date;

--查找出所有分区表

cursor C_table_name is

select t1.table_name

from user_tables t1

where t1.table_name in ('&TABLE_NAME');

begin

open C_table_name;

loop

fetch C_table_name

into v_table_name;

exit when C_table_name%notfound;

--查找出分区表的类型

select distinct (t2.segment_type)

into v_segment_type

from user_segments t2

where t2.segment_name = v_table_name;

if v_segment_type in ('TABLE SUBPARTITION') then

--取出分区和子分区表名的不含日期部分

select distinct substr(t3.partition_name,

0,

length(t3.partition_name) - 6),

substr(t3.subpartition_name,

0,

length(t3.subpartition_name) - 8)

into v_partition_name, v_subpartition_name

from user_tab_subpartitions t3

where t3.table_name = v_table_name;

--初始化日期

v_begin_date := to_date('&START_DATE', 'YYYYMMDD');

v_end_date := to_date('&END_DATE', 'YYYYMMDD');

--循环创建2010年的表分区

while v_begin_date < v_end_date loop

--创建增加分区语句，如：alter table v_table_name add partition v_partition_nameYYYYMM values (YYYYMM) (subpartition v_subpartition_nameYYYYMMDD values (YYYYMMDD));

v_begin_date_str := substr(to_char(v_begin_date, 'YYYYMMDD'), 7, 2);

if v_begin_date_str in ('01') then

v_sql := 'alter table ' || v_table_name || ' add partition ' ||

v_partition_name ||

substr(to_char(v_begin_date, 'YYYYMMDD'), 0, 6) ||

' values (' ||

substr(to_char(v_begin_date, 'YYYYMMDD'), 0, 6) ||

') (subpartition ' || v_subpartition_name ||

to_char(v_begin_date, 'YYYYMMDD') || ' values (' ||

to_char(v_begin_date, 'YYYYMMDD') || '))';

else

v_sql := 'alter table ' || v_table_name || ' modify partition ' ||

v_partition_name ||

substr(to_char(v_begin_date, 'YYYYMMDD'), 0, 6) ||

' add subpartition ' || v_subpartition_name ||

to_char(v_begin_date, 'YYYYMMDD') || ' values (' ||

to_char(v_begin_date, 'YYYYMMDD') || ')';

end if;

--显示增加分区语句

--DBMS_OUTPUT.put_line(v_sql);

--执行增加分区语句

execute immediate v_sql;

--日期加一天

v_begin_date := v_begin_date + 1;

end loop;

elsif v_segment_type in ('TABLE PARTITION') then

--取出分区表名的不含日期部分

select distinct (substr(t4.partition_name,

0,

length(t4.partition_name) - 6))

into v_partition_name

from user_tab_partitions t4

where t4.table_name = v_table_name;

--初始化日期

v_begin_date := to_date('&START_DATE', 'YYYYMMDD');

v_end_date := to_date('&START_DATE', 'YYYYMMDD');

--循环创建2010年的表分区

while v_begin_date < v_end_date loop

--创建增加分区语句，如：alter table v_table_name add partition v_partition_nameYYYYMM values (YYYYMM);

v_sql := 'alter table ' || v_table_name || ' add partition ' ||

v_partition_name ||

substr(to_char(v_begin_date, 'YYYYMMDD'), 0, 6) ||

' values (' ||

substr(to_char(v_begin_date, 'YYYYMMDD'), 0, 6) || ')';

--显示增加分区语句

--- DBMS_OUTPUT.put_line(v_sql);

--执行增加分区语句

execute immediate v_sql;

--日期加一月

v_begin_date := add_months(v_begin_date, 1);

end loop;

else

DBMS_OUTPUT.put_line(v_table_name || '表类型为' || v_segment_type ||

',请查看!');

end if;

end loop;

close C_table_name;

end;