Data Pump - Oracle Database Full Tutorial

Oracle Database - Data Pump

Start

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 <bkpadmin>/<bkp123> 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=<schema>
directory= DATAPUMP_REMOTE_DIR
dumpfile=<dbname>_<schema>.dmp
logfile=exp_<dbname>_<schema>.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=<schema>
directory= DATAPUMP_REMOTE_DIR
dumpfile=<dbname>_<schema>_%U.dmp
logfile=exp_<dbname>_<schema>.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=<dbname>_<schema>.dmp
logfile=exp_<dbname>_<schema>.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>

Import Type : User Import
Import entire schema
Parameter file details [say imp.par] :

schemas=<schema>
directory= DATAPUMP_REMOTE_DIR
dumpfile=<dbname>_<schema>.dmp
logfile=imp_<dbname>_<schema>.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=<schema>
directory= DATAPUMP_REMOTE_DIR
dumpfile=<dbname>_<schema>_%U.dmp
logfile=imp_<dbname>_<schema>.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=<dbname>_<schema>.dmp
logfile=exp_<dbname>_<schema>.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>

Read Article
https://develop.horje.com/learn/1434/reference