How to use Oracle Data Pump (expdp/impdp) for data migration

Oracle Data Pump (expdp/impdp) enables efficient data migration via server-side export and import. First, create a directory object: CREATE DIRECTORY dpump_dir AS '/u01/oracle/dpump'; then grant privileges: GRANT READ, WRITE ON DIRECTORY dpump_dir TO system. Use expdp to export schemas, tables, or full databases: expdp system/password@db10g DIRECTORY=dpump_dir DUMPFILE=export.dmp LOGFILE=export.log SCHEMA=scott. For imports, use impdp with remap options: impdp system/password@db20g DIRECTORY=dpump_dir DUMPFILE=export.dmp LOGFILE=import.log REMAP_SCHEMA=scott:scott_new. Control jobs interactively using ATTACH, STATUS, STOP_JOB, or query DBA_DATAPUMP_JOBS for monitoring.

Oracle Data Pump (expdp and impdp) is a powerful utility for high-speed data and metadata movement between Oracle databases. It's commonly used for data migration, backups, and database upgrades. Unlike the older exp/imp tools, Data Pump runs server-side and offers better performance, filtering options, and parallel processing.

Create Directory Object

Before using expdp or impdp, you must define a directory object in the database that points to a physical server path. This is where dump files will be read from or written to.

• The directory must exist on the database server.
• The Oracle user (e.g., SYSTEM) needs read/write privileges on it.

Export Data with expdp

Use expdp to export schema, table, or full database content into dump files.

• DIRECTORY: References the Oracle directory object.
• DUMPFILE: Name of the output file.
• LOGFILE: Log of the export operation.
• SCHEMAS: Exports specific schema(s).
• You can also use TABLES, FULL=Y, or QUERY for filtered exports.

Import Data with impdp

Use impdp to import data from dump files into a target database.

• REMAP_SCHEMA: Changes schema owner during import.
• REMAP_TABLESPACE: Redirects objects to a different tablespace.
• TABLE_EXISTS_ACTION=REPLACE or APPEND handles existing tables.
• Add PARALLEL=4 to speed up large imports.

Monitor and Control Jobs

Data Pump jobs can be monitored and interactively controlled.

• Run expdp or impdp without starting immediately by adding ATTACH=job_name.
• Type STATUS to see progress.
• Type STOP_JOB to pause, then resume later.
• Query DBA_DATAPUMP_JOBS to list active jobs.

Data migration with Oracle Data Pump is efficient and flexible. Ensure directory access, test with small datasets first, and use remap options when moving between environments. Basically, expdp creates the backup, impdp restores it—customize as needed.

The above is the detailed content of How to use Oracle Data Pump (expdp/impdp) for data migration. For more information, please follow other related articles on the PHP Chinese website!