-- Create table(创建分区表)
create table BILL_MONTHFEE_ZERO
(
SERV_ID;;NUMBER(20) not null,
BILLING_CYCLE_MONTH NUMBER(6) not null,
; DATE_TYPENUMBER(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_paraexp_dxsq_tables.dmp
log=E:exp_paraexp_dxsq_tables.log;;技巧：删除表中一个字段：alter table bill_monthfee_zero set unused column date_type;添加一个字段：alter table bill_monthfee_zero add date_type number(1);