1. 创建分离分区的存储过程
CREATE OR REPLACE Procedure SP_Split_Partition( v_table_name_in in varchar2, v_part_name_prifex_in in varchar2, v_split_partition_source_in in varchar2, v_tablespace_name_in in varchar2, v_field_tablespace_name_in in varchar2, v_create_partition_count_in in number ) is /* 名称:SP_Split_Partition 描述:oracle分区表分离分区的存储过程 参数说明: v_table_name_in 分区表表名称 v_part_name_prifex_in 分区名称前缀 v_split_partition_source_in 要分离的目标分区 v_tablespace_name_in in 分区表所在表空间 v_field_tablespace_name_in 特别字段特别分区SQL v_create_partition_count_in 从现在开始创建多少个分区 */ -- Local variables here i integer; ---最后一个分区信息 cursor cur_utp(v_table_name_in in user_tab_partitions.table_name%TYPE) is select * from (select utp.table_name, utp.tablespace_name, utp.partition_name, utp.high_value, utp.high_value_length, utp.partition_position from user_tab_partitions utp where utp.table_name = UPPER(v_table_name_in) and utp.high_value_length<>8 order by utp.partition_position desc ) utp where rownum = 1; v_high_value varchar2(255); --less than value信息 v_partition_max_date date; ---- 当前最大分区 v_sqlexec VARCHAR2(2000); --DDL语句变量 v_count number := 0; v_interver number := 1; --步长间隔 单位(月) v_tablespace_name varchar2(200); v_max_date_this_time date; begin -- 根据v_create_partition_count_in, 得到最后要生成的分区日期 v_max_date_this_time:=last_day(add_months(trunc(SYSDATE), v_create_partition_count_in))+1; --取值 for utp in cur_utp(v_table_name_in) loop v_high_value := substr(utp.high_value,11,10); v_partition_max_date := to_date(v_high_value,'YYYY-MM-DD'); dbms_output.put_line('v_high_value:' || v_high_value); --如果没有给默认值 if (v_tablespace_name_in is null) then v_tablespace_name := utp.tablespace_name; else v_tablespace_name := v_tablespace_name_in; end if; end loop; i:= 0; v_partition_max_date := add_months(v_partition_max_date,v_interver); /************************************************************ alter table Article_Detail split partition Article_Detail_Others at(TO_DATE('2014-01-01', 'yyyy-mm-dd')) into( partition Article_Detail_201401 LOB (CONTENT_HTML) store as SECUREFILE (TABLESPACE wm_article_detail_clob) tablespace WM_ARTICLE_DETAIL, partition Article_Detail_Others LOB (CONTENT_HTML) store as SECUREFILE (TABLESPACE wm_article_detail_clob) tablespace WM_ARTICLE_DETAIL ); *************************************************************/ while v_partition_max_date <= v_max_date_this_time loop v_SqlExec := 'ALTER TABLE ' || v_table_name_in || ' SPLIT PARTITION ' || v_split_partition_source_in || ' AT' || '(TO_DATE('''||to_char(v_partition_max_date, 'YYYY-MM-DD')||''',''YYYY-MM-DD'')) INTO(PARTITION '|| v_part_name_prifex_in || to_char(v_partition_max_date,'YYYYMM') || ' ' || v_field_tablespace_name_in || ' TABLESPACE ' || v_tablespace_name || ', PARTITION '||v_split_partition_source_in||')'; dbms_output.put_line('Added Partition ' || i || '=' || v_SqlExec); DBMS_Utility.Exec_DDL_Statement(v_SqlExec); v_partition_max_date := add_months(v_partition_max_date,v_interver); i:= i + 1; end loop; v_count := v_count + i; dbms_output.put_line('Added Partition Count:' || v_count); commit; Exception when OTHERS then --ReturnValue:=-1003; dbms_output.put_line('The SQLCode is: '||SQLCODE); dbms_output.put_line('The SQLERRM is: '||SQLERRM); end SP_Split_Partition;
2. 创建调度分离分区的存储过程
CREATE OR REPLACE Procedure SP_Call_SP_Split_Partition is begin update Article_Detail t set t.transfer_done_time=t.extracted_time where t.transfer_done_time is null; commit; dbms_stats.gather_table_stats('WDM_APP','ARTICLE_DETAIL',partname=>'ARTICLE_DETAIL_OTHERS'); SP_Split_Partition( 'ARTICLE_DETAIL', 'ARTICLE_DETAIL_', 'ARTICLE_DETAIL_OTHERS', 'WM_ARTICLE_DETAIL', 'LOB (CONTENT_HTML) store as SECUREFILE (TABLESPACE wm_article_detail_clob)', 3 ); SP_Split_Partition( 'ARTICLE_DATA', 'ARTICLE_DATA_', 'ARTICLE_DATA_OTHERS', 'WM_ARTICLE_DATA', '', 3 ); Exception when OTHERS then --ReturnValue:=-1003; dbms_output.put_line('The SQLCode is: '||SQLCODE); dbms_output.put_line('The SQLERRM is: '||SQLERRM); end SP_Call_SP_Split_Partition;
3. 创建相应的JOB, 并执行.
CREATE OR REPLACE PROCEDURE Job_SP_Call_SP_Split_Partition AS JobNo user_jobs.job%TYPE; BEGIN begin dbms_job.submit(JobNo, 'begin SP_Call_SP_Split_Partition; end;', SYSDATE+1/1440,'TRUNC(SYSDATE+15)'); COMMIT; end; END; / call Job_SP_Call_SP_Split_Partition();