Oracle Database

Data Pump

Introduction#

Following are the steps to create a data pump import/export:

Monitor Datapump jobs

Datapump jobs can be monitored using

1. data dictionary views:

   select * from dba_datapump_jobs; 
   SELECT * FROM DBA_DATAPUMP_SESSIONS; 
   select username,opname,target_desc,sofar,totalwork,message from V$SESSION_LONGOPS where username = 'bkpadmin';

2. Datapump status:

  • Note down the job name from the import/export logs or data dictionary name and

  • Run attach command:

  • type status in Import/Export prompt

    impdp / attach=<SYS_IMPORT_SCHEMA_01> Import> status

Press press CTRL+C to come out of Import/Export prompt

Step 3/6 : Create directory

create or replace directory DATAPUMP_REMOTE_DIR as '/oracle/scripts/expimp';

Step 7 : Export Commands

Commands:

expdp <bkpadmin>/<bkp123>  parfile=<exp.par>

*Please replace the data in <> with appropriate values as per your environment. You can add/modify parameters as per your requirements. In the above example all the remaining parameters are added in parameter files as stated below: *

  • Export Type : User Export

  • Export entire schema

  • Parameter file details [say exp.par] :

    schemas= directory= DATAPUMP_REMOTE_DIR dumpfile=.dmp logfile=exp_.log


  • Export Type : User Export for large schema

  • Export entire schema for large datasets: Here the export dump files will be broken down and compressed. Parallelism is used here (Note : Adding parallelism will increase the CPU load on server)

  • Parameter file details [say exp.par] :

    schemas= directory= DATAPUMP_REMOTE_DIR dumpfile=%U.dmp logfile=exp__.log compression = all parallel=5


  • Export Type : Table Export [ Export set of tables]

  • Parameter file details [say exp.par] :

    tables= tname1, tname2, tname3 directory= DATAPUMP_REMOTE_DIR dumpfile=.dmp logfile=exp_.log


Step 9 : Import Commands

Prerequisite:

  • Prior to user import it is a good practice to drop the schema or table imported.

Commands:

impdp <bkpadmin>/<bkp123>  parfile=<imp.par>

*Please replace the data in <> with appropriate values as per your environment. You can add/modify parameters as per your requirements. In the above example all the remaining parameters are added in parameter files as stated below: *

  • Import Type : User Import

  • Import entire schema

  • Parameter file details [say imp.par] :

    schemas= directory= DATAPUMP_REMOTE_DIR dumpfile=.dmp logfile=imp_.log


  • Import Type : User Import for large schema

  • Import entire schema for large datasets: Parallelism is used here (Note : Adding parallelism will increase the CPU load on server)

  • Parameter file details [say imp.par] :

    schemas= directory= DATAPUMP_REMOTE_DIR dumpfile=%U.dmp logfile=imp__.log parallel=5


  • Import Type : Table Import [ Import set of tables]

  • Parameter file details [say imp.par] :

    tables= tname1, tname2, tname3 directory= DATAPUMP_REMOTE_DIR dumpfile=.dmp logfile=exp_.log TABLE_EXISTS_ACTION= <APPEND /SKIP /TRUNCATE /REPLACE>


1. Datapump steps

Source Server [Export Data] Target Server [Import Data]
1. Create a datapump folder that will contain the export dump files 4. Create a datapump folder that will contain the import dump files
2. Login to database schema that will perform the export. 5. Login to database schema that will perform the import.
3. Create directory pointing to step 1. 6. Create directory pointing to step 4.
7. Run Export Statements.
8. Copy/SCP the dump files to Target Server.
9. Run Import statements
10. check data ,compile invalid objects and provide related grants

Copy tables between different schemas and tablespaces

 expdp <bkpadmin>/<bkp123> directory=DATAPUMP_REMOTE_DIR dumpfile=<customer.dmp>


 impdp <bkpadmin>/<bkp123> directory=DATAPUMP_REMOTE_DIR dumpfile=<customer.dmp> remap_schema=<source schema>:<target schema> remap_tablespace=<source tablespace>:<target tablespace>

This modified text is an extract of the original Stack Overflow Documentation created by the contributors and released under CC BY-SA 3.0 This website is not affiliated with Stack Overflow