Mir Sayeed Hassan – Oracle Blog

Different Methodologies of Working with Data Pump scenarios in Oracle Database 19C

Oracle Data Pump is a high-performance utility for performing the database data. This dumpfile can be used to import the same data and structures into same database or another Oracle database environment for migration purpose, making it a key tool for backup, migration, and data transfer operations.

we use the Data Pump (expdp/impdp) offers significant enhancements in speed, scalability, and flexibility, making it the preferred choice for modern database management.

Server-Side Utility: Oracle Data Pump runs entirely on the database server, unlike the legacy expdp/impdp tools which were client-based.

Job Restart Capability: Supports restarting jobs from the point of interruption, eliminating the need to rerun the entire process.

Direct DB Transfer: Enables data movement between databases without creating intermediate dump files.

Metadata-Only: Allows exporting only database structures (schemas, tables, objects) without actual data.

Flexible Execution Options: Can be executed using expdp/impdp commands or through the DBMS_DATAPUMP package for advanced control.

Required Privileges: The EXP_FULL_DATABASE role is necessary to export data from schemas other than the user’s own.

How to Export Data Using Oracle Data Pump 

Full DB Level syntax:

expdp system/(password) or If you are using PDB expdp system/(password)@pdb1
FULL=Y 
DUMPFILE=fulldb_dump%U.dmp
FILESIZE=4G
PARALLEL=3 
LOGFILE=fulldb_dump.log

Tablespace Level:

expdp system/(password) or If you are using PDB expdp system/(password)@pdb1
DUMPFILE=users_tbs.dmp
LOGFILE=users_tbs.log
TABLESPACES=users

Note: if you want to include the multiple tablesapce, you can use it like TABLESPACES=users, undo

Schema Level

expdp system/(password) or If you are using PDB expdp system/(password)@pdb1
SCHEMAS=schema_name (ex: like HR or your prod schema)
DUMPFILE=schema_name.dmp
LOGFILE=schema_name.log

Note: if you want to include the multiple schemas, you can use it like schemas=hr, mir

Table Level 

expdp system/(password) or If you are using PDB expdp system/(password)@pdb1
TABLES=schema_name.table_name (ex: like HR.EMPLOYEES)
DUMPFILE=table_name.dmp
LOGFILE=table_name.log

Note: 

FULL=Y → Exports the complete database
DUMPFILE → Specifies multiple dump files using %U for parallel processing
FILESIZE=4G → Limits each dump file size to 4 GB
PARALLEL=5 → Enables parallel execution with 3 worker processes
LOGFILE → export operation details for monitoring and troubleshooting

If one or more tables: TABLES=HR.employees,HR.departments.

Important Note: 

– During export, index data is not included in the dump file,  Only the index definitions (structure) are exported.

– Indexes are automatically recreated during the import process opeartion, ensuring optimal performance & consistency.

Some of the Additional Parameter are using in expdp are:

CONTENT: Controls export type — DATA_ONLY (data), METADATA_ONLY (structure), or ALL (both, default).

INCLUDE It allows selective export of specific object types or objects using the syntax INCLUDE=object_type[:name_clause.

EXCLUDE: Specifies which object types or objects should be omitted from the export using the syntax EXCLUDE=object_type[:name_clause].

ESTIMATE_ONLY=YES: Calculates required export disk space using block/statistics methods without executing the export; default method is BLOCKS.

FLASHBACK_SCN: Exports data as of a specific System Change Number (SCN), ensuring a consistent point-in-time snapshot. 

FLASHBACK_TIME: Exports data as of a specified timestamp, providing a consistent point-in-time view of the database during the export operation. 

NETWORK_LINK: Specifies that a remote database, accessed through a database link (db_link), is used as the source for the export operation, enabling direct data transfer without creating dump files. 

DUMPFILE: Specifies dump file location and name (directory:file); with %U enables auto-generated sequential files (01, 02…) for parallel exports.

FILESIZE: Defines the maximum size for each dump file; supports units such as bytes, KB, MB, or GB, with bytes as the default.

LOGFILE: Specifies the log file location & name using the syntax LOGFILE=directory:file, where directory is a database directory object and file is the log filename or just logifle name.

DIRECTORY: Directory object used as the location for both dump and log files during the export operation.

NOLOGFILE=Y: Disables log file generation during the export operation, preventing creation of a log file.

JOB_NAME: Assigns a user-defined name to the Data Pump job, which is visible in data dictionary views; if not specified, a system-generated name is used by default. 

COMPRESSION: It controls metadata compression during export; supported options are METADATA_ONLY and NONE. Compression applies only to metadata, not to actual table data. 

QUERY: Filters exported table data using a SQL WHERE clause with optional ordering.

Creating directory and assigning the privileges. 

Example: 
CREATE DIRECTORY dump_dir_name AS ‘/u01/app/oracle/data_pump/directory_name’;

Ex: GRANT READ, WRITE ON DIRECTORY directory_name TO sys;
GRANT READ, WRITE ON DIRECTORY directory_name TO system;
GRANT READ, WRITE ON DIRECTORY directory_name TO mir;

Import Operation.

Oracle Data Pump Import (impdp) is a server-side utility used to efficiently import data & metadata from dump files into an Oracle database, this will be supporting full, schema, or table-level operations.

Full Database Import:

impdp system/(password) or If you are using PDB expdp system/(password)@pdb1
FULL=Y
DIRECTORY=dp_dir
DUMPFILE=full.dmP
LOGFILE=full_import.log 

Schema-Level Import:

impdp system/(password) or If you are using PDB expdp system/(password)@pdb1
SCHEMAS=hr
DIRECTORY=dp_dir
DUMPFILE=hr_schema.dmp
LOGFILE=hr_import.log

Table-Level Import

impdp system/(password) or If you are using PDB expdp system/(password)@pdb1
TABLES=hr.employees,hr.departments
DIRECTORY=dp_dir
DUMPFILE=tables.dmp
LOGFILE=tables_import.log 

Key Points

• DIRECTORY → Specifies the database directory object containing dump files

• DUMPFILE → Defines the dump file to import

• LOGFILE → Stores import operation details

• FULL / SCHEMAS / TABLES → Controls the scope of import.

=====Hence this will be useful for the expdp/impdp operation for all dbas=====