Hello Friend's,
Oracle Data Pump (expdp and impdp) is a powerful utility for high-speed data movement between Oracle databases. It offers several modes of operation to suit different backup, migration, and cloning requirements. In this blog, we’ll explore the different types of Oracle Data Pump exports (expdp) and imports (impdp) with practical examples.
1. Full Database Export and Import
This mode exports or imports the whole database, including all schemas, tables, data, and metadata.
Example: Full Database Export (expdp)
expdp system/password@Primdb full=Y directory=DATA_PUMP_DIR dumpfile=full_db.dmp logfile=full_db.log
Example: Full Database Import (impdp)
impdp system/password@Primdb full=Y directory=DATA_PUMP_DIR dumpfile=full_db.dmp logfile=full_imp.log
Use Case:
Full database migration.
Disaster recovery.
2. Schema-Level Export and Import
Exports or imports all objects of a given schema.
Example: Schema Export (expdp)
expdp system/password@Primdb schemas=HR,SCOTT directory=DATA_PUMP_DIR dumpfile=schema_hr_scott.dmp logfile=schema_exp.log
Example: Schema Import (impdp)
impdp system/password@Primdb schemas=HR,SCOTT directory=DATA_PUMP_DIR dumpfile=schema_hr_scott.dmp logfile=schema_imp.log
Use Case:
Migrating individual application schemas.
Copying users' data from one database to another.
3. Table-Level Export and Import
Exports or imports individual tables and their data.
Example: Table Export (expdp)
expdp system/password@Primdb tables=HR.EMPLOYEES,HR.DEPARTMENTS directory=DATA_PUMP_DIR dumpfile=tables_hr.dmp logfile=tables_exp.log
Example: Table Import (impdp)
impdp system/password@Primdb tables=HR.EMPLOYEES,HR.DEPARTMENTS directory=DATA_PUMP_DIR dumpfile=tables_hr.dmp logfile=tables_imp.log
Use Case:
Extracting individual tables for reporting.
Transferring tables from one environment to another.
4. Tablespace-Level Export and Import
Exports or imports all objects in a tablespace.
Example: Tablespace Export (expdp)
expdp system/password@Primdb tablespaces=USERS directory=DATA_PUMP_DIR dumpfile=tbs_users.dmp logfile=tbs_exp.log
Example: Tablespace Import (impdp)
impdp system/password@Primdb tablespaces=USERS directory=DATA_PUMP_DIR dumpfile=tbs_users.dmp logfile=tbs_imp.log
Use Case:
Migrating a tablespace to another database.
Reorganizing storage.
5. Transportable Tablespace (TTS) Export and Import
Transports tablespaces between databases by exporting metadata only and copying data files.
Example: Transportable Tablespace Export (expdp)
expdp system/password@Primdb transport_tablespaces=USERS directory=DATA_PUMP_DIR dumpfile=tts_users.dmp logfile=tts_exp.log
Example: Transportable Tablespace Import (impdp)
impdp system/password@Primdb transport_datafiles='/path/to/users01.dbf' directory=DATA_PUMP_DIR dumpfile=tts_users.dmp logfile=tts_imp.log
Use Case:
Rapid migration of big data sets.
Transferring read-only data between databases.
6. Network-Based Import (Data Pump Over Database Link)
Imports data from a source database directly without intermediate dump files.
Example: Network Import (impdp)
impdp system/password@target_db network_link=source_db schemas=HR directory=DATA_PUMP_DIR logfile=network_imp.log
Use Case:
Real-time data synchronization.
Prevention of dump file storage.
ConversionConversion EmoticonEmoticon