万能拆分脚本

– author: dengyu
– file: split_partyyyymm_char.sql
– desc: 拆分文件命名格式yyyymm
– order: desc
– date:2015-09-08
– note: 该脚本只仅仅打印拆分脚本，并非执行
– version 2.0 dengyu
倒序拆分 自动判断分区值格式是number,date,char

DECLARE
v_tabname VARCHAR2 (130) := ‘OWNER.TABLENAME’; –表名
v_partname_start VARCHAR2 (120) := ‘PRE_TAB_’; –分区开头
v_partmax VARCHAR2 (130) := ‘TAB_max’; –最大分区名
v_partend DATE := DATE ‘2015-6-1’; – 预分到分区月份
v_partcur DATE := DATE ‘2013-5-1’; – 当前的最大分区月份
v_partnext DATE;
v_sql VARCHAR2 (32766);
v_pctype1 varchar2(100);
v_pctype2 varchar2(100);
v_col varchar2(100);
v_coltype varchar2(100);
BEGIN

SELECT tc.column_name,tc.data_type into v_col,v_coltype
FROM dba_part_key_columns pc
JOIN
dba_tab_columns tc
ON pc.owner = tc.owner
AND pc.name = tc.table_name
AND pc.column_name = tc.column_name
WHERE tc.table_name =
UPPER (
CASE
WHEN INSTR (v_tabname, ‘.’) > 0
THEN
SUBSTR (v_tabname,
INSTR (v_tabname, ‘.’) + 1)
ELSE
v_tabname
END)
AND tc.owner =
CASE
WHEN INSTR (v_tabname, ‘.’) > 0
THEN
UPPER (
SUBSTR (v_tabname,
1,
INSTR (v_tabname, ‘.’) - 1))
ELSE
USER
END;
IF v_coltype = ‘DATE’
THEN
v_pctype1 := ‘to_date(”’;
v_pctype2 := ”’,”yyyymmdd”)’;
ELSIF ( v_coltype = ‘VARCHAR2’
OR v_coltype = ‘VARCHAR’
OR v_coltype = ‘CHAR’)
THEN
v_pctype1 := ””;
v_pctype2 := ””;
ELSE
v_pctype1 := ”;
v_pctype2 := ”;
END IF;
– split max
v_sql :=
‘alter table ’
|| v_tabname
|| ’ split partition ’
|| v_partmax
|| ’ at(’
|| v_pctype1
||to_char(ADD_MONTHS (v_partend, 1),’yyyymm’)
||v_pctype2
|| ‘)into (partition ’
|| v_partname_start
|| TO_CHAR (v_partend, ‘yyyymm’)
|| ‘, partition ’
|| v_partmax
|| ‘)’;
dbms_output.put_line(v_sql);
– execute immediate v_sql;
– dbms_output.put_line(TO_CHAR (v_partend, ‘yyyymm’)||’ had splited.’);

– split yyymm partition itertor
WHILE v_partcur < v_partend
LOOP
v_partnext := ADD_MONTHS (v_partend, -1);
v_sql :=
‘alter table ’
|| v_tabname
|| ’ split partition ’
|| v_partname_start
|| TO_CHAR (v_partend, ‘yyyymm’)
|| ’ at(’
|| v_pctype1
|| to_char(v_partend,’yyyymm’)
||v_pctype2
|| ‘)into (partition ’
|| v_partname_start
|| TO_CHAR (v_partnext, ‘yyyymm’)
|| ‘, partition ’
|| v_partname_start
|| TO_CHAR (v_partend, ‘yyyymm’)
|| ‘)’;
dbms_output.put_line(v_sql);
– execute immediate v_sql;
– dbms_output.put_line(v_partnext||’ had splited.’);
v_partend := v_partnext;
END LOOP;
END;

生成的脚本手动执行或把execute 前注释去掉直接执行，
另外可以根据dba[all][user]_part_tables 判断分区类型，再升级上面的脚本自适应list,range 分区

注意分区后的index status