Export or import with Oracle Data Pump

In Oracle 10g, exp and imp have been redesigned as the Oracle Data Pump (although Oracle still ships and fully supports exp and imp). If you're used to exporting exp and imp, the Data Pump command-line programs have a syntax that will look very familiar.

Data Pump runs as a job inside the database, rather than as a stand-alone client application. This means that jobs are somewhat independent of the process that started the export or import. One machine (say a scheduled job) could start the export, while another machine (such as a DBA's laptop) can check the status of the job. Since the job is inside the database, if you want to export to a file, the first thing that you must do is create a database DIRECTORY object for the output directory, and grant access to users who will be doing exports and imports:

create or replace directory dumpdir as 'c:\';
grant read,write on directory dumpdir to scott;

Once the directory is granted, you can export a user's object with command arguments that are very similar to exp and imp:

expdp scott/tiger directory=dumpdir dumpfile=scott.dmp

While the export job is running, you can press [Ctrl]C (or the equivalent on your client) to "detach" from the export job. The messages will stop coming to your client, but it's still running inside the database. Your client will be placed in an interactive mode (with Export> prompt). To see which jobs are running, type status. If you run expdp attach=, you can attach to a running job.

Data Pump doesn't necessarily have to write to files. Now there are options to allow you to export database objects directly into a remote database over SQL*Net. You simple specify the remote option with the connect string of the remote database. This is something like a one-time database replication job.

Data Pump is much faster than the old exp and imp client commands. One new feature that really helps make it faster is the "parallel" option. With this option, the Data Pump will pump data in four different threads. For example, I ran the following job, pressed [Ctrl]C, and queried the status of the background jobs:

expdp scott/tiger directory=dumpdir dumpfile=scott2.dmp parallel=4
job_name=scott2

Export: Release 10.1.0.2.0 - Production on Friday, 31 December, 2004 14:54

Copyright (c) 2003, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 -
Production
With the Partitioning, OLAP and Data Mining options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SCOTT"."SCOTT2": scott/******** directory=dumpdir
dumpfile=scott2.dmp parallel=4 job_name=scott2
Estimate in progress using BLOCKS method...

Export> status

Job: SCOTT2
  Operation: EXPORT
  Mode: SCHEMA
  State: EXECUTING
  Bytes Processed: 0
  Current Parallelism: 4
  Job Error Count: 0
  Dump File: C:\SCOTT2.DMP
 & &  bytes written: 4,096

Worker 1 Status:
  State: EXECUTING

Worker 2 Status:
  State: WORK WAITING

Worker 3 Status:
  State: WORK WAITING

Worker 4 Status:
  State: WORK WAITING

Not only is the Data Pump running inside the database, but also, most of the command-line features are exposed from inside the database through a PL/SQL api, DBMS_DATAPUMP. For example, you can start the export job from a PL/SQL package with the following PL/SQL code:

declare
 & &  handle number;
begin
 & &  handle := dbms_datapump.open('EXPORT','SCHEMA');
 & &  dbms_datapump.add_file(handle,'SCOTT3.DMP','DUMPDIR');
 & &  dbms_datapump.metadata_filter(handle,'SCHEMA_EXPR','= ''SCOTT''');
 & &  dbms_datapump.set_parallel(handle,4);
 & &  dbms_datapump.start_job(handle);
 & &  dbms_datapump.detach(handle);
end;
/

Check out Data Pump to learn about many of its other great new features. For instance, Data Pump contains features with the ability to rename datafiles, move objects to different tablespaces, or select schema objects or schemas using wildcard patterns or expressions. The Data Pump can also act as an interface to external tables (i.e., a table can be linked to data stored in a data pump export file like the Oracle Loader interface available since Oracle 9i).

Scott Stephens worked for Oracle for more than 13 years in technical support, e-commerce, marketing, and software development.

Links

• Subscribe to this feed