分区表中多个分区的合并

/*显示当前用户所有分区表的信息 */
select * from user_part_tables;

/* 显示当前用户所有分区表的详细分区信息* /
select * from user_tab_partitions;

/* 执行下列语句会产生ORA-14275错误 */
alter table PARTITIONED_TABLE merge partitions part_1,part_2 into partition part_1;
ORA-14275: cannot reuse lower-bound partition as resulting partition;
ORA-14275: 不能将下界分区作为结果分区重用

/* 执行分区表中多个分区的合并 */
DECLARE
   l_first   VARCHAR (30);

BEGIN
   SELECT partition_name
     INTO l_first
     FROM (SELECT   ROWNUM AS sn, partition_name
               FROM user_tab_partitions
              WHERE table_name = 'RANGE_EXAMPLE01'
           ORDER BY partition_name) a
    WHERE a.sn = 1;

   FOR x IN (SELECT partition_name
               FROM (SELECT   ROWNUM AS sn, partition_name
                         FROM user_tab_partitions
                        WHERE table_name = 'RANGE_EXAMPLE01'
                     ORDER BY partition_name) a
              WHERE a.sn > 1)
   LOOP
      execute immediate   'alter table range_example01 merge partitions '
                           || l_first
                           || ','
                           || x.partition_name
                           || ' into partition '
                           || x.partition_name;
      l_first:= x.partition_name;              
   END LOOP;
END;

/* 产生分区表实现多个分区合并的代码 */
DECLARE
   l_first   VARCHAR (30);

BEGIN
   SELECT partition_name
     INTO l_first
     FROM (SELECT   ROWNUM AS sn, partition_name
               FROM user_tab_partitions
              WHERE table_name = 'RANGE_EXAMPLE01'
           ORDER BY partition_name) a
    WHERE a.sn = 1;

   FOR x IN (SELECT partition_name
               FROM (SELECT   ROWNUM AS sn, partition_name
                         FROM user_tab_partitions
                        WHERE table_name = 'RANGE_EXAMPLE01'
                     ORDER BY partition_name) a
              WHERE a.sn > 1)
   LOOP
      DBMS_OUTPUT.put_line
                          (   'alter table range_example01 merge partitions '
                           || l_first
                           || ','
                           || x.partition_name
                           || ' into partition '
                           || x.partition_name
                           || ';'
                          );
      l_first:= x.partition_name;
   END LOOP;
END;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9932141/viewspace-667678/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9932141/viewspace-667678/