oracle分区表学习及应用

-- Create table(创建分区表)create table BILL_MONTHFEE_ZERO( SERV_ID;;NUMBER(20) not null, BILLING_CYCLE_MONTH NUMBER(6) not null, ; DATE_TYPENUMBER(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); ;--合并分区ALTER TABLE;bill_monthfee_zero MERGE PARTITIONS p_200408, p_200409 INTO PARTITION p_all;--将分区改名alter table;bill_monthfee_zero rename Partition;p_200408 to p_fee_200408;--将分区改表空间alter table;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_paraexp_dxsq_tables.dmplog=E:exp_paraexp_dxsq_tables.log;;技巧：删除表中一个字段：alter table bill_monthfee_zero set unused column date_type;添加一个字段：alter table bill_monthfee_zero add date_type number(1);