海量資料處理_表分割槽(分割槽自動維護與歷史分割槽歸檔)

1,分割槽自動維護
CREATE OR REPLACE PACKAGE partition_managment IS

  /*======================================================*/
  /* 增加分割槽                                           */
  /*======================================================*/
  PROCEDURE add_partition(p_tab_name IN VARCHAR2);

  /*======================================================*/
  /* 刪除分割槽                                           */
  /*======================================================*/
  PROCEDURE drop_partition(p_tab_name IN VARCHAR2);

END partition_managment;
/

CREATE OR REPLACE PACKAGE BODY partition_managment IS

  PROCEDURE log(p_comm IN VARCHAR2, p_type IN VARCHAR2) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    /*INSERT INTO CLEAN_OUT_LOG
          (WHEN, COMM, TYPE)
        VALUES
          (SYSDATE, P_COMM, P_TYPE);
        COMMIT;
    */
    dbms_output.put_line('info:' || p_comm || '  type:' || p_type);
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      dbms_output.put_line('ERROR IN partition_management.LOG');
  END log;

  /*======================================================*/
  /* 增加分割槽,增加新的分割槽                              */
  /*======================================================*/
  PROCEDURE add_partition(p_tab_name IN VARCHAR2) IS
    l_ddl            VARCHAR2(200);
    l_partition_name VARCHAR2(200);
    l_split_date     VARCHAR2(8);
    l_partition_num  INT;
  BEGIN
    --get partition name
    SELECT to_char(add_months(SYSDATE, 2), 'YYYYMM') || '01',
           'P' || to_char(add_months(SYSDATE, 1), 'YYYYMM')
      INTO l_split_date, l_partition_name
      FROM dual;
 
    --does it exist?
    SELECT COUNT(*)
      INTO l_partition_num
      FROM user_tab_partitions
     WHERE table_name = p_tab_name
       AND partition_name = l_partition_name;
    IF l_partition_num > 0 THEN
      log('partition ' || l_partition_name || ' 已經存在,skip', 'I');
      RETURN;
    END IF;
 
    --create
    BEGIN
      l_ddl := 'alter table ' || p_tab_name ||
               ' split partition pmax at (to_date(''' || l_split_date ||
               ''',''YYYYMMDD'')) into (' || ' partition ' ||
               l_partition_name || ', partition pmax)';
      log('add new partition,ddl:' || l_ddl, 'I');
      EXECUTE IMMEDIATE l_ddl;
      log('add new partition ok,ddl:' || l_ddl, 'I');
    EXCEPTION
      WHEN OTHERS THEN
        log('Error when create a new partition:' ||
            substr(dbms_utility.format_error_stack, 1, 900),
            'E');
    END;
  END add_partition;

  /*======================================================*/
  /* 刪除分割槽,每呼叫一次,刪除最早的一個超過13個月的分割槽   */
  /*======================================================*/
  PROCEDURE drop_partition(p_tab_name IN VARCHAR2) IS
    l_ddl            VARCHAR2(200);
    l_partition_name VARCHAR2(200);
  BEGIN
    --find the oldest partition
    BEGIN
      SELECT partition_name
        INTO l_partition_name
        FROM (SELECT *
                FROM user_tab_partitions
               WHERE table_name = p_tab_name
               ORDER BY partition_name)
       WHERE rownum < 2;
    EXCEPTION
      WHEN OTHERS THEN
        log('No partition found for table:' || p_tab_name, 'W');
    END;
 
    --is older than 13 month?
    BEGIN
      IF (add_months(SYSDATE, -13) >
         to_date(substr(l_partition_name, 2) || '01', 'YYYYMMDD')) THEN
        l_ddl := 'alter table ' || p_tab_name || ' drop partition ' ||
                 l_partition_name;
        log('drop partition,ddl:' || l_ddl, 'I');
        EXECUTE IMMEDIATE l_ddl;
        log('drop partition ok:' || l_ddl, 'I');
      ELSE
        log('最早的分割槽不需要刪除:' || l_partition_name, 'I');
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        log('Error when drop partition:' ||
            substr(dbms_utility.format_error_stack, 1, 900),
            'E');
    END;
 
  END drop_partition;

END partition_managment;
/

2,歷史分割槽歸檔：
--create directory:
create directory ext_local as '/local/TEST';

create directory ext_store as '/store/TEXT';

--grant privilege:
grant read,write on ext_local to test;

grant read,write on ext_store to test;

--create external table:
create table ext_200201
organization external(type oracle_dump
default directory ext_local location('ext_200201.dmp'))
as
select /*+ full(orderitem) */ * from orderitem
where shipdate >= to_date('20020101','YYYYMMDD')
and shipdate < to_date('20020201','YYYYMMDD');

--drop table
drop table ext_200201;

--cp
cp -p ext_200201.dmp /store/TEST/

--drop partition
alter table orderitem drop partition pt200201;

--restore
create table ext_200201_c(
...)
organization external(type oracle_dump
default directory ext_store location('ext_200201.dmp'));