Oracle分区命令集

-- Create table(创建分区表)create table BILL_MONTHFEE_ZERO(SERV_ID NUMBER(20) not null,BILLING_CYCLE_MONTH NUMBER(6) not null,DATE_TYPE NUMBER(1),ACC_NBR VARCH-- Create table(创建分区表)create table BILL_MONTHFEE_ZERO(SERV_ID NUMBER(20) not null,BILLING_CYCLE_MONTH NUMBER(6) not null,DATE_TYPE NUMBER(1),ACC_NBR VARCHAR2(80))partition by range (BILLING_CYCLE_MONTH)(partition p_200407 values less than (200407) tablespace TS_ZIKEN storage(initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0), partition p_200408 values less than (200408) tablespace TS_ZIKEN storage(initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0)) ;create index idx_bill_monthfee_zero_idx01 on bill_monthfee_zero(billing_cycle_month)tablespace TS_ZIKEN_idxstorage(initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0) nologging;grant all on bill_monthfee_zero to dxsq_dev;--增加分区表alter table BILL_MONTHFEE_ZERO add Partition p_200409values less than (200409) tablespace ts_ziken;--删除一分区alter table part_tbl drop Partition part_tbl_08;--将一个分区分为两个分区alter table bill_monthfee_zero split Partition p_200409 at (200409)into (Partition p_200409_1 tablespace ts_ziken,Partition p_200409_2 tablespace ts_ziken_idx);--合并分区ALTERTABLE bill_monthfee_zero MERGE PARTITIONS p_200408, p_200409 INTOPARTITION p_all--将分区改名altertable bill_monthfee_zero rename Partition p_200408 to p_fee_200408--将分区改表空间altertable bill_monthfee_zero move Partition p_200409tablespace ts_ziken_01 nologging--查询特定分区select count(*) from BILL_MONTHFEE_ZERO partition (p_200407);--添加数据insert into bill_monthfee_zero select * from bill_monthfee_zero partition (p_200407)--分区表的导出userid=dxsq/teledoone@jndxsq154buffer=102400tables=bill_monthfee:P_200401,file=E:"exp_para"exp_dxsq_tables.dmplog=E:"exp_para"exp_dxsq_tables.log技巧：删除表中一个字段：alter table bill_monthfee_zero set unused column date_type;添加一个字段：alter table bill_monthfee_zero add date_type number(1);