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 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_idx storage(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_200409 values 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_200409 tablespace 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@jndxsq154 buffer=102400 tables=bill_monthfee:P_200401, file=E:\exp_para\exp_dxsq_tables.dmp log=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);
