Oracle当数据记录超过千万(50000000)条时，并且不在同一个库，如何从一个表导到另一个表？提高性能

Question

当数据记录超过千万(50000000)条时，并且不在同一个库，如何从一个表导到另一个表？提高性能
DA库有表TA，有8千万条数据，DB库有同样结构的TB表，如何在将TA表导入TB表，并且如何优化性能？
我是用数据链，导的过程中，没有经过任何字段的计算
insert into tb select * from ta@数据链
可是这样出现临时表空间TEMP一直增大，导致硬盘没空间了，怎么办？并且TEMP不自动释放如果是在存储过程中加一个计数器，用游标每10000条COMMIT一次，这样会不会提高效率
我试过小的表，用游标每10000条COMMIT时，效率更慢。
怎么办？

Answer 1

在楼主的基础上提一些小建议：
1、tb表中删除所有索引约束；如有索引的话
2、select 所有字段 from ta where 索引字段；如有索引的话

Answer 2

在过程里设置自动提交分析一下效果。
另外有点疑问：必须每次都同步TA表的全部数据么？能不能换成update/insert模式？

Answer 3

你的表设计的是分区表还是普通的表，如果是分区表，可以一个分区一个分区的insert到目的表并且用direct-path方式；如果是普通表，可以写个过程，用BULK COLLECT和FORALL来实现批量insert。

Answer 4

楼主，是否有权限在DA库有表TA上建立触发器？
有的话，用触发器更新吧，实时的

Answer 5

不能进行时时的更新，因为两个库不在同一个机器上，DA在外网，DB在内网
计划每周更新一次4楼的“如果是普通表，可以写个过程，用BULK COLLECT和FORALL来实现批量insert。”怎么实现？
两个表中没有可以区别的字段，也就是说没办法知道哪些是新的需要更新，哪些是老的记录不用更新

Answer 6

本地库DB所在的机器内存有点小，才1G，
ORACLE装在D盘，所有的数据都在D盘，明显的有5G剩余空间，可是如果一进行导数据操作，结果可用的只有十几M了，TEMP空间和回滚空间就猛增加。并且也不自动释放
两个表同步时，无法确定哪些表是新数据，所以我的想法就是每次先清空本地表DB.TB，然后把远程表全部更新过来
有没有好的办法？

Answer 7

你不是可以做DB_link吗？ 说明数据库DB可以访问DA。 
请问DA可以访问DB吗？ 可以的话，又有建立在DA建立触发器的权限，就可以做实时更新

Answer 8

BLUK COLLECT需要大量内存，因为机器才1G，所以用BLUK COLLECT不太合适吧

Answer 9

如果每天变化的数据量小，则先update后insert实现
如果每天变化的数据量也很大，则用4楼的办法

Answer 10

可以在DA上建立一张临时表TA1，结构和TA一样，外加一个时间字段；
并且在TA上建立触发器，当插入，更新时，同时将记录插入到TA1中，加上插入，更新时间；在导入B表时，只需要导入更新时间在上次导入时间之后的记录就可以了，这样可以大大提高效率

Answer 11

没有在DA上建立触发器的权限，只有读的权限
以下是我有游标每10000条提交一次，但是没有提高性能，反而更慢
create or replace procedure P_insertinto_TB is
  k         integer := 0; --计数器初始化
  rowrecord TB%rowtype;
  cursor cur_TB is
    select * from TA@simis;
begin
  execute immediate 'truncate table TB';
  open cur_TB;
  loop
    fetch cur_TB
      into rowrecord;
    exit when cur_TB%notfound;
    insert into TB values rowrecord;
    k := k + 1;
    if k = 10000 then
      commit;
      k := 0;
    end if;
  end loop;
  commit;
  close cur_TB;end P_insertinto_TB;

Answer 12

给个用bulk collect的例子
SQL> CREATE TABLE sales_target_errors
  2  (sql_err_mesg varchar2(4000))
  3  /Table created.
Elapsed: 00:00:00.28
SQL>  DECLARE
  2        TYPE array IS TABLE OF sales_target%ROWTYPE
  3           INDEX BY BINARY_INTEGER;
  4        sales_src_arr   ARRAY;
  5        errors          NUMBER;
  6        error_mesg     VARCHAR2(255);
  7        bulk_error      EXCEPTION;
  8        l_cnt           NUMBER := 0;
  9        PRAGMA exception_init
 10              (bulk_error, -24381);
 11        CURSOR c IS 
 12           SELECT * 
 13           FROM   sales_src;
 14        BEGIN
 15        OPEN c;
 16        LOOP
 17          FETCH c 
 18             BULK COLLECT 
 19             INTO sales_src_arr 
 20             LIMIT 100;
 21          BEGIN
 22             FORALL i IN 1 .. sales_src_arr.count 
 23                      SAVE EXCEPTIONS
 24               INSERT INTO sales_target VALUES sales_src_arr(i);
 25          EXCEPTION
 26          WHEN bulk_error THEN
 27            errors := 
 28               SQL%BULK_EXCEPTIONS.COUNT;
 29            l_cnt := l_cnt + errors;
 30            FOR i IN 1..errors LOOP
 31              error_mesg := SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE);
 32              INSERT INTO sales_target_errors 
 33              VALUES     (error_mesg);
 34       END LOOP;
 35          END;
 36          EXIT WHEN c%NOTFOUND;
 37      
 38       END LOOP;
 39       CLOSE c;
 40       DBMS_OUTPUT.PUT_LINE
 41        ( l_cnt || ' total errors' );
 42       END;
 43  /
只要设置合适的limit值，内存不是问题

Answer 13

若果只是某几张表，
建议使用物理视图吧。
因为已经存在了DB_LINK
在TB上建立个物理视图步骤:
建立TA,TB的DB_LINK在源数据库上，创建要同步表的快照日志
Create snapshot log on Ta.tablename在目标数据库上创建快照(被同步（源）数据库服务必须启动)
Create snapshot sn_user as select * from user@dblink_TAAlter snapshot sn_TA.tablename refresh fast Start with sysdate next sysdate+30/24*60*60;
--oracle自动在当前时间立即进行第一次快速刷新，以后每隔30秒快速刷新一次现在实现了TA某表到TB的，然后
将sn_TA.tablename建个触发，如
CREATE OR REPLACE TRIGGER BST114.TRI_USER_AFR
AFTER DELETE OR INSERT OR UPDATE
ON BST114.SN_user 
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
declare
    tmp_id number(10):=-1;
    flag number(3):=0;
begin
  dbms_output.put_line('begin');
  if inserting then         
      for p in(select id from user where id=:new.id)
      loop
        tmp_id:=p.id;
      end loop;
      
      dbms_output.put_line(tmp_id||'===------------');
      if (tmp_id=-1) then
          insert into user(id,name,age)
          values(:new.id,:new.name,:new.age);
      end if;
  end if;
  
  if updating then
     dbms_output.put_line('updated');
     for p in(select name,age from user where id=:old.id)
     loop
         if (p.name!=:new.name) or (p.age!=:new.age) then
              update user set name=:new.name,age=:new.age where id=:old.id;
         end if;
     end loop;
  end if;
  
  if deleting then
      dbms_output.put_line('deleted');
      delete from user where id=:old.id;
  end if;
  dbms_output.put_line('end');
end TRI_USER_AFR;这样就实现了TB中物理视图到 目的表的数据传输。

Answer 14

楼上各位已经提供了很多意见了，结合自己的情况处理。
还有个比较常见的方式就是用expdp,impdp ,必须是10g版本，而且服务器磁盘空间足够。

Answer 15

看看我按照18楼的方法，为什么会导致目标表被锁住？
CREATE OR REPLACE PROCEDURE P_ADD_TB2_2 IS
  TYPE ARRAY IS TABLE OF TB2%ROWTYPE;
  SALES_SRC_ARR ARRAY;
  ERRORS        NUMBER;
  ERROR_MESG    VARCHAR2(255);
  BULK_ERROR EXCEPTION;
  L_CNT NUMBER := 0;
  PRAGMA EXCEPTION_INIT(BULK_ERROR, -24381);
  CURSOR C IS
    SELECT * FROM TB2;
BEGIN
  OPEN C;
  LOOP
    FETCH C BULK COLLECT
      INTO SALES_SRC_ARR LIMIT 1000;
    BEGIN
      FORALL I IN 1 .. SALES_SRC_ARR.COUNT SAVE EXCEPTIONS
        INSERT INTO TB2_2 VALUES SALES_SRC_ARR (I);
    EXCEPTION
      WHEN BULK_ERROR THEN
        ERRORS := SQL%BULK_EXCEPTIONS.COUNT;
        L_CNT  := L_CNT + ERRORS;
        FOR I IN 1 .. ERRORS LOOP
          ERROR_MESG := SQLERRM(-SQL%BULK_EXCEPTIONS(I).ERROR_CODE);
          INSERT INTO SALES_TARGET_ERRORS VALUES (ERROR_MESG);
        END LOOP;
    END;
    COMMIT;
    EXIT WHEN C%NOTFOUND;
  END LOOP;
  CLOSE C;
  --DBMS_OUTPUT.PUT_LINE(L_CNT || ' total errors');
END;

Answer 16

去掉索引，去掉主键外键。
建议使用游标每10000条COMMIT的方法，这样系统资源占用的会较少。或者导出成文本文件。

Answer 17

在程序中调用dbms_datapump（或者expdp,impdp)并不是困难的事情，可以ss一下。
应为不是同机器，主要要解决COPY文件的问题（应该是EASY的事情）.
至少尝试一下看看。

Answer 18

你的数据比较多而内存较小，所以分次插入的思路是对的
但是你用游标去整，这不是把简单问题搞复杂了么？游标多么的耗资源!你直接关掉索引，然后
insert into tb select * from ta where rownum<=xxxx...
commit;照这样，整到包里面，一个星期作业调度一次，简简单单不就完了