oracle expdp/impdp 使用 parfile 做多表匯出/匯入

Parfile 原文

A parameter file allows you to specify Data Pump parameters within a file, and then that file can be specified on the command line instead of entering all the individual commands. This can be useful if you use the same parameter combination many times. The use of parameter files is also highly recommended if you are using parameters whose values require the use of quotation marks.

簡單的說，當執行expdp時，需要多張表匯出的話，單一行匯出的指令會寫得特別的長，因此使用parfile可以將指令寫成一個file檔，對於有多表匯出的需求時，可以用","分開，經常匯出或維護時，能夠快速方便確認與修改

Example: 一行指令

$ expdp system/password tables=schema.table1,schema.table2,schema.table3 directory=dump dumpfile=expdp_%U.dmp logfile=expdp.log parallels=8 compression=all 

$ expdp system/password include=TABLE:\IN(\'schema.table1\',\'schema.table2\',\'schema.table3\')\" directory=dump dumpfile=expdp_%U.dmp logfile=expdp.log parallels=8 compression=all 

Example: use parfile

$ cat expdp.par
userid=system/password
directory=dump
dumpfile=expdp_%U
logfile=expdp.log
parallels=8
compression=all
tables=schema.table1,
schema.table2,
schema.table3

$ expdp parfile=expdp.par

Export: Release 11.2.0.2.0 - Production on Tue Mar 22 10:00:36 2022

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_TABLE_03":  system/******** parfile=expdp_mt.par 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 212.6 GB
. . exported "SCHEMA"."TABLE1"  45.70 GB 582743012 rows
. . exported "SCHEMA"."TABLE2"  40.63 GB 582748643 rows
. . exported "SCHEMA"."TABLE3"  34.28 GB 1390225941 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_03" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_03 is:
  /dmp/expdp_01.dmp
  /dmp/expdp_02.dmp
  /dmp/expdp_03.dmp
  /dmp/expdp_04.dmp
  /dmp/expdp_05.dmp
  /dmp/expdp_06.dmp
  /dmp/expdp_07.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_03" successfully completed at 10:26:09

從上述的例子來看，使用parfile，對於匯出多張表的時候，只需要維護好parfile，而不需要打一長串的指令或指令太長，需要在結尾輸入換行符號"\"