LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon