oracle 符复合分区,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此程序就完美了。