oracle 高效复制百万数据

 mark 一下初始化数据时候复制1000万的数据sql ，用时10分钟左右

CREATE OR REPLACE PROCEDURE WHL_DTL_LOOP_PKG IS
--DECLARE
 --LD_START_DATE      DATE;
  LN_COMMIT_LABEL    NUMBER := 0;
  C_COMMIT_REC       NUMBER := 5000;
  /**TYPE TYP_TABLE IS TABLE OF DRM_WHL_DEALER_ACTIVITY_FACT%type;
  RT_BRR_TEMP  TYP_TABLE;*/
  
   TYPE typ_DEALER_NAME_CHS IS TABLE OF DRM_WHL_DEALER_ACTIVITY_FACT.DEALER_NAME_CHS%type;
   v_DEALER_NAME_CHS  typ_DEALER_NAME_CHS                     ;
  CURSOR CUR_CON IS
  select  DEALER_NAME_CHS from DPAPI.DP_BL_WHL_DLR_ACTIVITY_FACT_V@drm_bi_link  where PERIOD_ID>=20201201;
 BEGIN
  --DBMS_OUTPUT.PUT_LINE('START');
  DBMS_OUTPUT.PUT_LINE('START:'||' - '||TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'));
  
  EXECUTE IMMEDIATE 'TRUNCATE TABLE DRM_WHL_DEALER_ACTIVITY_FACT';
    --OPEN CURSOR AND FETCH DATE INTO REC
    OPEN CUR_CON;
    LOOP
      BEGIN
        FETCH CUR_CON BULK COLLECT INTO 
                v_DEALER_NAME_CHS 
         LIMIT C_COMMIT_REC;
        IF v_DEALER_NAME_CHS.COUNT > 0 THEN
          FORALL LN_IDX IN v_DEALER_NAME_CHS.FIRST .. v_DEALER_NAME_CHS.LAST
          -- INSERT  DATA
          INSERT /*+ APPEND */ INTO DRM_WHL_DEALER_ACTIVITY_FACT
                 (id,
                 DEALER_NAME_CHS
                 ) 
          VALUES (
                 SEQ_DRM_WHL_DEALER_ACTIVITY.NEXTVAL,
                v_DEALER_NAME_CHS (LN_IDX)
                
               );
          --COUNT INSERTED RECORDCOUNTS
          --LN_INS_COUNTER  := LN_INS_COUNTER + RT_BRR_TEMP.COUNT;
          LN_COMMIT_LABEL := LN_COMMIT_LABEL + v_DEALER_NAME_CHS.COUNT;
          v_DEALER_NAME_CHS.DELETE;
          -- LN_COMMIT_LABEL>5000 THEN COMMIT;
          IF LN_COMMIT_LABEL > C_COMMIT_REC THEN
          COMMIT;
          LN_COMMIT_LABEL := 0;
          END IF;
        ELSE
          EXIT;
        END IF;
        EXIT WHEN CUR_CON%NOTFOUND;
      END;
    END LOOP;
    COMMIT;
    CLOSE CUR_CON;
    COMMIT;

  --DBMS_OUTPUT.PUT_LINE('END');
  DBMS_OUTPUT.PUT_LINE('END:'||' - '||TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'));
EXCEPTION
  WHEN OTHERS THEN
    --DBMS_OUTPUT.PUT_LINE('ERROR:'||' - '||SQLCODE||' - '||SQLERRM);
  DBMS_OUTPUT.PUT_LINE('ERROR:'||SQLCODE||' - '||SQLERRM||' - '||TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'));
END;
/