oracle.分区 命令,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;