Oracle分区过程及建立job自动添加删除分区脚本

最新推荐文章于 2024-04-20 18:40:30 发布

yzy85

最新推荐文章于 2024-04-20 18:40:30 发布

阅读量3k

点赞数

本文链接：https://blog.csdn.net/yzy85/article/details/51660537

版权

PLSQL Developer工具中存储过程测试方法：建立存储过程后，在存储过程名称上点击右键，选中”添加调试信息“，然后再次点击右键，选择“测试”，工具会自动打开新窗口，然后即可进入单步调试状态；

1、新建分区表及根据实际应用情况建立本地索引或者全局索引，分区后，查询语句条件要包含分区字段，否则导致每个分区都会扫描，消耗更大

-- Create table
create table EMS_LAYERFORMULADATA2
(
  id                 NUMBER,
  formulano          NVARCHAR2(50) not null,
  acquisitiontime    NVARCHAR2(20) not null,
  totalvalue         NVARCHAR2(20),
  duration           NVARCHAR2(20),
  acquisitiontimeend NVARCHAR2(20),
  flagid             NUMBER
)
partition by range (acquisitiontimeend)
   (
   partition EMS_LAYERFORMULADATA_201506 values  less than ('2015-07')   tablespace HTEMS,
   partition EMS_LAYERFORMULADATA_201507 values  less than ('2015-08')   tablespace HTEMS,
   partition EMS_LAYERFORMULADATA_201508 values  less than ('2015-09')   tablespace HTEMS,
   partition EMS_LAYERFORMULADATA_201509 values  less than ('2015-10')   tablespace HTEMS,
   partition EMS_LAYERFORMULADATA_201510 values  less than ('2015-11')   tablespace HTEMS,
   partition EMS_LAYERFORMULADATA_201511 values  less than ('2015-12')   tablespace HTEMS,
   partition EMS_LAYERFORMULADATA_201512 values  less than ('2016-01')   tablespace HTEMS,
   partition EMS_LAYERFORMULADATA_201601 values  less than ('2016-02')   tablespace HTEMS,
   partition EMS_LAYERFORMULADATA_201602 values  less than ('2016-03')   tablespace HTEMS,
   partition EMS_LAYERFORMULADATA_201603 values  less than ('2016-04')   tablespace HTEMS,
   partition EMS_LAYERFORMULADATA_201604 values  less than ('2016-05')   tablespace HTEMS,
   partition EMS_LAYERFORMULADATA_201605 values  less than ('2016-06')   tablespace HTEMS,
   partition EMS_LAYERFORMULADATA_201606 values  less than ('2016-07')   tablespace HTEMS,
   partition EMS_LAYERFORMULADATA_201607 values  less than ('2016-08')   tablespace HTEMS,
   partition EMS_LAYERFORMULADATA_201608 values  less than ('2016-09')   tablespace HTEMS,
   partition EMS_LAYERFORMULADATA_201609 values  less than ('2016-10')   tablespace HTEMS,
   partition EMS_LAYERFORMULADATA_201610 values  less than ('2016-11')   tablespace HTEMS,
   partition EMS_LAYERFORMULADATA_201611 values  less than ('2016-12')   tablespace HTEMS,
   partition EMS_LAYERFORMULADATA_201612 values  less than ('2017-01')   tablespace HTEMS
   );
-- Create/Recreate indexes 
create index INDEX_FORMULANO2 on EMS_LAYERFORMULADATA2 (FORMULANO)

2、向分区表中添加模拟数据

DECLARE
temp nvarchar2(19);
temp2 nvarchar2(19);
BEGIN
temp:= '2015-01-01 00:00:00';
temp2:= '2015-01-02 00:00:00';
WHILE temp<'2017-01' LOOP
  insert into EMS_LAYERFORMULADATA2 values(null,'Test_001',temp,'100','',temp2,'');
DBMS_OUTPUT.PUT_LINE(temp);
temp:=to_char((to_date(temp,'yyyy-mm-dd hh24:mi:ss')+1),'yyyy-mm-dd hh24:mi:ss');
temp2:=to_char((to_date(temp2,'yyyy-mm-dd hh24:mi:ss')+1),'yyyy-mm-dd hh24:mi:ss');
END LOOP;
DBMS_OUTPUT.PUT_LINE(temp);
END;

declare
temp varchar2(20):='aaaa';
i number;
begin
   for i in 1..100 loop
      insert into e(a,b) values(i,temp);
   end loop;
end;

查询分区数据：

select * from EMS_LAYERFORMULADATA2BK  partition(EMS_LAYERFORMULADATA2BK_201606)

</pre><p></p><p>3、oracle利用切换分区处理分区数据的转移，切换时需要一个中间表，中间表表结构需要和源表、目的表相同，切换到的目的表的限制可以比较少，比如源表有主键，目的表可以没有主键，但是目的表有主键，源表一定要有主键：</p><p></p><pre name="code" class="sql">alter table EMS_LAYERFORMULADATA2 exchange partition  EMS_LAYERFORMULADATA2_201606 with table EMS_LAYERFORMULADATA2EXCHANGE ;
alter table EMS_LAYERFORMULADATA2BK exchange partition  EMS_LAYERFORMULADATA2BK_201606 with table EMS_LAYERFORMULADATA2EXCHANGE ;

4、全局分区索引每次分区删除、转移、切换后需要重建，分区切换时切换分区的本地索引也需要重建，删除分区不需要重建本地索引

 alter index IX_EMS_DATAACQUISITION2BK_TIME rebuild partition EMS_DATAACQUISITION2BK_201606 nologging; 
 alter index PK_EMS_DATAACQUISITION22 rebuild  nologging;

5、处理分区后查看索引是否有效安全

select index_name, partition_name, status  
  from user_ind_partitions  
 where status = 'UNUSABLE'  
union all  
select index_name, '' as partition_name, status  
  from user_indexes  
 where status = 'UNUSABLE';

6、自动增加、删除、切换分区的sql存储过程脚本

CREATE OR REPLACE PROCEDURE processPartition IS
  nextPar   nvarchar2(6);
  sixMonthBeforePar nvarchar2(6);
  twoYearBeforePar   nvarchar2(6);
  nextParMonth   nvarchar2(7);
  isExist number;
  v_string varchar2(200); --字符串变量
BEGIN
  select to_char(add_months(sysdate,1),'YYYYMM') into nextPar from dual;--201607
  select to_char(add_months(sysdate,2),'YYYY-MM') into nextParMonth from dual;--2016-08
  select to_char(add_months(sysdate,-6),'YYYYMM') into sixMonthBeforePar from dual;
  select to_char(add_months(sysdate,-24),'YYYYMM') into twoYearBeforePar from dual;


  --处理EMS_LAYERFORMULADATA分区情况
  SELECT count(1) into isExist  FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='EMS_LAYERFORMULADATA' and PARTITION_NAME='EMS_LAYERFORMULADATA_'||nextPar;
  if isExist=0 then
    v_string:= 'alter table EMS_LAYERFORMULADATA add partition ' || 'EMS_LAYERFORMULADATA_' || nextPar ||'  values less than ('''|| nextParMonth|| ''')';
    EXECUTE IMMEDIATE v_string;
  end if;
  
  SELECT count(1) into isExist  FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='EMS_LAYERFORMULADATABK' and PARTITION_NAME='EMS_LAYERFORMULADATABK_'||nextPar;
  if isExist=0 then
    v_string:='alter table EMS_LAYERFORMULADATABK add partition ' || 'EMS_LAYERFORMULADATABK_' || nextPar ||'  values less than ('''|| nextParMonth|| ''')';
    EXECUTE IMMEDIATE v_string;
  end if;


  SELECT count(1) into isExist  FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='EMS_LAYERFORMULADATA' and PARTITION_NAME='EMS_LAYERFORMULADATA_'||sixMonthBeforePar;
  if isExist=1 then
    v_string:='alter table EMS_LAYERFORMULADATA exchange partition  EMS_LAYERFORMULADATA_'||sixMonthBeforePar || ' with table EMS_LAYERFORMULADATAEXCHANGE';
    EXECUTE IMMEDIATE v_string;
    v_string:='alter index IDX_EMS_FORMULADATA_NO rebuild partition EMS_LAYERFORMULADATA_'||sixMonthBeforePar||' nologging ';
    EXECUTE IMMEDIATE v_string;
    v_string:='alter index IDX_EMS_FORMULADATA_TIME rebuild partition EMS_LAYERFORMULADATA_'||sixMonthBeforePar||' nologging ';
    EXECUTE IMMEDIATE v_string;
    v_string:='alter table EMS_LAYERFORMULADATABK exchange partition  EMS_LAYERFORMULADATABK_'||sixMonthBeforePar || ' with table EMS_LAYERFORMULADATAEXCHANGE';
    EXECUTE IMMEDIATE v_string;
    v_string:='alter index IDX_EMS_FORMULADATABK_NO rebuild partition EMS_LAYERFORMULADATABK_'||sixMonthBeforePar||' nologging ';
    EXECUTE IMMEDIATE v_string;
    v_string:='alter index IDX_EMS_FORMULADATABK_TIME rebuild partition EMS_LAYERFORMULADATABK_'||sixMonthBeforePar||' nologging ';
    EXECUTE IMMEDIATE v_string;
    --EXECUTE IMMEDIATE 'alter table  EMS_LAYERFORMULADATA drop partition EMS_LAYERFORMULADATA_'||sixMonthBeforeParMonth;
  end if;


  SELECT count(1) into isExist  FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='EMS_LAYERFORMULADATABK' and PARTITION_NAME='EMS_LAYERFORMULADATABK_'||twoYearBeforePar;
  if isExist=1 then
    v_string:='alter table  EMS_LAYERFORMULADATABK drop partition EMS_LAYERFORMULADATABK_'||twoYearBeforePar;
    EXECUTE IMMEDIATE v_string;
  end if;


  --处理EMS_DATAACQUISITION分区情况
  SELECT count(1) into isExist  FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='EMS_DATAACQUISITION' and PARTITION_NAME='EMS_DATAACQUISITION_'||nextPar;
  if isExist=0 then
    v_string:= 'alter table EMS_DATAACQUISITION add partition ' || 'EMS_DATAACQUISITION_' || nextPar ||'  values less than ('''|| nextParMonth|| ''')';
    EXECUTE IMMEDIATE v_string;
  end if;
  
  SELECT count(1) into isExist  FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='EMS_DATAACQUISITIONBK' and PARTITION_NAME='EMS_DATAACQUISITIONBK_'||nextPar;
  if isExist=0 then
    v_string:='alter table EMS_DATAACQUISITIONBK add partition ' || 'EMS_DATAACQUISITIONBK_' || nextPar ||'  values less than ('''|| nextParMonth|| ''')';
    EXECUTE IMMEDIATE v_string;
  end if;
  
  SELECT count(1) into isExist  FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='EMS_DATAACQUISITION' and PARTITION_NAME='EMS_DATAACQUISITION_'||sixMonthBeforePar;
  if isExist=1 then
    v_string:='alter table EMS_DATAACQUISITION exchange partition  EMS_DATAACQUISITION_'||sixMonthBeforePar || ' with table EMS_DATAACQUISITIONEXCHANGE';
    EXECUTE IMMEDIATE v_string;
    v_string:='alter index IDX_EMS_DATAACQUISITION_TIME rebuild partition EMS_DATAACQUISITION_'||sixMonthBeforePar||' nologging ';
    EXECUTE IMMEDIATE v_string; 
    v_string:='alter index  PK_EMS_DATAACQUISITION rebuild nologging ';
    EXECUTE IMMEDIATE v_string;    
    v_string:='alter table EMS_DATAACQUISITIONBK exchange partition  EMS_DATAACQUISITIONBK_'||sixMonthBeforePar || ' with table EMS_DATAACQUISITIONEXCHANGE';
    EXECUTE IMMEDIATE v_string;
    v_string:='alter index IDX_EMS_DATAACQUISITIONBK_TIME rebuild partition EMS_DATAACQUISITIONBK_'||sixMonthBeforePar||' nologging ';
    EXECUTE IMMEDIATE v_string; 
    --EXECUTE IMMEDIATE 'alter table  EMS_LAYERFORMULADATA drop partition EMS_LAYERFORMULADATA_'||sixMonthBeforeParMonth;
  end if;
  
  SELECT count(1) into isExist  FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='EMS_DATAACQUISITIONBK' and PARTITION_NAME='EMS_DATAACQUISITIONBK_'||twoYearBeforePar;
  if isExist=1 then
    v_string:='alter table  EMS_DATAACQUISITIONBK drop partition EMS_DATAACQUISITIONBK_'||twoYearBeforePar;
    EXECUTE IMMEDIATE v_string;
  end if;


EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLCODE);
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
    DBMS_OUTPUT.PUT_LINE('Exception happened,data was rollback');
    ROLLBACK;
END;

7、建立job，定时自动执行存储过程

DECLARE
    v_job NUMBER;
BEGIN
    DBMS_JOB.SUBMIT(v_job,'processacquisitionPartition;',TRUNC(sysdate)+1/24,'TRUNC(sysdate)+1+1/24');     --- 每天凌晨1点执行一次, 从今天开始
    commit;
END;

8、学习过程使用到的资料：

http://www.2cto.com/database/201301/185425.html

http://www.blogjava.net/tjyhy590/archive/2014/05/10/413484.html

http://blog.itpub.net/7199859/viewspace-203970/

http://www.blogjava.net/tjyhy590/archive/2014/05/10/413484.html

http://www.2cto.com/database/201201/116414.html