当前位置:首页 > 开发 > 数据库 > 正文

oracle大数据表复制备份个人经验

发表于: 2014-05-26   作者:bitcarter   来源:转载   浏览:
摘要: 前提:    数据库仓库A(就拿oracle11g为例)中有两个用户user1和user2,现在有user1中有表ldm_table1,且表ldm_table1有数据5千万以上,ldm_table1中的数据是从其他库B(数据源)中抽取过来的,前期业务理解不够或者需求有变,数据有变动需要重新从B中抽取数据到A库表ldm_table1中。    
前提:
   数据库仓库A(就拿oracle11g为例)中有两个用户user1和user2,现在有user1中有表ldm_table1,且表ldm_table1有数据5千万以上,ldm_table1中的数据是从其他库B(数据源)中抽取过来的,前期业务理解不够或者需求有变,数据有变动需要重新从B中抽取数据到A库表ldm_table1中。
    重新抽取数据前想把原来的ldm_table1中的数据进行备份,备份到用户user2中,以备将来恢复(重新抽取可能出错等原因)。
    前面差不多是废话,可能举例不恰当,反正就是想备份大表(5千万以上记录,我自己这么认为,相对的),或者大表之间数据存在相互拷贝的一些方法。
1.具体备份方法(这里写了16个并行,视情况而定):
create table user2.LDM_table1 nologging parallel (DEGREE 16) AS SELECT * FROM user1.LDM_table1;

2.
truncate table user1.LDM_table1;

3.现在想把user2.LDM_table1中数据再恢复到user1.LDM_table1中,具体sql如下(我们不再create table了,因为user1.LDM_table1已经存在,并且索引什么的都建立了,如果数据没有超过1亿):
  
alter table user1.LDM_table1 nologging;
   alter session enable parallel dml;
   insert /*+append parallel*/ into user1.LDM_table1 SELECT/*+parallel*/ * FROM user2.LDM_table1;
COMMIT
alter table user1.LDM_table1 logging;;

4.数据再多一些,比如几个亿的,用3中方法有些慢,所以我目前觉得还是用create方法(上述1中提到的)好些。但是采用重新创建表的方法时,需要drop掉原来的表,并且要建立索引,具体sql如下:
  a.采用1中方法,
create table user1.LDM_table1 nologging parallel (DEGREE 16) AS SELECT * FROM user2.LDM_table1;

  b.创建主键(主键创建加并行好像不起作用)
  c.创建索引(这里加了24个并行,视情况)
  
create bitmap index user1.INDEX_LDM_table1_RQ  on user1.LDM_table1 (RQ)parallel 24  local;

  d.如果需要收集统计信息,则执行
    
exec  dbms_stats.gather_table_stats('USER1','LDM_TABLE1,CASCADE=>true,estimate_percent=>10,method_opt=>'for all columns size auto',degree=>16);

5.如果表user1.LDM_table1表存在分区,那么重新恢复可就不是简单create table(上述方法1)就能行的,因为采用上述方法1会丢失分区,所以这时应该用另一种方法,具体sql如下:(注意必须写明各个字段,而字段后面不跟长度类型等)
 
 create table user1.LDM_table1
(
  aaa,
  bbb,
  ccc,
  ....
    
)
partition by range (N_DM)
(
  partition P00000000000 values less than (' 5500000')
    tablespace TS_DAT_LDM
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 160K
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition P 5500000 values less than ('5501000')
    tablespace TS_DAT_LDM
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 160K
      next 1M
      minextents 1
      maxextents unlimited
    ),
  ........
)
select 
aaa,
bbb,
ccc,
.....
from user2.LDM_table1;
6.有时候需要将表user2.LDM_table1中的部分数据提交到表user1.LDM_table1中,如果采用上述3中的方法会觉得有些慢,可以采用分部提交或者就循环提交,每次提交100万,直至提交完毕,具体sql如下
   --循环提交数据 
declare
  cursor cur is
    select /*+parallel(16)*/* from user2.LDM_table1;
  type rec is table of cur%rowtype;
  recs rec;
begin
  execute immediate 'alter table user1.LDM_table1 nologging';
  execute immediate 'alter session enable parallel dml';
  open cur;
  while (true) loop
    fetch cur bulk collect
      into recs limit 1000000;
    forall i in 1 .. recs.count
      insert /*+append parallel(8)*/into user1.LDM_table1 values recs (i);
    commit;
    exit when cur%notfound;
  end loop;
  close cur;
  execute immediate 'alter table user1.LDM_table1 logging';
end;
/
7.如果想删除大表user1.LDM_table1中的部分数据,比如从几亿数据中删除1000万,可能采用delete方法比较慢,所以我们可以采用循环删除的方法,具体sql如下:

--循环删除数据
declare
  v_cnt NUMBER:=0;
BEGIN
  LOOP
   DELETE FROM user1.LDM_table1 WHERE y_dm LIKE '10025%'
    AND ROWNUM<=10000;
   v_cnt:=SQL%ROWCOUNT;
     COMMIT;
   EXIT WHEN v_cnt<=0;
  END LOOP
END;
/
--加并行快点
declare
  v_cnt NUMBER:=0;
BEGIN
  execute immediate 'alter table user1.LDM_table1 nologging';
  execute immediate 'alter session enable parallel dml';
  LOOP
   DELETE/*+PARALLEL(16)*/ FROM user1.LDM_table1 WHERE y_dm LIKE '10025%'
    AND ROWNUM<=10000;
   v_cnt:=SQL%ROWCOUNT;
     COMMIT;
   EXIT WHEN v_cnt<=0;
  END LOOP
END;
/
8.注意内容
  如果索引很多(一般是超过5个,我觉得),可以考虑先删除索引,后加入数据,最后创建索引
9.自己理解比较少,只能写成这样,欢迎讨论。
  
  
   

oracle大数据表复制备份个人经验

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
在数据操作之中,有可能有些用户不会进行事务的提交,那么在这种情况下很可能无法进行完整的备份操
Technorati 标签: BE, Backup Exec, Veritas, 备份 1.1 根据策略创建Job—Oracle复制备份集,差异复
Xcode是强大的IDE(但个人觉得不如Visual Studio做得好),其强大功能无需本人再赘述,本文也不是一
本篇文章为大家介绍如何对MySQL进行复制、表结构复制,也可以分字段进行复制。也可以将一张表中的数
1.1. 数据库复制 1.1.1. 基本概念 复制是将一组数据从一个数据源拷贝到多个数据源的技术,是将一份
安装SQLServer数据库默认是不区分大小写的,这里只修改某个字段值区分大小写 另一种方法:在SQL语句
Technorati 标签: BE, Backup Exec, Veritas, 备份 1.1 根据策略创建Job—Oracle复制备份集与差异备
Technorati 标签: BE, Backup Exec, Veritas, 备份 1.1 根据策略创建Job—Oracle复制备份集与差异备
GXPT是一个分布式系统,此系统暂时包含权限系统、基础系统、评教系统、考试系统,各个系统建有自己
1: 用sys/orcl登陆: 2:创建用户(PLSQL Developer) : create tablespace tianbao datafile 'F:\ap
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号