oracle composite partition operation

– Create and modify sub-partition method (range-list)
select partition_name, subpartition_name, tablespace_name
from user_tab_subpartitions where table_name = ‘TM_CI_USER_SALARY_M1’;

– Relevant query primary partition table and sub-partition table

all_tab_partitions
all_tab_subpartitions

– Examples of operations

create table scott.TM_CI_USER_SALARY_M1 is
(
statis_month number (6),
area_code varchar (4),
empno number (4),
ename varchar2 (10),
job varchar2 (9)
The 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;

– Increase the sub-partition the main method
alter table scott.tm_ci_user_salary_m1 modify partition salary_m_200909 add subpartition area_code_12 values ??(’12 ‘)
– Delete sub-partition method
alter table scott.tm_ci_user_salary_m1 drop subpartition area_code_12;
– Wear off sub-partition method
alter table scott.tm_ci_user_salary_m1 truncate subpartition area_code_12;
– New primary partition and sub-partition method
alter table scott.tm_ci_user_salary_m1 add partition salary_m_200911 values ??less than (200911) (subpartition area_code_13 values ??(’13 ‘));
– To the user debugger permissions
grant debug connect session to scott;

– Wear off the main partition corresponding sub-data is truncated

alter table scott.tm_ci_user_salary_m truncate partition user_salary_m_201003

The specific dynamic sql is as follows:

– View primary partitions are
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 there is to see the child partitions
if vi_result> 0 then
select count (*) into vi_result1
the 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 there are sub-sub-to wear off sub-partition data
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;

– If there is no corresponding increase in the sub-partition
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;

– Need dynamic SQL to_char program.