oracle分区表学习及应用
来源:互联网 发布:mac制作pe启动盘 编辑:程序博客网 时间:2024/05/19 03:19
-- 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 (200408)
tablespace TS_ZIKEN
storage(initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0),
partition p_200408 values less than (200409)
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 local;
grant all on bill_monthfee_zero to dxsq_dev;
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 (200408)
tablespace TS_ZIKEN
storage(initial 100k next 100k minextents 1 maxextents unlimited pctincrease 0),
partition p_200408 values less than (200409)
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 local;
grant all on bill_monthfee_zero to dxsq_dev;
--增加分区表
alter table BILL_MONTHFEE_ZERO add Partition p_200409
values less than (200410) tablespace ts_ziken;
values less than (200410) 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
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
tablespace ts_ziken_01 nologging
--查询特定分区
select count(*) from BILL_MONTHFEE_ZERO partition (p_200407);
select count(*) from BILL_MONTHFEE_ZERO partition (p_200407);
--添加数据
insert into bill_monthfee_zero select * from bill_monthfee_zero partition (p_200407)
insert into bill_monthfee_zero select * from bill_monthfee_zero partition (p_200407)
--分区表的导出
userid=scott/tiger@aaa
buffer=102400
tables=bill_monthfee:P_200401,
file=E:/exp_para/exp_dxsq_tables.dmp
log=E:/exp_para/exp_dxsq_tables.log
buffer=102400
tables=bill_monthfee:P_200401,
file=E:/exp_para/exp_dxsq_tables.dmp
log=E:/exp_para/exp_dxsq_tables.log
一个表加入到一个分区表中当作是一个分区子表例子
--清空11月份数据.
alter table bill_monthfee truncate partition p_200311;
/
--防止操作出错,先把原表备份.
create table tmp_bill_monthfee_2311 as select * from tmp_bill_monthfee_200311;
--把备份的表添加到原表中,这时备份表的记录为0
alter table bill_monthfee exchange Partition p_200311 with table tmp_bill_monthfee_200311
/
--重建11月份的索引.
ALTER INDEX IDX_BILL_MONTHFEE_01 REBUILD PARTITION p_200311 nologging
/
ALTER INDEX IDX_BILL_MONTHFEE_ACC_ID REBUILD PARTITION p_200311 nologging
/
ALTER INDEX IDX_BILL_MONTHFEE_SERV_ID REBUILD PARTITION p_200311 nologging
/
alter table bill_monthfee truncate partition p_200311;
/
--防止操作出错,先把原表备份.
create table tmp_bill_monthfee_2311 as select * from tmp_bill_monthfee_200311;
--把备份的表添加到原表中,这时备份表的记录为0
alter table bill_monthfee exchange Partition p_200311 with table tmp_bill_monthfee_200311
/
--重建11月份的索引.
ALTER INDEX IDX_BILL_MONTHFEE_01 REBUILD PARTITION p_200311 nologging
/
ALTER INDEX IDX_BILL_MONTHFEE_ACC_ID REBUILD PARTITION p_200311 nologging
/
ALTER INDEX IDX_BILL_MONTHFEE_SERV_ID REBUILD PARTITION p_200311 nologging
/
注意:把表加入到分区表中,此表的结构与分区表要一致,而且导入后这个表的记录都没了.
技巧:
删除表中一个字段:
alter table bill_monthfee_zero set unused column date_type;
添加一个字段:alter table bill_monthfee_zero add date_type number(1);
- oracle分区表学习及应用
- oracle分区表学习及应用
- oracle分区表学习及应用
- Oracle分区表学习及应用
- oracle分区表学习及应用--转帖
- Oracle分区表学习及练习
- 深入学习Oracle分区表及分区索引
- 深入学习Oracle分区表及分区索引
- 深入学习Oracle分区表及分区索引
- 深入学习Oracle分区表及分区索引
- 深入学习Oracle分区表及分区索引
- 深入学习Oracle分区表及分区索引
- 深入学习Oracle分区表及分区索引
- 深入学习Oracle分区表及分区索引
- 深入学习Oracle分区表及分区索引
- 深入学习Oracle分区表及分区索引
- oracle 分区表简单应用
- oracle分区表学习小结
- 在Web上用style实现完美颜色渐变 (非原创)
- 一些非常有用的JAVA常用方法
- 黯然销魂和欣喜如狂
- Oracle专家调优秘密(引用yesky)
- Oracle8i中使用Java语言来开发存储过程
- oracle分区表学习及应用
- 用鼠标画图
- ACL&ACE的三条原则!
- 如何创建一个简单的JAVA存储过程
- 对Spring Framework的理解
- SAS多任务并行处理,利用systask
- 论坛灌水机
- 040902 R 脚本特效 (All Screen , Hide bar...)
- 使用 javap 深入查看类文件