Oracleで特定スキーマの全データをコピー

用途

Oracleで特定スキーマのデータだけをコピーしたい場合に使用。
本番機から開発機へのデータコピー用です。

素直にExport/Importなど、システム管理者用のコマンドを使う方がおすすめです。
ですが、アプリケーションエンジニアだとシステム権限を持ってなくて
できいという方も多いと思うので、そういった方には使えます。

DECLARE
   x_schema_name   VARCHAR2(40) := 'targetSchema';
   x_db_link_name  VARCHAR2(40) := 'sourceDB';
BEGIN
  FOR rec IN(
    SELECT A.OWNER, A.TABLE_NAME FROM ALL_TABLES A
    WHERE A.OWNER IN(x_schema_name)
    ORDER BY A.OWNER , A.TABLE_NAME
  ) LOOP
   BEGIN
     EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || rec.OWNER || '.' || rec.TABLE_NAME; 
     EXECUTE IMMEDIATE 'INSERT /*+ APPEND */  INTO ' || rec.OWNER || '.' || rec.TABLE_NAME || ' SELECT * FROM ' || rec.OWNER || '.' || rec.TABLE_NAME || '@' || x_db_link_name; 
     COMMIT;
   EXCEPTION
     WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE(rec.OWNER || '.' || rec.TABLE_NAME);
   END;
 END LOOP;
END;