oracle 复合分区相关操作

-- 创建和修改子分区的方法(range-list)
select partition_name,subpartition_name,tablespace_name
from user_tab_subpartitions where table_name='TM_CI_USER_SALARY_M1';

-- 相关查询主分区表和子分区表

all_tab_partitions
all_tab_subpartitions

--实例操作 

create table scott.TM_CI_USER_SALARY_M1
(
  statis_month number(6),
  area_code    varchar(4),
  empno        number(4),
  ename        varchar2(10),
  job          varchar2(9),
  mgr          number(4),
  hiredate     date,
  sal          number(7,2),
  comm         number(7,2),
  deptno       number(2)
)
partition by range(statis_month) subpartition by list(area_code)
(partition salary_m_200909 values less than (200909) tablespace tbs_cb1
(subpartition area_code_11 values ('11') tablespace tbs_cb1)
)

ALTER   TABLE   diving   MODIFY   PARTITION   locations_us  
              ADD   SUBPARTITION   us_locs5   TABLESPACE   us1;  
 
-- 增加子分区主法
alter table  scott.tm_ci_user_salary_m1 modify partition salary_m_200909 add subpartition area_code_12 values ('12')
-- 删除子分区方法
alter table  scott.tm_ci_user_salary_m1  drop subpartition area_code_12 ;
-- 戴断子分区方法
alter table  scott.tm_ci_user_salary_m1  truncate subpartition area_code_12 ;
-- 新增加主分区及子分区的方法
alter table  scott.tm_ci_user_salary_m1 add partition salary_m_200911 values less than (200911)( subpartition area_code_13 values ('13') )  ;
-- 给用户调试程序权限
grant debug connect session to scott;

-- 戴断主分区其相应的子分数据也被截断

alter table scott.tm_ci_user_salary_m truncate partition user_salary_m_201003

具体动态sql如下：

-- 查看是存在主分区
     select count(*) into vi_result
    from all_tab_partitions
    where table_owner = upper(vs_user_name)
      and table_name = upper(vs_table_name)
      and partition_name = upper(vs_partition_name) || '_' || is_month;
    -- 如果存在查看子分区情况
    if vi_result > 0 then
    select count(*) into vi_result1
     from all_tab_subpartitions t
    where t.table_owner = upper(vs_user_name)
      and t.table_name = upper(vs_table_name)
      and t.partition_name = upper(vs_partition_name) || '_' || is_month
      and t.subpartition_name = upper(vs_partition_name) || '_' || is_month || '_' ||upper(vs_subpartition_name) || '_' || vs_area
      ;
      -- 如果存在子分则戴断子分区数据
      if vi_result1 > 0 then
       execute immediate 'alter table ' || vs_user_name || '.' || vs_table_name ||
                         ' truncate subpartition ' || vs_partition_name || '_' || is_month || '_' || vs_subpartition_name || '_' || vs_area ;
     
      -- 如果不存在增加相应的子分区
      else 
      execute immediate ' alter table ' || vs_user_name || '.' || vs_table_name ||
            ' modify partition ' || vs_partition_name || '_' || is_month ||
            ' add subpartition ' || vs_partition_name || '_' || is_month || '_' || vs_subpartition_name || '_' || vs_area || ' values ' || '('|| vs_area || ')' ;
      end if;
      else
     
      vs_sql := ' alter table ' || vs_user_name || '.' || vs_table_name ||
            ' add partition ' || vs_partition_name || '_' || is_month || ' values less than ( ' || /*vi_month*/ vi_next_month ||
            ')( subpartition ' || vs_partition_name || '_' || is_month || '_' || vs_subpartition_name || '_' || vs_area || ' values (' ||
            vs_area || '))';
     
      execute immediate vs_sql/*'alter table ' || vs_user_name || '.' || vs_table_name ||
            ' add partition ' || vs_partition_name || '_' || is_month || ' less than ' || '('|| vi_month || ')' ||  '(' ||
            ' subpartition ' || vs_subpartition_name || '_' || is_area_code ||  ' values ' || '('|| is_area_code || ' )) '*/
            ;
      end if;

-- 说明还需在动态sql里进行to_char此程序就完美了。