About Oracle 分区(Partitions)-存储过程(Procedure)

最新推荐文章于 2021-04-04 06:10:55 发布

ddttoop

最新推荐文章于 2021-04-04 06:10:55 发布

阅读量581

点赞数

分类专栏： Oracle DataWarehouse

本文链接：https://blog.csdn.net/weixin_45883933/article/details/105047351

版权

Oracle 同时被 2 个专栏收录

32 篇文章 0 订阅

订阅专栏

DataWarehouse

22 篇文章 0 订阅

订阅专栏

Oracle有关存储过程处理表分区

Procedure:alter_table_partition_nameE

CREATE OR REPLACE PROCEDURE ALTER_TABLE_PARTITION_NAME
AS
V_EXECUTE_SQL VARCHAR2(200);
CURSOR PARTITION_STR IS SELECT A.TABLE_NAME,PARTITION_NAME FROM USER_TAB_PARTITIONS A JOIN ADD_TABLE_PARTITION_CONF B 
    ON A.TABLE_NAME = B.TABLE_NAME
    WHERE PARTITION_NAME LIKE '%PART%'
    AND  PARTITION_TYPE = 'M'
    AND LENGTH(PARTITION_NAME) = 9;
      BEGIN 
      FOR PARTITION_RESULT IN PARTITION_STR LOOP 
        V_EXECUTE_SQL := 'ALTER TABLE '||PARTITION_RESULT.TABLE_NAME||' rename partition '||PARTITION_RESULT.PARTITION_NAME|| ' to PART_20'||SUBSTR(PARTITION_RESULT.PARTITION_NAME,6,4);
        --INSERT INTO TMP1 VALUES(PARTITION_RESULT.TABLE_NAME,V_EXECUTE_SQL);
        --COMMIT;
        EXECUTE IMMEDIATE V_EXECUTE_SQL;
    END LOOP;   
END ALTER_TABLE_PARTITION_NAME;

Procedure:add_table_partitions_manul

CREATE OR REPLACE PROCEDURE ADD_TABLE_PARTITIONS_MANUL --表分区名称为：PART_当前月（YYMM） 分区条件为  小于每个月第一天8点
AS
V_CURRENT_DATE VARCHAR2(20);
V_PARTITION_DATE VARCHAR2(20);
V_EXECUTE_SQL VARCHAR2(200);
CURSOR CUR_STR IS 
SELECT TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,PARTITION_DATE,SYSDATE_YYYYMM FROM (
SELECT TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,'20'||SUBSTR(PARTITION_NAME,6,4) AS PARTITION_DATE,TO_CHAR(ADD_MONTHS(SYSDATE,5),'yyyymm') AS SYSDATE_YYYYMM FROM ( --TO_CHAR(ADD_MONTHS(SYSDATE,2) 对应数字实现创建更多分区
SELECT P.TABLE_NAME,MAX(PARTITION_NAME) PARTITION_NAME,MAX(P.TABLESPACE_NAME) TABLESPACE_NAME FROM USER_TAB_PARTITIONS P
WHERE PARTITION_NAME LIKE 'PART%' AND LENGTH(PARTITION_NAME) = 9 --AND TABLE_NAME ='DWT_EQP_STATE_A'
AND TABLE_NAME NOT IN (SELECT DISTINCT TABLE_NAME FROM USER_TAB_PARTITIONS WHERE PARTITION_NAME LIKE '%SYS%')
GROUP BY P.TABLE_NAME
)) WHERE SYSDATE_YYYYMM >= PARTITION_DATE;
BEGIN 
   FOR CUR_RESULT IN CUR_STR LOOP 
       BEGIN 
             V_CURRENT_DATE:= CUR_RESULT.SYSDATE_YYYYMM;
             V_PARTITION_DATE:= CUR_RESULT.PARTITION_DATE;
                WHILE V_PARTITION_DATE <= V_CURRENT_DATE
                    LOOP
                          V_PARTITION_DATE:= TO_CHAR(ADD_MONTHS(TO_DATE(V_PARTITION_DATE,'yyyymm'),1),'yyyymm');
                          V_EXECUTE_SQL:='ALTER TABLE '||CUR_RESULT.TABLE_NAME||' add partition PART_'||TO_CHAR(TO_DATE(V_PARTITION_DATE,'yyyymm'),'yymm')|| ' values less than(to_date('''||TO_CHAR(ADD_MONTHS(TO_DATE(V_PARTITION_DATE,'YYYY-MM'),1),'YYYY-MM')||'-01 08:00:00'''||','||'''YYYY-MM-DD HH24:MI:SS'''||')) TABLESPACE '||CUR_RESULT.TABLESPACE_NAME;
                         -- INSERT INTO TMP1 VALUES(V_EXECUTE_SQL);
                          EXECUTE IMMEDIATE V_EXECUTE_SQL;
                          --COMMIT;
                    END LOOP;
       END;
END LOOP;
END ADD_TABLE_PARTITIONS_MANUL;

Procedure:pro_drop_table_partition

CREATE OR REPLACE PROCEDURE PRO_DROP_TABLE_PARTITION(  --删除指定表的历史分区，默认保留30个分区 
TAB_NAME IN VARCHAR2,
INPUT_NUM IN NUMBER := 30
)
AS
V_TRUNC_SQL VARCHAR2(200);
V_DROP_SQL VARCHAR2(200);
CURSOR CUR_RESULTS IS
SELECT TABLE_NAME,PARTITION_NAME FROM (
SELECT TABLE_NAME,
       PARTITION_NAME,
       ROW_NUMBER() OVER(PARTITION BY TABLE_NAME ORDER BY PARTITION_POSITION DESC) RK
  FROM USER_TAB_PARTITIONS
 WHERE TABLE_NAME = TAB_NAME AND PARTITION_POSITION <> 1
 ) WHERE RK > INPUT_NUM;
 BEGIN
 FOR CUR_ROW IN CUR_RESULTS
 LOOP
 V_TRUNC_SQL:= 'alter table '|| CUR_ROW.TABLE_NAME ||' truncate partition '||CUR_ROW.PARTITION_NAME||' drop storage';   --回收分区表分区空间
 V_DROP_SQL:= 'alter table '|| CUR_ROW.TABLE_NAME ||' DROP PARTITION '||CUR_ROW.PARTITION_NAME;   --删除分区表指定分区

 --INSERT INTO CM_TMP_TEST2 VALUES(TAB_NAME,CUR_ROW.TABLE_NAME,CUR_ROW.PARTITION_NAME,INPUT_NUM,V_TRUNC_SQL,V_DROP_SQL);
 EXECUTE IMMEDIATE V_TRUNC_SQL;
 EXECUTE IMMEDIATE V_DROP_SQL;

 --COMMIT;

 END LOOP;
END PRO_DROP_TABLE_PARTITION;

Procedure:pro_del_add_partition_by_day

CREATE OR REPLACE PROCEDURE PRO_DEL_ADD_PARTITION_BY_DAY
 AS
 /***************************************************
   ** 功能：根据配置表增加分区
   ****************************************************/
   V_START_DATE  VARCHAR2(30);            -- 存放循环起始日期(每天早八点到晚八点为一天)
   V_END_DATE    VARCHAR2(30);            -- 存放循环截止日期(每天早八点到晚八点为一天)
   V_TABLE_NAME      VARCHAR2(100);       -- 表名
   V_PARTITION_NAME  VARCHAR2(30);    -- 表分区名
   V_PARTITION_EXIST VARCHAR2(30);      -- 判断表分区是否已经存在
   V_EXEC_SQL       VARCHAR2(200);   -- 存放将要执行的SQL语句

CURSOR CUR_STR IS
  SELECT TABLE_NAME,START_DATE,END_DATE,TABLESPACE_NAME FROM ADD_TABLE_PARTITION_CONF WHERE PARTITION_TYPE = 'D';

  BEGIN
    FOR CUR_RESULT IN CUR_STR LOOP

      V_START_DATE  := TO_CHAR(SYSDATE,'yyyymm')||'01080000';
      V_END_DATE    := TO_CHAR(ADD_MONTHS(SYSDATE,2),'yyyymm')||'01080000';
      V_TABLE_NAME  := UPPER(CUR_RESULT.TABLE_NAME);

       SELECT NVL(TO_CHAR(MAX(HIGH_VALUE_IN_DATE_FORMAT),'yyyymmddhh24miss'), V_END_DATE) INTO V_PARTITION_EXIST FROM (
              SELECT TABLE_NAME,
          PARTITION_NAME,
          TO_DATE (
          TRIM (
            '''' FROM REGEXP_SUBSTR (
                EXTRACTVALUE (
                  DBMS_XMLGEN.
                  GETXMLTYPE (
                  'select high_value from user_tab_partitions where table_name='''
                  || TABLE_NAME
                  || ''' and partition_name = '''
                  || PARTITION_NAME
                  || ''''),
                  '//text()'),
                '''.*?''')),
          'syyyy-mm-dd hh24:mi:ss')
          HIGH_VALUE_IN_DATE_FORMAT
          FROM USER_TAB_PARTITIONS
          WHERE TABLE_NAME = V_TABLE_NAME
          ) WHERE HIGH_VALUE_IN_DATE_FORMAT >= TO_DATE(V_START_DATE,'yyyymmdd hh24:mi:ss');

      WHILE V_START_DATE <= V_END_DATE LOOP

        IF V_PARTITION_EXIST < V_START_DATE THEN
        V_PARTITION_NAME := 'PART_'||TO_CHAR(TO_DATE(V_START_DATE,'yyyymmdd hh24:mi:ss') + 1,'yyyymmdd');
        V_EXEC_SQL := 'ALTER TABLE '||V_TABLE_NAME||' add partition '||V_PARTITION_NAME|| ' values less than(to_date('||V_START_DATE||','||'''yyyymmddhh24miss'''||')) ';
       -- INSERT INTO TMP1 VALUES(V_EXEC_SQL,V_PARTITION_EXIST);
       -- COMMIT;
        EXECUTE IMMEDIATE V_EXEC_SQL;
        END IF;

         V_START_DATE := TO_CHAR(TO_DATE(V_START_DATE,'yyyymmdd hh24:mi:ss')+1,'yyyymmddhh24miss');

    END LOOP;
  END LOOP;
END PRO_DEL_ADD_PARTITION_BY_DAY;

Procedure:pro_del_add_partition_by_month

CREATE OR REPLACE PROCEDURE PRO_DEL_ADD_PARTITION_BY_MONTH
AS
/***************************************************
  ** 功能：根据配置表增加分区 by month
  ****************************************************/
  v_start_date  VARCHAR2(30);            -- 存放循环起始日期(每天早八点到晚八点为一天)
  v_end_date    VARCHAR2(30);            -- 存放循环截止日期(每天早八点到晚八点为一天)
  v_table_name      VARCHAR2(100);       -- 表名
  v_partition_name  VARCHAR2(30);    -- 表分区名
  v_partition_exist VARCHAR2(30);      -- 判断表分区是否已经存在
  v_exec_sql       VARCHAR2(200);   -- 存放将要执行的SQL语句

cursor cur_str is
 select table_name,start_date,end_date,tablespace_name from add_table_partition_conf where partition_type = 'M';

 begin
   for cur_result in cur_str loop

    -- v_start_date  := to_char(sysdate,'yyyymmdd')||'080000';
     v_start_date  := to_char(sysdate,'yyyymm')||'01080000';
     v_end_date    := to_char(add_months(sysdate,5),'yyyymm')||'01080000';
     v_table_name  := UPPER(cur_result.table_name);

      select nvl(to_char(max(high_value_in_date_format),'yyyymmddhh24miss'), v_end_date) into v_partition_exist from (
             select table_name,
         partition_name,
         to_date (
         trim (
           '''' from regexp_substr (
               extractvalue (
                 dbms_xmlgen.
                 getxmltype (
                 'select high_value from user_tab_partitions where table_name='''
                 || table_name
                 || ''' and partition_name = '''
                 || partition_name
                 || ''''),
                 '//text()'),
               '''.*?''')),
         'syyyy-mm-dd hh24:mi:ss')
         high_value_in_date_format
         from user_tab_partitions
         where table_name = v_table_name
         ) where high_value_in_date_format >= to_date(v_start_date,'yyyymmdd hh24:mi:ss');
     WHILE v_start_date <= v_end_date LOOP
       IF v_partition_exist < v_start_date THEN
       v_partition_name := 'PART_'||to_char(add_months(to_date(v_start_date,'yyyymmdd hh24:mi:ss'),1),'yyyymm');
         v_exec_sql := 'ALTER TABLE '||v_table_name||' add partition '||v_partition_name|| ' values less than(to_date('||v_start_date||','||'''yyyymmddhh24miss'''||'))';
      -- INSERT INTO TMP1 VALUES(v_exec_sql,v_partition_exist);
      -- commit;
       EXECUTE IMMEDIATE v_exec_sql;
       END IF;
        v_start_date := to_char(add_months(to_date(v_start_date,'yyyymmdd hh24:mi:ss'),1),'yyyymmddhh24miss');

   END LOOP;
 END LOOP;
END PRO_DEL_ADD_PARTITION_BY_MONTH;

ddttoop

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
About Oracle 分区(Partitions)-存储过程(Procedure)

Oracle有关存储过程处理表分区Procedure:alter_table_partition_nameECREATE OR REPLACE PROCEDURE ALTER_TABLE_PARTITION_NAMEASV_EXECUTE_SQL VARCHAR2(200);CURSOR PARTITION_STR IS SELECT A.TABLE_NAME,PARTITION_N...
复制链接

扫一扫