兩隻小虎,一隻豬

隨著資料庫不斷的成長，每隔一段時間就需要將資料從線上環境Archive出來
Oracle 提供Datapump 這個工具來做資料備份的動作

要使用Datapump 除了可以寫成PL/SQL 來呼叫DBMS_DATAPUMP之外
例一個方式就是透過其他程式來呼叫

由於我對PL/SQL不是那麼熟悉
所以試了一下用Java 來呼叫expdp 的方式來執行DATAPUMP

1.先產生相關參數檔

public void writeDataPumpConfig()
{
  try
  {
    FileWriter fwriter = new FileWriter("./DataPump.cfg", false);
    fwriter.write("DIRECTORY=ARCHIVE_PUMP" + "\n");
    fwriter.write("DUMPFILE=DumpData.dmp" + "\n");
    fwriter.write("JOB_NAME=DumpJob" + "\n");
    fwriter.write("LOGFILE=DumpData.log" + "\n");
    fwriter.write("TABLES=ORDER" + "\n");
    fwriter.write("QUERY=\" WHERE FAB_ID='E' AND DATA_DATE ='2009-08-23' \"" + "\n");
    fwriter.flush();
  } catch(Exception e){
    System.out.println("Exception Message : " + e.toString());
  }
}
           

2. 呼叫expdp, 並且接收expdp 傳回的message

public void callDataPump ()
{
  String line;
  OutputStream stdin = null;
  InputStream stdout = null;
  InputStream stderr = null;
  BufferedReader brCleanUp ;
  try
  {
    Runtime rt = Runtime.getRuntime() ;
    Process process = rt.exec("expdp ID/PASSWORD@ORACLE_SIE parfile=./DataPump.cfg");
    
    stdin    = process.getOutputStream ();
    stdout   = process.getInputStream ();
    stderr   = process.getErrorStream ();
         
    // "write" the parms into stdin
    line = "param1" + "\n";   
    stdin.write(line.getBytes() );
    stdin.flush();

    line = "param2" + "\n";
    stdin.write(line.getBytes() );
    stdin.flush();

    line = "param3" + "\n";
    stdin.write(line.getBytes() );
    stdin.flush();

    stdin.close();
         
    // clean up if any output in stdout
    brCleanUp =  new BufferedReader (new InputStreamReader (stdout));
         
    while ((line = brCleanUp.readLine ()) != null) {
      System.out.println ("[Stdout] " + line);
    }
    brCleanUp.close();
    
    // clean up if any output in stderr
    brCleanUp =  new BufferedReader (new InputStreamReader (stderr));
    while ((line = brCleanUp.readLine ()) != null) {
      System.out.println ("[Stderr] " + line);
    }
    brCleanUp.close();
    
    process.destroy() ;
  }catch (Exception e){
    System.out.println(e.toString());
  }
}