Sunday, September 21, 2008

Data Pump - Operating on Specific Objects


Ever had a need to export only certain procedures from one user to be recreated in a different database or user? Unlike the traditional export utility, Data Pump allows you to export only a particular type of object. For instance, the following command lets you export only procedures, and nothing else--no tables, views, or even functions:

expdp bob/iclaim directory=DPDATA1 dumpfile=expprocs.dmp include=PROCEDURE

To export only a few specific objects--say, function FUNC1 and procedure PROC1--you could use

expdp bob/iclaim directory=DPDATA1 dumpfile=expprocs.dmp
include=PROCEDURE:\"=\'PROC1\'\",FUNCTION:\"=\'FUNC1\'\"

This dumpfile serves as a backup of the sources. You can even use it to create DDL scripts to be used later. A special parameter called SQLFILE allows the creation of the DDL script file.

impdp bob/iclaim directory=DPDATA1 dumpfile=expprocs.dmp sqlfile=procs.sql

This instruction creates a file named procs.sql in the directory specified by DPDATA1, containing the scripts of the objects inside the export dumpfile. This approach helps you create the sources quickly in another schema.

Using the parameter INCLUDE allows you to define objects to be included or excluded from the dumpfile. You can use the clause INCLUDE=TABLE:"LIKE 'TAB%'" to export only those tables whose name start with TAB. Similarly, you could use the construct INCLUDE=TABLE:"NOT LIKE 'TAB%'" to exclude all tables starting with TAB. Alternatively you can use the EXCLUDE parameter to exclude specific objects.

Expdp and impdp help

expdp help=y

Similarly, impdp help=y will show all the parameters in DPI.

While Data Pump jobs are running, you can pause them by issuing STOP_JOB on the DPE or DPI prompts and then restart them with START_JOB. This functionality comes in handy when you run out of space and want to make corrections before continuing.

No comments: