oracle大数据量定期备份参考方案

相比很多同学都做过历史数据备份的工作，可能方式有很多种，在此说一种业务场景：有原始表a和历史表a_his两个表，每个月月初将a中上个月之前的历史数据，转移到a_his中。

可能一开始想到的方案就是使用insert into a_his select * from a where date<'上个月1号'; 然后delete from a where date<'上个月1号';  。

且说这个方案逻辑上有没有问题，其实没有问题(一开始我也是这么做的)。但是当你有不止一个a表，且每个表的数据量都在百万千万甚至亿级的时候，你会发现，你的这个insert into 和delete 很消耗数据库的性能。

那么怎么弄才能快，而且不消耗性能。

经过一番搜索及尝试，推荐以下方法。

1、首先原始表a需要根据业务进行分区，而且分区的名称必须是有规则的，比如我的命名是以P_开头，xxxxMMyy结尾，示例：P_20170101，此处是根据date进行按月分区。 a_his表结构跟a一样，但是不用分区。完整的建表示例：

create table a(

vid varchar2(20),

vname varchar2(50),

vdate varchar2(10)

)

partition by range (vdate)

(
   partition P_20170101 values less than ('2017-01-01'),
  partition P_20170201 values less than ('2017-02-01')

);

如需要建索引，就建本地索引，分区表不建议建主键。

建索引的语句如下：create index IND_a_vid on a(vid) local nologging;

2、将用户授予建表及不限表空间权限。如 grant create table,unlimited tablespace to testuser;

3、封装执行数据转移的存储过程。示例：

create or replace procedure proce_movedatadtl(
vtype in varchar2,  -- 表名
tempworkdate in varchar2 --日期
)
as
tempstr varchar2(20);
tempsql varchar2(2000);
begin
  tempstr:= replace(tempworkdate,'-','');

-- 根据日期，动态创建原始表名为vtype 的备份表，并指定表空间为test_bak，只创建表结构
  tempsql:='create table '||vtype||'_'||tempstr||' tablespace test_bak as select * from '||vtype||' where 1=0';
  execute immediate tempsql;

-- 将原始表的指定表分区数据转移到备份表中，执行之后该分区索引会失效
  tempsql:='alter table '||vtype||' exchange partition P_'||tempstr||' with table '||vtype||'_'||tempstr;
  execute immediate tempsql;

-- 重建原始表的指定表分区的索引
  tempsql:='alter index IND_'||vtype||'_P_VID rebuild partition P_'||tempstr;   --重建索引，否则该分区索引是失效的
  execute immediate tempsql;
end;
/

4、创建可行执行的存储过程，可通过job，定时每月1号执行
create or replace procedure proce_movedata
as
tempworkdate varchar2(19);
begin
  select to_char(add_months(sysdate,-1), 'yyyy-MM')||'-01' into tempworkdate from dual; --根据当前时间查询上个月的第一天时间
  proce_movedatadtl('a',tempworkdate);
end;
/

此处给出的方案是我这边根据业务指定的，其他如果不符合此业务模型的可能就不能完全套用这个，但是应该也会提供一种思路。

凡是代码就要符合业务方可。