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);

 

--合并分区
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_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);