oracle817怎么备份,ORACLE数据库年底前的备份和整理oracle817-CSDN博客

用到了IMP EXP 分区回滚段

1.有一个表存放每天的进销存业务

2.另外两张分别存放每天的销售流水

将去年的数据转存到不常用的表备份

将今年的数据表重新导出、导入然后重新做数据索引

一处理销售

select min(invoice_date),max(invoice_date),count(*),count(distinct invoice_date)

from invoice

create table invoice08

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;