linux 创建分区表,创建分区表

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

--合并分区ALTERTABLEbill_monthfee_zeroMERGEPARTITIONS p_200408,p_200409INTOPARTITIONp_all

--将分区改名

altertablebill_monthfee_zero renamePartitionp_200408top_fee_200408

--将分区改表空

间

altertablebill_monthfee_zero

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