Oracle's Data Pump utility (expdp and impdp)

 

Prerequisites for expdp:

• Oracle Directory Object

CREATE DIRECTORY data_pump_dir AS '/u01/app/oracle/datapump_backups';

-- Grant to the user performing the export

GRANT READ, WRITE ON DIRECTORY data_pump_dir TO your_user; 

• User Permissions

GRANT EXP_FULL_DATABASE TO your_user;

Schema Export:

expdp system/password@ORCLDB SCHEMAS=SCOTT DIRECTORY=data_pump_dir DUMPFILE=scott_schema.dmp LOGFILE=scott_schema.log

Table Export:

expdp hr/hr_pass@ORCLDB TABLES=hr.employees,hr.jobs DIRECTORY=data_pump_dir DUMPFILE=hr_tables.dmp LOGFILE=hr_tables.log

Full Database Export:

expdp system/password@ORCLDB FULL=Y DIRECTORY=data_pump_dir DUMPFILE=full_db.dmp LOGFILE=full_db.log

Export with Data Filtering (QUERY parameter):

expdp hr/hr_pass@ORCLDB TABLES=hr.employees DIRECTORY=data_pump_dir DUMPFILE=employees_filtered.dmp LOGFILE=employees_filtered.log QUERY=employees:"WHERE department_id = 10"

Export Metadata Only:

expdp hr/hr_pass@ORCLDB SCHEMAS=HR DIRECTORY=data_pump_dir DUMPFILE=hr_metadata.dmp LOGFILE=hr_metadata.log CONTENT=METADATA_ONLY

Using a Parameter File (recommended for complex exports): 

Create a file with export parameters:

# exp_scott.par

SCHEMAS=SCOTT

DIRECTORY=data_pump_dir

DUMPFILE=scott_schema.dmp

LOGFILE=scott_schema.log

COMPRESSION=ALL

PARALLEL=4

Then run:

expdp username/password@ORCLDB PARFILE=exp_scott.par

Advanced expdp Scripting Concepts:

Excluding/Including Objects :

• EXCLUDE=TABLE:"IN ('AUDIT_TRAIL', 'TEMP_TABLE')"
• INCLUDE=TABLE:"LIKE 'EMP%'"

Flashback SCN/Time: 

Export data as of a specific SCN or timestamp.

• FLASHBACK_SCN=1234567 
• FLASHBACK_TIME="TO_TIMESTAMP('2023-05-27 10:00:00', 'YYYY-MM-DD HH24:MI:SS')"

PARALLEL: 

Use PARALLEL=N to specify the number of parallel worker processes. This significantly speeds up exports, especially for large databases, but requires multiple dump files (DUMPFILE=full_db_%U.dmp).

COMPRESSION: 

COMPRESSION=ALL (or METADATA_ONLY, DATA_ONLY) to compress dump files.

ENCRYPTION: 

For sensitive data, 

ENCRYPTION=DATA_ONLY 

and 

ENCRYPTION_PASSWORD=your_secure_password.

CONTENT: 

CONTENT=ALL (default), METADATA_ONLY, or DATA_ONLY.

Network Exports (via DB_LINK): 

expdp can export directly over a database link to another database without creating dump files on the source.

expdp system/password NETWORK_LINK=your_dblink SCHEMAS=SCOTT ...