用到了IMP EXP 分区 回滚段
1.有一个表存放每天的进销存业务
2.另外两张分别存放每天的销售流水
将去年的数据 转存到 不常用的表备份
将今年的数据表重新导出、导入 然后重新做数据索引
一 处理销售
select min(invoice_date),max(invoice_date),count(*),count(distinct invoice_date)
from invoice
create table invoice08
as
select * from invoice
where invoice.invoice_date between to_date(20080101,'YYYYMMDD') and
to_date(20081231,'yyyymmdd')
exp userid file = back_invoice_08.dmp tables = invoice08
delete from invoice where invoice_date
然后将invoice表数据重新做数据整理(现在本人没有学到其他的方法,但是我测试用EXP导出后IMP导入可以解决我的小问题)
1. exp userid file = back_invoice.dmp tables = invoice;
2. truncate table invoice
3. imp userid file = back_invoice.dmp tables = invoice;
4. 重新做索引
结果只剩下2009年的当年数据(保留一年数据)
添加 数据的索引并加入分区功能 提高检索的速度
CREATE INDEX "SZSTORE"."INVOICE|_INDEX1"
ON "SZSTORE"."INVOICE"("INVOICE_DATE")
TABLESPACE "SYSTEM" GLOBAL
PARTITION BY RANGE (INVOICE_DATE) (
PARTITION "INVOICE|_INDEX1_P1"
VALUES LESS THAN (TO_DATE('2009-1-1','YYYY-MM-DD')) ,
PARTITION "INVOICE|_INDEX1_P2"
VALUES LESS THAN (TO_DATE('2009-2-1','YYYY-MM-DD')) ,
PARTITION "INVOICE|_INDEX1_P3"
VALUES LESS THAN (TO_DATE('2009-3-1','YYYY-MM-DD')) ,
PARTITION "INVOICE|_INDEX1_P4"
VALUES LESS THAN (TO_DATE('2009-4-1','YYYY-MM-DD')) ,
PARTITION "INVOICE|_INDEX1_P5"
VALUES LESS THAN (TO_DATE('2009-5-1','YYYY-MM-DD')) ,
PARTITION "INVOICE|_INDEX1_P6"
VALUES LESS THAN (TO_DATE('2009-6-1','YYYY-MM-DD')) ,
PARTITION "INVOICE|_INDEX1_P7"
VALUES LESS THAN (TO_DATE('2009-7-1','YYYY-MM-DD')) ,
PARTITION "INVOICE|_INDEX1_P8"
VALUES LESS THAN (TO_DATE('2009-8-1','YYYY-MM-DD')) ,
PARTITION "INVOICE|_INDEX1_P9"
VALUES LESS THAN (TO_DATE('2009-9-1','YYYY-MM-DD')) ,
PARTITION "INVOICE|_INDEX1_P10"
VALUES LESS THAN (TO_DATE('2009-10-1','YYYY-MM-DD')) ,
PARTITION "INVOICE|_INDEX1_P11"
VALUES LESS THAN (TO_DATE('2009-11-1','YYYY-MM-DD')) ,
PARTITION "INVOICE|_INDEX1_P12"
VALUES LESS THAN (TO_DATE('2009-12-1','YYYY-MM-DD')) ,
PARTITION "INVOICE|_INDEX1_P13"
VALUES LESS THAN (TO_DATE('2010-1-1','YYYY-MM-DD')) ,
PARTITION "INVOICE|_INDEX1_P14"
VALUES LESS THAN (TO_DATE('2010-2-1','YYYY-MM-DD')) ,
PARTITION "INVOICE|_INDEX1_P15"
VALUES LESS THAN (TO_DATE('2010-3-1','YYYY-MM-DD')) ,
PARTITION "INVOICE|_INDEX1_P16"
VALUES LESS THAN (TO_DATE('2010-4-1','YYYY-MM-DD')) ,
PARTITION "INVOICE|_INDEX1_P17"
VALUES LESS THAN (TO_DATE('2010-5-1','YYYY-MM-DD')) ,
PARTITION "INVOICE|_INDEX1_P18"
VALUES LESS THAN (TO_DATE('2010-6-1','YYYY-MM-DD')) ,
PARTITION "INVOICE|_INDEX1_P19"
VALUES LESS THAN (TO_DATE('2010-7-1','YYYY-MM-DD')) ,
PARTITION "INVOICE|_INDEX1_P20"
VALUES LESS THAN (TO_DATE('2010-8-1','YYYY-MM-DD')) ,
PARTITION "INVOICE|_INDEX1_P21"
VALUES LESS THAN (TO_DATE('2010-9-1','YYYY-MM-DD')) ,
PARTITION "INVOICE|_INDEX1_P22"
VALUES LESS THAN (TO_DATE('2010-10-1','YYYY-MM-DD')) ,
PARTITION "INVOICE|_INDEX1_P23"
VALUES LESS THAN (TO_DATE('2010-11-1','YYYY-MM-DD')) ,
PARTITION "INVOICE|_INDEX1_P24"
VALUES LESS THAN (MAXVALUE) )
--------------------------------------------------------------------------------------
因为明细的数据量太大 所以准备存放一个季度
--------------------------------------------------------------------------------------
1.查看数据
2.创建并备份数据
create table invoice_line09_79 as select * from invoice_line
where invoice_date between to_date(20090701,'yyyymmdd')
and to_date(20090930,'yyyymmdd')
3.删除数据
/
alter rollback segment RBS5 shrink;
alter rollback segment RBS4 shrink;
alter rollback segment RBS3 shrink;
alter rollback segment RBS2 shrink;
alter rollback segment RBS1 shrink;
alter rollback segment RBS0 shrink;
alter rollback segment RBS6 shrink;
/
delete from invoice_line
where invoice_date between to_date(20090701,'yyyymmdd')
and to_date(20090930,'yyyymmdd')
4. 整理数据 EXP 导出 IMP 导入一遍
问题1:
删除数据 报错回滚段满了
TABLE1 为存放每日 进销存数据 每日增加大约50000条数据 一年在200000000数据左右 所以删除非常慢
才有一下删除防范
删除TABLE1中的数据 按照每个月COMMIT 一次 可以防止 回滚段占满 报错 优化一下应该
alter rollback segment RBS5 shrink;
alter rollback segment RBS4 shrink;
alter rollback segment RBS3 shrink;
alter rollback segment RBS2 shrink;
alter rollback segment RBS1 shrink;
alter rollback segment RBS0 shrink;
alter rollback segment RBS6 shrink;
declare
month number(2);
begin
for 1..12 loop
delete from TABLE1
where to_char(run_date,'yyyy')=2008
and to_char(run_date,'mm')=month;
commit;
alter rollback segment RBS5 shrink;
alter rollback segment RBS4 shrink;
alter rollback segment RBS3 shrink;
alter rollback segment RBS2 shrink;
alter rollback segment RBS1 shrink;
alter rollback segment RBS0 shrink;
alter rollback segment RBS6 shrink;
end;