Oracle分区之四：分区维护和管理

核心提示：T3 HR P_OTHERS 0实验四、alter table t3 merge partitions p2_1,p2_2 int

T3 HR P_OTHERS 0

实验四、
alter table t3 merge partitions p2_1,p2_2 into partition p2;
SQL> select TABLE_NAME, TABLE_OWNER, PARTITION_NAME, SUBPARTITION_COUNT from dba_tab_partitions where table_name='T3';
TABLE_NAME TABLE_OWNER PARTITION_NAME SUBPARTITION_COUNT
------------------------------ ------------------------------ ------------------------------ ------------------
T3 HR P1 0
T3 HR P2 0
T3 HR P3 0
T3 HR P_OTHERS 0
实验五( 向分区某个分区里增加个分区列值)
SQL> alter table t3 modify partition p3 add values('ZQ');
Table altered.
二，分区索引的相关实验
实验六(创建索引分区)
create index idx_t3 on t3(id)
global partition by range(id)
(
partition p1 values less than (1000),
partition p2 values less than (maxvalue)
);
drop index idx_3
create index idx_t3 on t3(id)
global partition by hash(id)
partitions 4;

create table tt2(id number,createdate date)
partition by range(createdate)
subpartition by hash(id) subpartitions 2
(
partition p1 values less than (to_date('2010-07-01','yyyy-mm-dd')),
partition p2 values less than (to_date('2011-01-01','yyyy-mm-dd'))
);

create table tt4(id number,name varchar2(10))
partition by range(name)
(
partition p1 values less than ('h'),
partition p2 values less than ('o')
);
create index idx_tt4 on tt4(id) local;
drop indexe idx_tt4 ;
create index idx_tt4 on tt4(id)
global partition by range(id)
(
partition p1 values less than (1000),
partition p2 values less than (maxvalue)
);
SQL> SET LINESIZE 200
SQL> select INDEX_OWNER, INDEX_NAME,PARTITION_NAME FROM dba_Ind_Partitions where index_name='IDX_TT4';
INDEX_OWNER INDEX_NAME PARTITION_NAME
------------------------------ ------------------------------ ------------------------------
HR IDX_TT4 P1
HR IDX_TT4 P2

alter index idx_tt4 split partition p2 at (2000) into
(partition p3,partition p_max);
SQL> select INDEX_OWNER, INDEX_NAME,PARTITION_NAME FROM dba_Ind_Partitions where index_name='IDX_TT4';
INDEX_OWNER INDEX_NAME PARTITION_NAME
------------------------------ ------------------------------ ------------------------------