Data Pump Overview

Datapump is introduced in oracle database 10g(10.2.0.1) and later

Oracle Data Pump technology enables very high-speed movement of data and 

metadata from one database to another database.

Oracle Data Pump is made up of three distinct parts

The command-line clients, expdp and impdp

The DBMS_DATAPUMP PL/SQL package (also known as the Data Pump API)

The DBMS_METADATA PL/SQL package (also known as the Metadata API)

Data Pump expdp and impdp using following Modes:

Full Database

Table

User

Tablespace

EXPDP

Full Database:

Full database mode exports the all database objects.

Table:

Table mode export the specified table in user

Example:

Table definitions

Table Data (all or selected rows)

Table indexes

User:

User mode export the all objects from users schema

Example:

Table definitions

Table Data

Owner grants

Owner indexes

Tablesapce:

Tablespace mode can be used to export the all tables in the specified tablespace

Example:

Table definitions

Table Data

Owner grants

Owner indexes

I explain following document Expdp  basic queries and how its perform it

Table MODE:

Step 1: Create the one directory

SQL> create directory DATA_PUMP as '/u01/DATA_PUMP';

SQL> grant read on directory DATA_PUMP to AIRTEL;

SQL> grant write on directory DATA_PUMP to AIRTEL;

Step 2: Export Customer table from airtel user.

Pre check:

First check the table size after take the expdp from table

Example:

expdp directory=DATA_PUMP dumpfile=CUSTOMER.dump logfile=CUSTOMER.log tables=CUSTOMER

User MODE:

Pre check:

First check the schema size and objects counts are there after  take the expdp from schema

EX:

SQL> select count(*),object_type from dba_objects where owner='DBSNMP' group by object_type;

Example:

expdp directory=DATA_PUMP dumpfile=airtel.dmp logfile=airtel.log schemas=airtel

Tablespace MODE:

Pre check:

Check the tablespace size after  take the expdp from tablespace

Example:

expdp directory=DATA_PUMP dumpfile=TELECOM_tbs.dmp logfile=TELECOM.log tablespaces=TELECOM

Full Database Mode:

Pre check:

Check the database size after take expdp from full database

Example:

expdp directory-DATA_PUMP dumpfile=full.dmp logfile=full.log full=y

IMPDP

Import utilities following modes

Full Database:

Import all objects from the export dump file

Tables:

Import specified tables to the schema.

USER:

Import all objects that belongs to the schema

Tablespace:

Import all definitions of the objects contained in the tablespace.

IMPDP process structure

New tables are created

Data is imported

Indexes are imported

Triggers are imported

Any bitmap, functional, Domain indexes are built

I explain following document impdp  basic queries and how its perform it.

Table MODE:

Scenario 1

Export Customer table from test server airtel user

impdp directory=DATA_PUMP dumpfile=CUSTOMER.dump logfile=impCUSTOMER.log tables=customer

Post check:

After imported the table check the table size and table count in same or not is target to source server.

Scenario 2

How to import particular selected tables in one user to another user

impdp directory=DATA_PUMP dumpfile=SCOTT032.dmp logfile=impSCOTT032.log tables=scott.SALGRADE,scott.BONUS remap_schema=scott:jio

User MODE:

Scenario 1

How to import schema in one database to another database  

EXAMPLE:

impdp directory=DATA_PUMP dumpfile=airtel.dmp logfile=airtel.log  schemas=airtel

Scenario 2

How to import schema object in different schemas

Remap_schema:

Syntax:

REMAP_SCHEMA=source_schema:target_schema

EXAMPLE:

impdp directory=DATA_PUMP dumpfile=SCOTT032.dmp logfile=SCOTT01.log remap_schema=SCOTT:aircel

Tablespace Mode:

Scenario 1

How to import tablespace in one database to another database

Example:

impdp directory=DATA_PUMP dumpfile=TELECOM_tbs.dmp logfile=TELECOM.log tablespaces=TELECOM

Scenario 2

How to import tablespace data’s in different tablespace

REMAP_TABLESPACE

Loads all objects from the source schema into a target schema.

Syntax:

REMAP_TABLESPACE=source_tablespace:target_tablespace

Example:

impdp directory=DATA_PUMP dumpfile=TELECOM_tbs.dmp logfile=TELECOM.log REMAP_TABLESPACE=TELECOM:REL

Full Database:

Example:

impdp directory-DATA_PUMP dumpfile=full.dmp logfile=full.log full=y


Preserve Schema credentiails Queries:-

Select 'alter user  '||username||' profile default;' from dba_users where username='<USER_NAME>';

Select 'alter user '||name||' identified by values '||''''||password||''''||';' from user$ where name='<Username>';

Datapump Frequently Asked Questions

How to import table without index?

impdp directory=DATA_PUMP_DIR dumpfile=schema.dmp logfile=reschema1.log exclude=index remap_schema=airtel:aircel

How to import index only?

impdp directory=DATA_PUMP_DIR dumpfile=schema.dmp logfile=reschema2.log include=index remap_schema=airtel:aircel

How to import table structure only?

impdp directory=DATA_PUMP_DIR dumpfile=dept.dmp logfile=dept001.log tables=sanjeev.dept content=metadata_only

If the tablespace is Read Only,Can we export objects from that tablespaces?

Yes

How do you perform Compression with EXPDP?

EXPDP itself will now compress all metadata written to the dump file and IMPDP will decompress it automatically—no more messing around at the operating system level.

Compress=Y in the parameter file of export file.

Estimating how much disk space will be consumed in a schema mode export?

expdp directory=DATA_PUMP dumpfile=airtel.dmp logfile=airtel.log schemas=airtel ESTIMATE_ONLY=y