oracle 表分区的用途,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)

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