ADVERTISEMENT

.par File in Oracle Data Pump

A .par file is a plain text file used with Oracle expdp or impdp commands to pass parameters. It keeps your commands short, readable, and reusable — ideal for scheduled and production jobs.

Example: Export Multiple Tables

Filename: export_tables.par

tables=(HR.EMPLOYEES, HR.DEPARTMENTS)
directory=DATA_PUMP_DIR
dumpfile=hr_tables.dmp
logfile=hr_tables.log

This exports selected tables from the HR schema to the path linked to DATA_PUMP_DIR.

How to Run the Export

nohup expdp '"/ as sysdba"' parfile=export_tables.par &
  • Uses parameters from the .par file
  • Job runs in background and continues after logout

Common Export .par File Examples

TaskSample .par File Content
Schema Exportschemas=HRdirectory=DATA_PUMP_DIRdumpfile=hr.dmplogfile=hr.log
Export Tablestables=(HR.EMPLOYEES, HR.DEPARTMENTS)directory=DATA_PUMP_DIRdumpfile=hr_tab.dmplogfile=hr_tab.log
Full Exportfull=ydirectory=DATA_PUMP_DIRdumpfile=full.dmplogfile=full.log
Tablespace Exporttablespaces=USERSdirectory=DATA_PUMP_DIRdumpfile=users.dmplogfile=users.log
Query-Based Exporttables=HR.EMPLOYEESquery="WHERE department_id=10"directory=DATA_PUMP_DIRdumpfile=dept10.dmplogfile=dept10.log
Parallel Exportschemas=HRparallel=4dumpfile=hr_%U.dmpdirectory=DATA_PUMP_DIRlogfile=hr_parallel.log
Exclude Objectsschemas=HRexclude=TABLE:"IN ('EMP_TEMP','DEPT_OLD')"directory=DATA_PUMP_DIRdumpfile=hr_clean.dmplogfile=hr_exclude.log
Include Specific Objectsschemas=HRinclude=TABLE:"= 'EMPLOYEES'"directory=DATA_PUMP_DIRdumpfile=emp_only.dmplogfile=emp_only.log
Compressed Dump Fileschemas=HRcompression=alldirectory=DATA_PUMP_DIRdumpfile=hr_compressed.dmplogfile=hr_compressed.log
Network Mode Exportnetwork_link=REMOTE_DB_LINKschemas=HRdirectory=DATA_PUMP_DIRdumpfile=hr_net.dmplogfile=hr_net.log

Example: Import Tables Using .par File

Filename: import_tables.par

dumpfile=hr_tables.dmp
directory=DATA_PUMP_DIR
tables=(HR.EMPLOYEES, HR.DEPARTMENTS)
logfile=hr_import.log

How to Run the Import

nohup impdp '"/ as sysdba"' parfile=import_tables.par &

This imports selected tables from the specified dump file.

Notes

  • To create and manage Oracle directories, refer to:
    🔗 Create Directory in Oracle
  • Use tables=(...) for clean syntax when exporting multiple tables
  • For large exports, use dumpfile=exp_%U.dmp with parallel=4
  • Always check the .log file to verify success or errors
  • Avoid hardcoding passwords inside .par files

ADVERTISEMENT