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

数据库  /  管理员 发布于 2年前   222

前提：
   数据库仓库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;COMMITalter 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 LOOPEND;/

--加并行快点

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 LOOPEND;/

8.注意内容
  如果索引很多（一般是超过5个，我觉得），可以考虑先删除索引，后加入数据，最后创建索引
9.自己理解比较少，只能写成这样，欢迎讨论。