oracle分區表學習及應用

     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)

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