1、增加一个分区
ALTER TABLE sales
ADD PARTITION jan96 VALUES LESS THAN ( '01-FEB-1999' )
TABLESPACE tsx;
增加一个列表分区
ALTER TABLE q1_sales_by_region
ADD PARTITION q1_nonmainland VALUES ('HI', 'PR')
STORAGE (INITIAL 20K NEXT 20K) TABLESPACE tbs_3
NOLOGGING;
2、合并分区
alter table dept coalesce partition
3、删除分区
ALTER TABLE sales DROP PARTITION dec98;
ALTER INDEX sales_area_ix REBUILD(如果含有全局索引);
4、合并分区
ALTER TABLE four_seasons
MERGE PARTITIONS quarter_one, quarter_two INTO PARTITION quarter_two;
Then, rebuild the local index for the affected partition.
-- Rebuild index for quarter_two, which has been marked unusable
-- because it has not had all of the data from Q1 added to it.
-- Rebuilding the index will correct this.
--
ALTER TABLE four_seasons MODIFY PARTITION
quarter_two REBUILD UNUSABLE LOCAL INDEXES;
5、移动一个分区
ALTER TABLE parts MOVE PARTITION depot2
TABLESPACE ts094 NOLOGGING;
6、重建一个local索引
ALTER INDEX I_FOUR_SEASONS_L REBUILD PARTITION I_QUARTER_FOUR
7、重命名一个分区
ALTER TABLE scubagear RENAME PARTITION sys_p636 TO tanks
8、一个分区拆分为两个分区
ALTER TABLE RANGE_EXAMPLE SPLIT PARTITION
PART_1 at
(TO_DATE(' 1994-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
INTO ( PARTITION PART_1 tablespace st1,
PARTITION PART_3 tablespace users)
9、truncate一个分区
ALTER TABLE sales TRUNCATE PARTITION dec98;
ALTER INDEX sales_area_ix REBUILD;
10、如果存在约束的情况,先disable约束
ALTER TABLE sales
DISABLE CONSTRAINT dname_sales1;
ALTER TABLE sales TRUNCATE PARTITTION dec94;
ALTER TABLE sales
ENABLE CONSTRAINT dname_sales1;
ALTER TABLE sales
ADD PARTITION jan96 VALUES LESS THAN ( '01-FEB-1999' )
TABLESPACE tsx;
增加一个列表分区
ALTER TABLE q1_sales_by_region
ADD PARTITION q1_nonmainland VALUES ('HI', 'PR')
STORAGE (INITIAL 20K NEXT 20K) TABLESPACE tbs_3
NOLOGGING;
2、合并分区
alter table dept coalesce partition
3、删除分区
ALTER TABLE sales DROP PARTITION dec98;
ALTER INDEX sales_area_ix REBUILD(如果含有全局索引);
4、合并分区
ALTER TABLE four_seasons
MERGE PARTITIONS quarter_one, quarter_two INTO PARTITION quarter_two;
Then, rebuild the local index for the affected partition.
-- Rebuild index for quarter_two, which has been marked unusable
-- because it has not had all of the data from Q1 added to it.
-- Rebuilding the index will correct this.
--
ALTER TABLE four_seasons MODIFY PARTITION
quarter_two REBUILD UNUSABLE LOCAL INDEXES;
5、移动一个分区
ALTER TABLE parts MOVE PARTITION depot2
TABLESPACE ts094 NOLOGGING;
6、重建一个local索引
ALTER INDEX I_FOUR_SEASONS_L REBUILD PARTITION I_QUARTER_FOUR
7、重命名一个分区
ALTER TABLE scubagear RENAME PARTITION sys_p636 TO tanks
8、一个分区拆分为两个分区
ALTER TABLE RANGE_EXAMPLE SPLIT PARTITION
PART_1 at
(TO_DATE(' 1994-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
INTO ( PARTITION PART_1 tablespace st1,
PARTITION PART_3 tablespace users)
9、truncate一个分区
ALTER TABLE sales TRUNCATE PARTITION dec98;
ALTER INDEX sales_area_ix REBUILD;
10、如果存在约束的情况,先disable约束
ALTER TABLE sales
DISABLE CONSTRAINT dname_sales1;
ALTER TABLE sales TRUNCATE PARTITTION dec94;
ALTER TABLE sales
ENABLE CONSTRAINT dname_sales1;