Oracle Data Pump export per schema with size estimate and timing
Prepare a Data Pump directory, estimate export size and export several schemas with expdp while measuring elapsed time.
This article shows how to prepare a Data Pump directory, estimate the size of an export, then export several Oracle schemas with expdp while measuring the total elapsed time.
Preparation
Create the Oracle directory that points to a file system folder and grant read and write access:
CREATE DIRECTORY DATA_PUMP_DIR_U2 AS '/u2/oradata/DATA_PUMP';
GRANT read, write ON DIRECTORY DATA_PUMP_DIR_U2 TO system;
Estimate the size of an export
With ESTIMATE_ONLY=YES, expdp computes the size without writing any data:
expdp system/<password> directory=DATA_PUMP_DIR_U2 dumpfile=exp_full_20190814.dmp logfile=exp_full_20190814.log full=y ESTIMATE_ONLY=YES
Possible values: ESTIMATE_ONLY=[YES | NO].
Schema-by-schema export with timing
STARTTIME=$(date +%s)
date=$(date '+%Y%m%d%H%M%S')
expdp system/<password> schemas=SCHEMA_A compression=all directory=DATA_PUMP_DIR_U2 dumpfile=exp_SCHEMA_A_${date}.dmp logfile=exp_SCHEMA_A_${date}.log
expdp system/<password> schemas=SCHEMA_B compression=all directory=DATA_PUMP_DIR_U2 dumpfile=exp_SCHEMA_B_${date}.dmp logfile=exp_SCHEMA_B_${date}.log
expdp system/<password> schemas=SCHEMA_C compression=all directory=DATA_PUMP_DIR_U2 dumpfile=exp_SCHEMA_C_${date}.dmp logfile=exp_SCHEMA_C_${date}.log
ENDTIME=$(date +%s)
elapsed=$((ENDTIME - STARTTIME))
echo "It takes ${elapsed} seconds to complete this task..."
Import
impdp user@orcl directory=dumpdir dumpfile=fulldb.dmp logfile=fulldb.log full=y
Notes
- A password on the command line is visible in the process list; prefer an interactive prompt or a protected parameter file.
- The directory used in
expdpmust be the one on which the privileges were granted.