oracle分区表学习及应用
2024-08-29 13:30:29
供稿：网友
，欢迎访问网页设计爱好者web开发。-- 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/[email protected]
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);
