ORACLE数据库年底前的备份和整理oracle817

 用到了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<to_date(20090101,'yyyymmdd')

然后将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;