创建分区表

CREATE TABLE test
(
A NUMBER,
B NUMBER,
TESTDATE DATE
)
partition by range (testdate)
(
 partition part_01 values less than(to_date('2005-10-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace testmin,
 partition part_02 values less than(to_date('2006-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace test1,
 partition part_03 values less than(to_date('2006-04-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace test2,
 partition part_04 values less than(maxvalue) tablespace testmax);

create index testix on test
(
A
)
local
(
partition idx_1 tablespace testixmin,
partition idx_2 tablespace testix1, 
partition idx_3 tablespace testix2,
partition idx_4 tablespace testixmax
);

create index test_PK on test(testdate,A)
global partition by range(testdate)
(
 partition pk_1 values less than(to_date('2005-10-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace testpkmin,
 partition pk_2 values less than(to_date('2006-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace testpk1,
 partition pk_3 values less than(to_date('2006-04-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace testpk2, 
 partition pk_4 values less than(maxvalue)) tablespace testpkmax;                            

alter table test
  add constraint test_PK primary key (testdate,A);

alter table test split partition part_03 AT (to_date('2006-04-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) into (partition part_03 tablespace test3,partition part_04 tablespace test4);

alter index test_pk split partition pk_3 at (to_date('2006-04-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) into (partition pk_3 tablespace testpk3,partition pk_4 tablespace testpk4);

alter table test move partition part_03 tablespace test3;

alter index testix rebuild partition idx_3 tablespace testix3;

select table_name,partition_name,high_value,tablespace_name from dba_tab_partitions where table_owner='***'

select * from DBA_IND_PARTITIONS where INDEX_owner='****'

index,status unusable or usable
alter index test_pk rebuild partition pk_1;

alter index TESTIX rename partition PART_04 to IDX_4;

alter table test drop partition part_01;

ALTER INDEX TEST_PK DROP PARTITION PK_1;

转载于:https://www.cnblogs.com/green0818/archive/2012/07/31/2616734.html