DATAPUMP

 DATAPUMP 

What are the differences between expdp and exp (Data Pump or normal exp/imp)?

Data Pump is server centric (files will be at server).

Data Pump has APIs, from procedures we can run Data Pump jobs.In Data Pump, we can stop and restart the jobs.

Data Pump will do parallel execution.Tapes & pipes are not supported in Data Pump.Data Pump consumes more undo tablespace.Data Pump import will create the user, if user doesn’t exist.

 

Why expdp is faster than exp (or) why Data Pump is faster than conventional export/import?

Data Pump is block mode, exp is byte mode.

Data Pump will do parallel execution.

Data Pump uses direct path API.

 

How to improve expdp/ impdp performance?

Using parallel option which increases worker threads. This should be set based on the number of cpus.

 

In Data Pump, where the jobs info will be stored (or) if you restart a job in Data Pump, how it will know from where to resume?

Whenever Data Pump export or import is running, Oracle will create a table with the JOB_NAME and will be deleted once the job is done. From this table, Oracle will find out how much job has completed and from where to continue etc.

How to import only metadata?

CONTENT= METADATA_ONLY

How to import into different user/tablespace/datafile/table?

REMAP_SCHEMA

REMAP_TABLESPACE

REMAP_DATAFILE

REMAP_TABLE

REMAP_DATA

How to append the table rows in the existing table?

TABLE_EXIST_ACTIONS=APPEND

 

Using Data Pump, how to export in higher version (11g) and import into lower version (10g), can we import to 9i?

Import data pump can always read export datapump dumpfile sets created by older versions of database. In your case it works, normal expdp on 10g and impdp on 11g

VERSION parameter in datapump is for other way around, if you want to import data taken from 11g into 10g database you need to specify VERSION while taking backup.