PART III in Software

Painting Code 39 in Software PART III

Data Pump is commonly used for extracting large amounts of data from one database and inserting it into another, but it can also be used to extract other information such as PL/SQL code or various object definitions There are several interfaces: commandline utilities, Enterprise Manager, and a PL/SQL API Whatever purpose and technique are used, the files are always in the Data Pump proprietary format It is not possible to read a Data Pump file with any tool other than Data Pump

Fine-grained object and data selection facilities mean that Data Pump can export either the complete database or any part of it It is possible to export table definitions with or without their rows; PL/SQL objects; views; sequences; or any other object type If exporting a table, it is possible to apply a WHERE clause to restrict the rows exported (though this may make direct path impossible) or to instruct Data Pump to export a random sample of the table expressed as a percentage

Parallel processing can speed up Data Pump operations Parallelism can come at two levels: the number of Data Pump worker processes, and the number of parallel execution servers each worker process uses An estimate facility can calculate the space needed for a Data Pump export, without actually running the job The Network Mode allows transfer of a Data Pump data set from one database to another without ever staging it on disk This is implemented by a Data Pump export job on the source database writing the data over a database link to the target database, where a Data Pump import job reads the data from the database link and inserts it Remapping facilities mean that objects can be renamed or transferred from one schema to another and (in the case of data objects) moved from one tablespace to another as they are imported When exporting data, the output files can be compressed and encrypted

The executables expdb and impdp are installed into the ORACLE_HOME/bin directory Following are several examples of using them Note that in all cases the command must be a single one-line command; the line breaks are purely for readability To export the entire database,

expdp system/manager@orcl11g full=y parallel =4 dumpfile=datadir1:full1_%Udmp, datadir2:full2_%Udmp, datadir3:full3_%Udmp, datadir4:full4_%Udmp, filesize=2G compression=all

This command will connect to the database as user SYSTEM and launch a full Data Pump export, using four worker processes working in parallel Each worker will generate its own set of dump files, uniquely named according to the %U template, which generates strings of eight unique characters Each worker will break up its output into files of 2GB (perhaps because of underlying file system restrictions) of compressed data A corresponding import job (which assumes that the files generated by the export have all been placed in one directory) would be

impdb system/manager@dev11g full=y directory=data_dir parallel=4 dumpfile=full1_%Udmp,full2_%Udmp,full3_%Udmp,full4_%Udmp

This command makes a selective export of the PL/SQL objects belonging to two schemas:

expdp system/manager schemas=hr,oe directory=code_archive dumpfile=hr_oe_codedmp include=function,include=package,include=procedure,include=type

This command will extract everything from a Data Pump export that was in the HR schema, and import it into the DEV schema:

The Database Control interface to Data Pump generates the API calls that are invoked by the expdp and impdp utilities, but unlike the utilities it makes it possible to see the scripts and if desired copy, save, and edit them To reach the Data Pump facilities, from the database home page select the Data Movement tab In the Move Row Data section, there are four links that will launch wizards: Export to Export Files Import from Database Define Data Pump export jobs Define Data Pump import jobs Define a Data Pump network mode import