整理一些分区表自动管理的脚本，以备不时之需
1、分区合并
create or replace procedure consumetest.sp_auto_partition_combine
/************************************************************************************
Name: sp_auto_partition_combine
Purpose: 分区合并
Output: errcode(7100=true,8100=false),errstr
Source table: partition tables
************************************************************************************/
(v_errcode out number, v_errstr out varchar2) as
v_minparti varchar2(10); --实际的最小分区
v_keepparti varchar2(10); --保留的最小分区
v_firstparti varchar2(10);
v_secondparti varchar2(10);
sqlcmd varchar2(300);
v_keepdaynum number := 110;
v_loopcount number; --循环计数器
v_exec_failinfo varchar2(500);
begin
--step1:处理按天分区的分区表
for c in (select distinct table_name as table_name
from user_tab_partitions
where partition_name <> 'PMAX'
and table_name like '%_ITEMDETAIL%'
and table_name like 'T%'
and high_value_length = 83
and length(partition_name) = 9
order by table_name) loop
select min(partition_name),
'P' || to_char(to_date(replace(max(partition_name), 'P', ''),
'yyyy-mm-dd') - v_keepdaynum,
'yyyymmdd')
into v_minparti, v_keepparti
from user_tab_partitions
where table_name = c.table_name
and partition_name <> 'PMAX';
if (v_minparti < v_keepparti) then
select count('x')
into v_loopcount
from user_tab_partitions
where table_name = c.table_name
and partition_name >= v_minparti
and partition_name <= v_keepparti;
for p in 1 .. v_loopcount loop
select min(partition_name), max(partition_name)
into v_firstparti, v_secondparti
from user_tab_partitions
where table_name = c.table_name
and rownum < 3
order by partition_name;
sqlcmd := 'alter table ' || c.table_name || ' merge partitions ' ||
v_firstparti || ',' || v_secondparti ||
' into partition ' || v_secondparti;
--execute immediate sqlcmd;
dbms_output.put_line('sqlcmd = ' || sqlcmd);
end loop;
end if;
end loop;
v_errcode := 7100;
v_errstr := 'sp_wh_auto_merge_partitions mession complete!';
exception
when others then
begin
dbms_output.put_line(sqlerrm);
v_exec_failinfo := substr(SQLERRM, 1, 500);
v_errcode := 8100;
v_errstr := v_exec_failinfo;
end;
end sp_auto_partition_combine;
/2、分区自动扩展
create or replace procedure consumetest.sp_auto_partition_extend
/************************************************************************************
Name: sp_auto_partition_extend
Purpose: 自动向前、向后扩展分区表分区(默认情况下扩展所有分区表，若想指定某张表则修改for循环条件即可)
Input: manageflag(0：向前扩展 1：向后扩展)
extenddate(eg.20140101)
Output: errcode(7100=true,8100=false),errstr
Source table: partition tables
************************************************************************************/
(v_manageflag in number, --0：向前扩展 1：向后扩展
v_extendate in varchar2, -- 指定向前扩展分区的起始时间或向后扩展分区的终止时间(eg.20140101)
v_errcode out number,
v_errstr out varchar2) as
v_count number; --计数器
v_actual_ptnum number; --除PMAX外分区表中已有分区数量（实际值）
v_already_ptnum number; --除PMAX外分区表中已有分区数量（理论值）
v_extenddate_num number; --向前或向后扩展的分区数
buffer varchar2(50);
v_sqlcmd varchar2(500);
v_extenddate varchar2(20);
v_lost_pname varchar2(10); --漏掉的分区名
v_extend_pname varchar2(10); --扩展分区
v_str_lost_pname varchar2(10); --漏掉分区的时间格式yyyymmdd
v_next_lost_pname varchar2(10); --漏掉的分区的前面的一个分区
v_extenddate_pname varchar2(10); --向前扩展分区的起始分区或向后扩展分区的终止分区(eg.P20140101)
v_str_extend_pname varchar2(10); --下一个扩展分区(eg.20140101)
v_next_extend_pname varchar2(10); --下一个扩展分区
begin
buffer := 'begin dbms_output.enable(200000);end;';
execute immediate buffer;
if (instr(v_extendate, 'P', 1) > 0) then
v_extenddate := replace(v_extendate, 'P', '');
elsif (instr(v_extendate, 'p', 1) > 0) then
v_extenddate := replace(v_extendate, 'p', '');
elsif (instr(v_extendate, '_', 1) > 0) then
v_extenddate := replace(v_extendate, '_', '');
elsif (instr(v_extendate, '-', 1) > 0) then
v_extenddate := replace(v_extendate, '-', '');
elsif (length(v_extendate) = 8) then
v_extenddate := v_extendate;
else
v_errcode := 8100;
v_errstr := ' Invalid parameter! ';
return;
end if;
--在进行扩展分区操作前，先检查现有分区是否存在断档现象，如有，则补全
for pt_info in (select table_name,
count('x') count_ptname,
max(partition_name) max_ptname,
min(partition_name) min_ptname
from user_tab_partitions
where partition_name <> 'PMAX'
and table_name like 'T%'
and high_value_length = 83
and length(partition_name) = 9
--and table_name = 'T_DW_IWM_GDB_CASHSTAT'
group by table_name
order by table_name) loop
v_already_ptnum := to_date(substr(pt_info.max_ptname, 2, 8), 'yyyymmdd') -
to_date(substr(pt_info.min_ptname, 2, 8), 'yyyymmdd') + 1;
v_actual_ptnum := pt_info.count_ptname;
--已有分区中存在分区断档现象
if (v_already_ptnum > v_actual_ptnum) then
for i in 2 .. v_already_ptnum loop
v_lost_pname := 'P' ||
to_char(to_date(substr(pt_info.max_ptname, 2, 8),
'yyyymmdd') - i + 1,
'yyyymmdd');
select count('x')
into v_count
from user_tab_partitions
where table_name = pt_info.table_name
and partition_name = v_lost_pname;
if (v_count = 0) then
v_next_lost_pname := 'P' || to_char(to_date(substr(pt_info.max_ptname,
2,
8),
'yyyymmdd') - i + 2,
'yyyymmdd');
v_str_lost_pname := substr(v_next_lost_pname, 2, 8);
v_sqlcmd := '';
v_sqlcmd := v_sqlcmd || 'ALTER TABLE ' ||
pt_info.table_name || ' split partition ' ||
v_next_lost_pname || ' at (to_date(''' ||
v_str_lost_pname ||
''',''yyyymmdd'')) into (partition ' ||
v_lost_pname || ', partition ' ||
v_next_lost_pname || ')' ||
' update global indexes ';
dbms_output.put_line(' v_sqlcmd := ' || v_sqlcmd);
--execute immediate v_sqlcmd;
end if;
end loop;
end if;
end loop;
--开始进行分区扩展操作
if (v_manageflag = 0) then
for pt_info in (select table_name, min(partition_name) min_ptname
from user_tab_partitions
where partition_name <> 'PMAX'
and table_name like 'T%'
and high_value_length = 83
and length(partition_name) = 9
--and table_name = 'T_DW_IWM_GDB_CASHSTAT'
group by table_name
order by table_name) loop
--自动向前扩展
v_extenddate_pname := 'P' || v_extenddate;
if (to_date(substr(pt_info.min_ptname, 2, 8), 'yyyymmdd') >
to_date(substr(v_extenddate_pname, 2, 8), 'yyyymmdd')) then
--计算向前扩展的分区数
v_extenddate_num := to_date(substr(pt_info.min_ptname, 2, 8),
'yyyymmdd') -
to_date(substr(v_extenddate_pname, 2, 8),
'yyyymmdd');
for i in 1 .. v_extenddate_num loop
v_extend_pname := 'P' || to_char(to_date(substr(pt_info.min_ptname,
2,
8),
'yyyymmdd') - i + 1,
'yyyymmdd');
v_next_extend_pname := 'P' || to_char(to_date(substr(pt_info.min_ptname,
2,
8),
'yyyymmdd') - i,
'yyyymmdd');
v_str_extend_pname := substr(v_extend_pname, 2, 8);
v_sqlcmd := '';
v_sqlcmd := v_sqlcmd || 'ALTER TABLE ' ||
pt_info.table_name || ' split partition ' ||
v_extend_pname || ' at (to_date(''' ||
v_str_extend_pname ||
''',''yyyymmdd'')) into (partition ' ||
v_next_extend_pname || ', partition ' ||
v_extend_pname || ')' ||
' update global indexes ';
dbms_output.put_line(' v_sqlcmd := ' || v_sqlcmd);
--execute immediate v_sqlcmd;
end loop;
end if;
end loop;
else
--自动向后扩展
for pt_info in (select table_name, max(partition_name) max_ptname
from user_tab_partitions
where partition_name <> 'PMAX'
and table_name like 'T%'
and high_value_length = 83
and length(partition_name) = 9
group by table_name
order by table_name) loop
--自动向后扩展
v_extenddate_pname := 'P' || v_extenddate;
if (to_date(substr(pt_info.max_ptname, 2, 8), 'yyyymmdd') <
to_date(substr(v_extenddate_pname, 2, 8), 'yyyymmdd')) then
--计算向后扩展的分区数
v_extenddate_num := to_date(substr(v_extenddate_pname, 2, 8),
'yyyymmdd') -
to_date(substr(pt_info.max_ptname, 2, 8),
'yyyymmdd');
for i in 1 .. v_extenddate_num loop
v_extend_pname := 'P' || to_char(to_date(substr(pt_info.max_ptname,
2,
8),
'yyyymmdd') + i,
'yyyymmdd');
v_next_extend_pname := 'P' || to_char(to_date(substr(pt_info.max_ptname,
2,
8),
'yyyymmdd') + i + 1,
'yyyymmdd');
v_str_extend_pname := substr(v_next_extend_pname, 2, 8);
v_sqlcmd := '';
v_sqlcmd := v_sqlcmd || 'ALTER TABLE ' ||
pt_info.table_name ||
' split partition pmax ' ||
' at (to_date(''' || v_str_extend_pname ||
''',''yyyymmdd'')) into (partition ' ||
v_extend_pname ||
', partition pmax) update global indexes ';
dbms_output.put_line(' v_sqlcmd := ' || v_sqlcmd);
--execute immediate v_sqlcmd;
end loop;
end if;
end loop;
end if;
commit;
v_errcode := 7100;
v_errstr := ' sp_auto_partition_extend mission complete ! ';
exception
when others then
dbms_output.put_line(sqlerrm);
v_errstr := substr(sqlerrm, 1, 400);
select 8100 into v_errcode from dual;
end sp_auto_partition_extend;3、移动分区表空间
create or replace procedure consumetest.sp_auto_partition_move(errcode out number,
errstr out varchar2) is
v_sqlcmd varchar2(300);
v_ts_name varchar2(20) := 'TS_YXW_HISTORY';
cursor c1 is
select table_name, partition_name
from user_tab_partitions
where partition_name between 'P201206' and 'P201207'
and composite = 'NO'
and tablespace_name <> v_ts_name;
/*cursor c2 is
select b.table_name,
b.partition_name,
b.subpartition_name,
b.tablespace_name
from user_tab_partitions a, user_tab_subpartitions b
where a.table_name = b.table_name
and a.partition_name = b.partition_name
and b.partition_name between 'P201108' and 'P20110831'
and b.tablespace_name <> v_ts_name;*/
begin
FOR ptname in c1 LOOP
v_sqlcmd := 'alter table ' || ptname.table_name || ' move partition ' ||
ptname.partition_name || ' tablespace ' || v_ts_name ||
' compress';
execute immediate v_sqlcmd;
END LOOP;
/*FOR ptname in c2 LOOP
v_sqlcmd := 'alter table ' || ptname.table_name ||
' move subpartition ' || ptname.subpartition_name ||
' tablespace ' || v_ts_name || ' compress';
execute immediate v_sqlcmd;
END LOOP;*/
errcode := 7100;
errstr := 'sp_auto_partition_move mession complete!';
EXCEPTION
WHEN OTHERS THEN
errcode := 8100;
errstr := 'sp_auto_partition_move mession fails!';
end sp_auto_partition_move;4、自动添加分区并记录日志
create or replace procedure consumetest.sp_auto_partition_task
/************************************************************************************
Name: sp_auto_partition_task
Purpose: 每天自动向前扩展一个分区，并保证每个表分区数量不变
Output: errcode(7100=true,8100=false),errstr
************************************************************************************/
(current_day date, keep_daynum number, keep_monthnum number) as
v_pt_endday_time date; --当前最大分区数据结束时间
v_pt_endmonth_time date; --当前最大分区数据结束时间
v_pday date; --新分区数据区间时间(v_pday 00:00:00--23:59:59)
v_pmonth date; --新分区数据区间时间(月)
v_pt_endday_time_next char(8); --新分区数据结束时间(天)
v_pt_endmonth_time_next char(8); --新分区数据结束时间(月)
day_num number; --新分区结束时间与当前最大分区结束时间天数差
month_num number; --新分区结束时间与当前最大分区结束时间月份数差
v_ptname_next varchar2(9); --新分区名
sqlcmd varchar2(300);
v_sql varchar2(300);
begin
--step1:处理按天分区的分区表
v_pday := trunc(current_day + keep_daynum, 'dd'); ----分区日期(添加当前时间之后keep_daynum天的分区)
for p_day_partition in (select table_name, max(partition_name) max_ptname
from user_tab_partitions
where partition_name <> 'PMAX'
and table_name like 'T%'
and high_value_length = 83
and length(partition_name) = 9
group by table_name
order by max_ptname) loop
v_pt_endday_time := to_date(substr(p_day_partition.max_ptname, 2, 9),
'yyyymmdd') + 1;
if (v_pday = v_pt_endday_time) then
v_pt_endday_time_next := to_char(v_pt_endday_time + 1, 'yyyymmdd'); --下一个分区结束点
v_ptname_next := 'P' || to_char(v_pday, 'yyyymmdd'); --下一个分区的分区名(P+分区日期)
sqlcmd := 'alter table ' || p_day_partition.table_name ||
' split partition pmax at ( to_date(''' ||
v_pt_endday_time_next ||
''',''yyyymmdd'')) into (partition ' ||
v_ptname_next ||
', partition pmax) update global indexes';
dbms_output.put_line(sqlcmd);
--execute immediate sqlcmd;
elsif v_pt_endday_time < v_pday then
--如果要添加的分区与已有的最大分区之间有漏掉的分区
day_num := v_pday - v_pt_endday_time; --漏掉的天数
for i in 1 .. day_num loop
v_pt_endday_time_next := to_char(v_pt_endday_time + 1, 'yyyymmdd'); --下一个分区结束点
v_ptname_next := 'P' ||
to_char(v_pt_endday_time, 'yyyymmdd'); --下一个分区的分区名(P+分区日期)
sqlcmd := 'alter table ' ||
p_day_partition.table_name ||
' split partition pmax at ( to_date(''' ||
v_pt_endday_time_next ||
''',''yyyymmdd'')) into (partition ' ||
v_ptname_next ||
', partition pmax) update global indexes';
dbms_output.put_line(sqlcmd);
--execute immediate sqlcmd;
v_pt_endday_time := v_pt_endday_time + 1; --加一天
end loop;
else
dbms_output.put_line('Today''s job has done,so didn''t split any day partition!');
exit;
end if;
end loop;
--step2:处理按月分区的分区表
v_sql := 'select trunc(sysdate,''mm'') + interval ''' || keep_monthnum ||
''' month from dual';
execute immediate v_sql
into v_pmonth; --分区日期(添加当前时间之后keep_monthnum月的分区)
for p_month_partition in (select table_name,
max(partition_name) max_ptname
from user_tab_partitions
where partition_name <> 'PMAX'
and table_name like 'T%'
and high_value_length = 83
and length(partition_name) = 7
group by table_name
order by max_ptname) loop
v_pt_endmonth_time := to_date(substr(p_month_partition.max_ptname, 2, 7),
'yyyymm');
if (v_pmonth = v_pt_endmonth_time) then
v_pt_endmonth_time_next := to_char(v_pt_endmonth_time + interval '2'
month,
'yyyymm'); --下一个分区结束点
v_ptname_next := 'P' || to_char(v_pt_endmonth_time +
interval '1' month,
'yyyymm'); --下一个分区的分区名(P+分区月份)
sqlcmd := 'alter table ' ||
p_month_partition.table_name ||
' split partition pmax at ( to_date(''' ||
v_pt_endmonth_time_next ||
''',''yyyymm'')) into (partition ' ||
v_ptname_next ||
', partition pmax) update global indexes';
dbms_output.put_line(sqlcmd);
--execute immediate sqlcmd;
elsif v_pt_endmonth_time < v_pmonth then
--如果要添加的分区与已有的最大分区之间有漏掉的分区
select months_between(v_pmonth, v_pt_endmonth_time)
into month_num --漏掉的月数
from dual;
for i in 1 .. month_num loop
v_pt_endmonth_time_next := to_char(v_pt_endmonth_time + interval '2'
month,
'yyyymm'); --下一个分区结束点
v_ptname_next := 'P' || to_char(v_pt_endmonth_time +
interval '1' month,
'yyyymm'); --下一个分区的分区名(P+分区月份)
sqlcmd := 'alter table ' ||
p_month_partition.table_name ||
' split partition pmax at ( to_date(''' ||
v_pt_endmonth_time_next ||
''',''yyyymm'')) into (partition ' ||
v_ptname_next ||
', partition pmax) update global indexes';
dbms_output.put_line(sqlcmd);
--execute immediate sqlcmd;
v_pt_endmonth_time := v_pt_endmonth_time + interval '1' month; --加一个月
end loop;
else
dbms_output.put_line('Today''s job has done,so didn''t split any month partition!');
exit;
end if;
end loop;
exception
when others then
begin
dbms_output.put_line(sqlerrm);
end;
end sp_auto_partition_task;5、移动分区表空间
create or replace procedure consumetest.sp_auto_tablespace_move is
v_ts_name varchar2(15) := 'TS_TABLE_DATA';
v_sqlcmd varchar2(300);
cursor c1 is
select table_name, partition_name
from user_tab_partitions
where tablespace_name = 'TS_TABLE_HISTORY';
begin
FOR ptname in c1 LOOP
v_sqlcmd := 'alter table hr.' || ptname.table_name ||
' move partition ' || ptname.partition_name ||
' tablespace ' || v_ts_name || ' parallel compress';
execute immediate v_sqlcmd;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
null;
end sp_auto_tablespace_move;
3688
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
