Oracle Data Pump

Oracle 10g Data Pump

1) Dump file are create on the server side.

2) Data moving is much faster, with the use of Parallelism.

3) No need of commit, it is taken care by datapump

4) Buffer is no longer required to be specified, and also commit is done automatically

5) We could stop/start the data pump job.

We have to create directory for storing the dumpfile

SQL>create direcoty dp as '~/path/to dir/'

Grant permision to users on above, if they are required to use it

SQL>grant read,write on db to <user>;

Now lets export

expdp scott/tiger dumpfile='dp:exp.dmp' logfile=dp:exp.log tables=employee,dept job_name=tables_dump

Datapump Parameters

remap_schema=source_schema:target_schema

content=all|data_only|metadata

exclude=triggers //Include everything except trigger

include=constraint //include just constraint

query=dept:"where deptno=20"

sqlfile=dir:file //DDL statement for the items you are exporitng

network_link=<database_link>

impdp two/two2 DIRECTORY=dp NETWORK_LINK=src tables=TWO.TWOEMP remap_schema=two:three PARALLEL=4 LOGFILE=impdp.log

Tables Export / Import

expdp system/XX@SID tables=EMP,DEPT directory=DPDIR dumpfile=tables.dmp logfile=expdp.log

impdp system/XX@SID tables=EMP,DEPT directory=DPDIR dumpfile=tables.dmp logfile=expdp.log

Database Export / Import

expdp system/XX@SID full=Y directory=DPDIR dumpfile=db.dmp logfile=expdp.log

impdp system/XX@SID full=Y directory=DPDIR dumpfile=db.dmplogfile=impdp.log

Schema Export / Import

expdp system/XX@SID schemas=SCOTT directory=DPDIR dumpfile=schemaexp.dmp logfile=expdp.log

impdp system/XX@SID schemas=SCOTT directory=DPDIR dumpfile=schemaimp.dmp logfile=expdp.log

Structure Export / Metadata

expdp system/XX@SID full=Y directory=DPDIR dumpfile=db_structure.dmp logfile=expdp_structure.log content=METADATA_ONLY

impdp system/XX@SID directory=DPDIR dumpfile=expdp.dmp SQLFILE=expfull.sql