oracle分区交换存储过程,oracle存储过程，分区

create or replace procedure stp_maintenance_partition as

v_currmax_date date;

v_partdate     varchar2(20);

v_high_value   varchar2(20);

v_partname     varchar(30);

v_tablename       varchar(80);

v_tablespace_name varchar(80);

v_partition_cnt   number(10);

v_errormsg        varchar2(2000);

v_tempname        varchar2(50);

v_part_cnt        number(10);

v_remote_part_cnt number(10);

v_remote_tab_cnt  number(10);

v_drop_date date;

v_add_date  date;

begin

--------处理按天分区的表--------------------------------------------------

for r in (select distinct p.*

from tm_tab_part_maintenance p, user_tab_partitions u

where p.table_name = u.table_name

and p.parition_type = 'DAY'

and p.valid_flg = 1) loop

v_tempname := r.table_name;

v_currmax_date := null;

v_drop_date    := sysdate - r.drop_time_units - 1;

for cur_part in (select partition_name, high_value

from user_tab_partitions

where table_name = r.table_name

order by partition_name) loop

v_high_value := substr(cur_part.high_value, 11, 19);

--删除超过时间的分区

if to_date(v_high_value, 'YYYY-MM-DD HH24:MI:SS') <= v_drop_date then

--判断是否这个表只剩一个分区

select count(*)

into v_part_cnt

from user_tab_partitions

where table_name = r.table_name;

if (v_part_cnt > 1) then

execute immediate 'alter table ' || r.table_name ||

' drop partition ' || cur_part.partition_name ||

' /*ggh_notsync*/';

end if;

end if;

--找出该分区表目前最大的high_value

if v_currmax_date is null then

v_currmax_date := to_date(v_high_value, 'YYYY-MM-DD HH24:MI:SS');

elsif to_date(v_high_value, 'YYYY-MM-DD HH24:MI:SS') > v_currmax_date then

v_currmax_date := to_date(v_high_value, 'YYYY-MM-DD HH24:MI:SS');

end if;

end loop;

--如果配置表里表空间为空，找出该表目前所用表空间

if r.TABLESPACE_NAME is null then

select tablespace_name

into v_tablespace_name

from user_tab_partitions

where table_name = r.table_name

and rownum = 1;

else

v_tablespace_name := r.TABLESPACE_NAME;

end if;

----增加分区

while (v_currmax_date - trunc(sysdate) <= r.add_time_units) loop

v_partname     := to_char(trunc(v_currmax_date), 'YYYYMMDD');

v_partdate     := to_char(trunc(v_currmax_date + 1),

'YYYY-MM-DD HH24:MI:SS');

v_currmax_date := v_currmax_date + 1;

select count(1)

into v_partition_cnt

from user_tab_partitions

where table_name = r.table_name

and partition_name = r.partition_pre || v_partname;

if (v_partition_cnt = 0) then

execute immediate 'alter table ' || r.table_name ||

' add partition ' || r.partition_pre ||

v_partname || ' values less than (to_date(''' ||

v_partdate ||

''',''YYYY-MM-DD HH24:MI:SS'')) tablespace ' ||

v_tablespace_name;

--如果配置了索引表空间索引，索引分区需rebuild到指定的索引表空间

if (r.index_tb_name is not null) then

for cur_part_ind in (select a.index_name,

a.partition_name,

a.tablespace_name,

a.status

from user_ind_partitions a, user_indexes b

where a.index_name = b.index_name

and b.table_name = r.table_name

and a.partition_name =

r.partition_pre || v_partname) loop

execute immediate 'alter index ' || cur_part_ind.index_name ||

' rebuild partition ' ||

cur_part_ind.partition_name || ' tablespace ' ||

r.index_tb_name;

end loop;

end if;

end if;

end loop;

--重建不可用的全局索引

for cur_global_ind in (select index_name, tablespace_name, status

from user_indexes

where table_name = r.table_name

and status = 'UNUSABLE') loop

execute immediate 'alter index ' || cur_global_ind.index_name ||

' rebuild tablespace ' ||

cur_global_ind.tablespace_name;

end loop;

--重建不可用的局部索引

for cur_part_ind in (select a.index_name,

a.partition_name,

a.tablespace_name,

a.status

from user_ind_partitions a, user_indexes b

where a.index_name = b.index_name

and b.table_name = r.table_name

and a.status = 'UNUSABLE') loop

execute immediate 'alter index ' || cur_part_ind.index_name ||

' rebuild partition ' ||

cur_part_ind.partition_name || ' tablespace ' ||

cur_part_ind.tablespace_name;

end loop;

end loop;

--------处理按月分区的表-----------------------------------------------

for rm in (select distinct p.*

from tm_tab_part_maintenance p, user_tab_partitions u

where p.table_name = u.table_name

and p.parition_type = 'MONTH'

and p.valid_flg = 1) loop

v_tempname := rm.table_name;

v_currmax_date := null;

v_drop_date    := add_months(trunc(sysdate, 'MM'), -rm.drop_time_units);

for cur_part in (select partition_name, high_value

from user_tab_partitions

where table_name = rm.table_name

order by partition_name) loop

v_high_value := substr(cur_part.high_value, 11, 19);

--删除超过时间的分区

if to_date(v_high_value, 'YYYY-MM-DD HH24:MI:SS') <= v_drop_date then

--判断是否这个表只剩一个分区

select count(*)

into v_part_cnt

from user_tab_partitions

where table_name = rm.table_name;

if (v_part_cnt > 1) then

execute immediate 'alter table ' || rm.table_name ||

' drop partition ' || cur_part.partition_name ||

' /*ggh_notsync*/';

end if;

end if;

--找出该分区表目前最大的high_value

if v_currmax_date is null then

v_currmax_date := to_date(v_high_value, 'YYYY-MM-DD HH24:MI:SS');

elsif to_date(v_high_value, 'YYYY-MM-DD HH24:MI:SS') > v_currmax_date then

v_currmax_date := to_date(v_high_value, 'YYYY-MM-DD HH24:MI:SS');

end if;

end loop;

--如果配置表里表空间为空，找出该表目前所用表空间

if rm.tablespace_name is null then

select tablespace_name

into v_tablespace_name

from user_tab_partitions

where table_name = rm.table_name

and rownum = 1;

else

v_tablespace_name := rm.TABLESPACE_NAME;

end if;

--增加分区

while (add_months(v_currmax_date, -rm.add_time_units) <=

trunc(sysdate, 'MM')) loop

v_partname     := to_char(trunc(v_currmax_date), 'YYYYMM');

v_partdate     := to_char(trunc(add_months(v_currmax_date, 1)),

'YYYY-MM-DD HH24:MI:SS');

v_currmax_date := add_months(v_currmax_date, 1);

select count(1)

into v_partition_cnt

from user_tab_partitions

where table_name = rm.table_name

and partition_name = rm.partition_pre || v_partname;

if (v_partition_cnt = 0) then

execute immediate 'alter table ' || rm.table_name ||

' add partition ' || rm.partition_pre ||

v_partname || ' values less than (to_date(''' ||

v_partdate ||

''',''YYYY-MM-DD HH24:MI:SS'')) tablespace ' ||

v_tablespace_name;

--如果配置了索引表空间索引，索引分区需rebuild到指定的索引表空间

if (rm.index_tb_name is not null) then

for cur_part_ind in (select a.index_name,

a.partition_name,

a.tablespace_name,

a.status

from user_ind_partitions a, user_indexes b

where a.index_name = b.index_name

and b.table_name = rm.table_name

and a.partition_name =

rm.partition_pre || v_partname) loop

execute immediate 'alter index ' || cur_part_ind.index_name ||

' rebuild partition ' ||

cur_part_ind.partition_name || ' tablespace ' ||

rm.index_tb_name;

end loop;

end if;

end if;

end loop;

--重建不可用的全局索引

for cur_global_ind in (select index_name, tablespace_name, status

from user_indexes

where table_name = rm.table_name

and status = 'UNUSABLE') loop

execute immediate 'alter index ' || cur_global_ind.index_name ||

' rebuild tablespace ' ||

cur_global_ind.tablespace_name;

end loop;

--重建不可用的局部索引

for cur_part_ind in (select a.index_name,

a.partition_name,

a.tablespace_name,

a.status

from user_ind_partitions a, user_indexes b

where a.index_name = b.index_name

and b.table_name = rm.table_name

and a.status = 'UNUSABLE') loop

execute immediate 'alter index ' || cur_part_ind.index_name ||

' rebuild partition ' ||

cur_part_ind.partition_name || ' tablespace ' ||

cur_part_ind.tablespace_name;

end loop;

end loop;

exception

when others then

/*dbms_output.put_line('Error:' || sqlcode || ':' || sqlerrm);

v_errormsg := 'Error:' || sqlcode || ':' || sqlerrm || ';' || CHR(10) ||

v_tempname || ' add partition failed!';*/

/* tivoli.SEND_MAIL(vIn_Sender       => 'sfods@sfexpress.com',

vIn_ReceiverList => 'niebaohong@sf-express.com',

vIn_Subject      => 'add partition failed',

vIn_Text         => v_errormsg);*/

/*utl_mail.send(sender     => 'sfods@sfexpress.com',

recipients => 'niebaohong@sf-express.com',

subject    => 'add partition failed!',

message    => 'sfods add partition failed,please check it',

mime_type  => 'text/plain;charset=ZHS16GBK');*/

PKG_SYS_LOG.ERROR_LOG(P_PACKAGE_NAME => '',                           P_PROC_NAME    => 'STP_MAINTENANCE_PARTITION',                           P_EXCEP_DT     => SYSDATE,                           P_EXCEP_CODE   => SQLCODE,                           P_EXCEP_DESC   => SQLERRM,                           P_EXCEP_REMK   => '增加/删除分区 ' || v_tempname,                           P_LINE_NO      => null); end stp_maintenance_partition;