split partition

周末在生产环境进行了SPLIT PARTITION 操作，较之前的SPLIT 实验相比，对脚本进行了相应的修改。
首先先将需要SPLIT分区的进行统计信息收集，然后将数据进行备份，再把索引变为UNUSABLE，然后进行SPLIT，再重将索引进行重建，检查是否有失效的索引（这一步很关键），最后重新收集统计信息。
和之前脚本相比，最大的区别是没有在SPLIT的时候直接更新索引，而是将操作分开来进行，先把索引变为UNUSABLE，然后SPLIT，这样做首先会加快SPLIT的进度，然后再重建索引的时候也相对可控，可以监控到具体在重建哪个索引。


EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'A',TABNAME => 'B',PARTNAME =>'ALL_FY',DEGREE => 16,METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO',GRANULARITY => 'PARTITION',CASCADE => TRUE);

--TABLE B
CREATE TABLE C.B_2015
AS 
SELECT *
FROM A.B
WHERE CREATION_DATE>=TO_DATE('2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS');

----------------------make global indexes unusable-------------------
select 'alter index '||'"'||owner||'"'||'.'||'"'||index_name||'"'||' unusable;' FROM dba_indexes
where table_name='B' and owner = 'A' and partitioned = 'NO';

----------------------split partition-------------------
ALTER TABLE A.B split partition ALL_FY at (TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) INTO (partition FY2015,partition ALL_FY)
PARALLEL 16;

ALTER TABLE A.B split partition ALL_FY at (TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) INTO (partition FY2016,partition ALL_FY)
PARALLEL 16;

ALTER TABLE A.B split partition ALL_FY at (TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) INTO (partition FY2017,partition ALL_FY)
PARALLEL 16;

ALTER TABLE A.B split partition ALL_FY at (TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) INTO (partition FY2018,partition ALL_FY)
PARALLEL 16;

ALTER TABLE A.B split partition ALL_FY at (TO_DATE(' 2020-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) INTO (partition FY2019,partition ALL_FY)
PARALLEL 16;

ALTER TABLE A.B split partition ALL_FY at (TO_DATE(' 2021-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) INTO (partition FY2020,partition ALL_FY)
PARALLEL 16;

----------------------rebuild global partitioned and non-partitioned indexes-------------------
select 'alter index '||'"'||owner||'"'||'.'||'"'||index_name||'"'||' rebuild parallel 16;' FROM (
select distinct owner, index_name  , partitioned, visibility, locality, status from  
(
        select * from 
        (
                select a.owner , a.index_name, a.partitioned, a.visibility, b.locality, c.partition_name,
                        case when (a.partitioned = 'YES' and d.status is not null) then d.status 
                        when (a.partitioned = 'YES' and c.status is not null) then c.status  
                        else a.status end status
                from dba_indexes a, dba_part_indexes b, dba_ind_partitions c, dba_ind_subpartitions d
                        where a.index_name = b.index_name(+)
                        and a.owner = b.owner(+)
                        and a.index_name = c.index_name (+)
                        and a.owner = c.index_owner (+)
                        and a.owner = d.index_owner (+)
                        and a.index_name = d.index_name (+)
                        and a.table_name = 'B'  
                        and (a.partitioned = 'NO')
        ) where status = 'UNUSABLE'
)
)
;

----------------------rebuild global partitioned and non-partitioned indexes-------------------
select 'alter index '||'"'||owner||'"'||'.'||'"'||index_name||'"'||' rebuild partition '|| partition_name ||' parallel 16;' FROM 
(
        select * from 
        (
                select a.owner , a.index_name, a.partitioned, a.visibility, b.locality, c.partition_name,
                        case when (a.partitioned = 'YES' and d.status is not null) then d.status 
                        when (a.partitioned = 'YES' and c.status is not null) then c.status  
                        else a.status end status
                from dba_indexes a, dba_part_indexes b, dba_ind_partitions c, dba_ind_subpartitions d
                        where a.index_name = b.index_name(+)
                        and a.owner = b.owner(+)
                        and a.index_name = c.index_name (+)
                        and a.owner = c.index_owner (+)
                        and a.owner = d.index_owner (+)
                        and a.index_name = d.index_name (+)
                        and a.table_name = 'B'  
                        and (a.partitioned = 'YES')
        ) where status = 'UNUSABLE'
)
;

----------------------change the index to no parallel-------------------
select 'alter index '||'"'||owner||'"'||'.'||'"'||index_name||'"'||' PARALLEL 1;' FROM dba_indexes
where partitioned ='NO' and table_name='B' and owner='A';

----------------------check index-------------------
select * FROM dba_indexes
where partitioned ='NO' and table_name='B' 
---and owner='A' 
and status !='VALID';

----------------------gather stats-------------------
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'A',TABNAME => 'B',DEGREE => 16,METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO',granularity => 'ALL',CASCADE => TRUE);

充一个，对于range分区（特别是时间range分区，又没有用interval的），往往要split max分区以便生成新的分区。如果max分区没有数据，split的时候照理说会很快的，但实际并非如此。因为max分区往往没有统计信息，需要先手工收集一下（指定分区，指定粒度与级联选项）
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
    OWNNAME => 'XXXDATA',
    TABNAME => 'XXX_TABLE',
    PARTNAME => 'PART_MAX',
    GRANULARITY => 'PARTITION',
    CASCADE => FALSE
);
END;


然后再做split，就很快了

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)',
  );
  SP_Split_Partition(
  'ARTICLE_DATA',
  'ARTICLE_DATA_',
  'ARTICLE_DATA_OTHERS',
  'WM_ARTICLE_DATA',
  '',
  );
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();