oracle 分区操作常用命令

--交换分区
alter table tbName exchange partition pName
with table tbName
including indexes
without validation;
--drop partition 
alter table tbName drop partition pName;
--add partition
alter table tbName add partition pName values less than (2000);
ALTER TABLE SALES MODIFY PARTITION P3 ADD SUBPARTITION P3SUB1 VALUES('COMPLETE'); 
--截断分区
ALTER TABLE SALES TRUNCATE PARTITION P2;
--合并分区
ALTER TABLE SALES MERGE PARTITIONS P1,P2 INTO PARTITION P2; 
--拆分分区 
ALTER TABLE SALES SPLIT PARTITION P2 AT(TO_DATE('2003-02-01','YYYY-MM-DD')) 
INTO (PARTITION P21,PARTITION P22); 
--接合分区(coalesca) 
ALTER TABLE SALES COALESCA PARTITION;
--非分区表转分区表
 call DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, 'T'); 
call DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'T', 'T_T'); 
call DBMS_REDEFINITION.FINISH_REDEF_TABLE(USER, 'T', 'T_T');   


--查看指定分区
select   *   from   T   partition(part_2012_5); 
ALTER TABLE T TRUNCATE PARTITION part_2012_5;