?过程名：manage_partition
?create or replace procedure manage_partition is
?partition_name_add_1? ? ? varchar2(20);
?partition_name_reduce_5? varchar2(20);
?current_time? ? ? ? ? ? ? varchar2(20);
?v_Sql? ? ? ? ? ? ? ? ? ? varchar2(1000);
?partiton_name? ? ? ? ? ? varchar2(50);
?partition_values? ? ? ? ? varchar2(20);
?swap_count? ? ? ? ? ? ? ? number(38);
?pro_name? ? ? ? ? ? ? ? ? varchar2(20);
?err_info? ? ? ? ? ? ? ? ? varchar2(20);
?sj? ? ? ? ? ? ? ? ? ? ? ? varchar2(20);
?cursor all_data is select table_name,max(partition_name) as partition_name,tablespace_name from user_tab_partitions where table_name in('T_partition_1','T_partition_2') group by
?table_name,tablespace_name;
?type mt_his is record(table_name varchar2(20),partiton_name varchar2(20),tablespace_name varchar2(50));
?all_table mt_his;
?begin
? select to_char(sysdate+1,'yyyy-mm-dd hh24:mi:ss') into partition_values from dual;
?select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') into current_time from dual;
?select 'P_'||substr(to_char(sysdate+1,'yyyymmdd'),1,8)||'_23'? into partition_name_add_1 from dual;
?select 'P_'||substr(to_char(sysdate - interval '5' day,'yyyymmdd'),1,8)||'_23'? into partition_name_reduce_5 from dual;
?for all_table in all_data loop
? if partition_name_add_1 <> all_table.partition_name then
? v_Sql := 'alter table '||all_table.table_name||' add partition '||partition_name_add_1||' values less than(TO_DATE('||''''||partition_values||''''||','||'''YYYY-MM-DD HH24:MI:SS'''||')) tablespace '||all_table.tablespace_name||'';
? execute immediate v_Sql;
? end if;
?end loop;
? ? declare
? ? cursor old_partition_1 is select partition_name,table_name from user_tab_partitions where table_name='T_partition_1' and substr(partition_name,3,10) < to_char(sysdate - interval '6' day,'yyyymmdd');
? ? --old_p_1 user_tab_partitions.partition_name%type;
? ? begin
? ? ? for old_p_1 in old_partition_1 loop
? ? v_Sql := 'alter table '||old_p_1.table_name||' drop partition '||old_p_1.partition_name||'';
? ? ? execute immediate v_Sql;
? ? ? end loop;
? ? end;
? ? declare
? ? ? cursor old_partition_2 is select partition_name,table_name from user_tab_partitions where table_name='T_partition_2' and? substr(partition_name,3,10) < to_char(sysdate - interval '1' year,'yyyymmdd');
? ? --old_p_1 user_tab_partitions.partition_name%type;
? ? begin
? ? ? for old_p_2 in old_partition_2 loop
? ? v_Sql := 'alter table '||old_p_2.table_name||' drop partition '||old_p_2.partition_name||'';
? ? dbms_output.put_line(old_p_2.table_name);
? ? ? execute immediate v_Sql;
? ? ? ? end loop;
? ? end;
? ? select count(1) into swap_count from T_PARTITION_SWAP;
? ? if swap_count=0 then
? ? ? ? v_Sql := 'alter table T_partition_1 exchange partition '||partition_name_reduce_5||' with table T_PARTITION_SWAP UPDATE INDEXES';
? ? ? execute immediate v_Sql;
? ? ? ? v_Sql := 'alter table T_partition_2 exchange partition '||partition_name_reduce_5||' with table T_PARTITION_SWAP UPDATE INDEXES';
? ? ? ? execute immediate v_Sql;
? ? ? else
? ? ? ? v_Sql := 'truncate table T_SMSGATEWAY_MT_SWAP';
? ? ? ? execute immediate v_Sql;
? ? ? ? ? v_Sql := 'alter table T_SMSGATEWAY_MT exchange partition '||partition_name_reduce_5||' with table T_SMSGATEWAY_MT_SWAP UPDATE INDEXES';
? ? ? execute immediate v_Sql;
? ? ? ? v_Sql := 'alter table T_SMSGATEWAY_MT_HIS exchange partition '||partition_name_reduce_5||' with table T_SMSGATEWAY_MT_SWAP UPDATE INDEXES';
? ? ? ? execute immediate v_Sql;
? ? ? end if;
?exception
? when others then
? ? --sg_log_err('manage_partition