For example, if you’re exporting a schema, a table is created in your account with the name SYS_EXPORT_SCHEMA_NN, where NN is a number that makes the table name unique in the user’s schema.
This status table contains information such as the objects exported/imported, start time, elapsed time, rows, error count, and so on. The status table has over 80 columns.
The status table is dropped by Data Pump upon successful completion of an export or import job. If you use the KILL_JOB interactive command, the master table is also dropped. If you stop a job with the STOP_JOB interactive command, the table isn’t removed and is used in the event you restart the job.
If your job terminates abnormally, the master table is retained. You can delete the status table if you don’t plan to restart the job.
308
CHAPTER 13 ■ DATA PUMP
DIRECTORY command to accomplish this. This example creates a directory named dp_dir and specifies that it is to map to the /oradump physical location on disk:
SQL> create directory dp_dir as '/oradump';
To view the details of the newly created directory, issue this query: SQL> select owner, directory_name, directory_path from dba_directories; By default, when you install Oracle, one default directory object is created named DATA_PUMP_DIR. If you don’t specify the DIRECTORY parameter when exporting or importing, Oracle by default attempts to use the default database-directory object. The default directory associated with DATA_PUMP_DIR can vary depending on the version of Oracle. On some systems, it may be ORACLE_HOME/rdbms/log; on other systems, it may point to ORACLE_BASE/admin/ORACLE_SID/dpdump. You have to inspect DBA_DIRECTORIES to verify the default location for your system.
Granting Access to the Directory
You need to grant permissions on the database-directory object to a user that wants to use Data Pump.
Use the GRANT statement to allocate the appropriate privileges. If you want a user to be able to read from and write to the directory, you must grant security access as follows: SQL> grant read, write on directory dp_dir to darl;
All directory objects are owned by the SYS user. If you’re using a user account that has the DBA role granted to it, then you have the read and write privileges on the directory object. I usually perform Data Pump jobs with a user that has DBA granted to it (so I don’t need to bother with granting access).
Taking an Export
When the directory object and grants are in place, you can use Data Pump to export information from a database. DBAs typically use exports for point-in-time backups of data and metadata. You can use these exports to either restore database objects or move data to different database environments. Suppose you recently created a table and populated it with data:
SQL> create table inv(inv_id number);
SQL> insert into inv values (123);
Now, you want to export the table. This example uses the previously created directory named DP_DIR. Data Pump uses the directory path specified by the directory object as the location on disk to write the dump file and log file:
$ expdp darl/foo directory=dp_dir tables=inv dumpfile=exp.dmp logfile=exp.log The expdp job creates a file named exp.dmp in the /oradump directory that contains the information required to re-create the INV table and populate it with data as of the time the export was taken. In addition, a log file named exp.log is created in the /oradump directory that contains all the logging information associated with this export job.
If you don’t specify a dump-file name, Data Pump creates a file named expdat.dmp. If a file named expdat.dmp already exists in the directory, then Data Pump throws an error. If you don’t specify a log-file name, then Data Pump creates one named export.log. If a file already exists (named export.log), then Data Pump overwrites it.
311
CHAPTER 13 ■ DATA PUMP
Interactive Command Mode
Data Pump provides an interactive command mode that allows you to monitor the status of a Data Pump job and modify on-the-fly a number of job characteristics. The interactive command mode is most useful for long-running Data Pump operations. In this mode, you can also stop, re-start, or terminate a currently running job. Each of these activities is discussed in the following subsections.
Entering Interactive Command Mode
There are two ways to get to the interactive command-mode prompt:
• Press Ctrl+C in a Data Pump job that you started via expdp or impdp.
• Use the ATTACH parameter to attach to a currently running job
When you run a Data Pump job from the command line, you’re placed in the command-line mode.
You should see output displayed to your terminal as a job progresses. If you want to exit command-line mode, press Ctrl+C. This places you in the interactive command-interface mode. For an export job, the prompt is
Export>
Type in the HELP command to view the export interactive commands available (see Table 13–1 for a description):
Export> help
Type EXIT to leave interactive command mode:
Export> exit
You should now be at the operating-system prompt.
Table 13–1.
Export Interactive Commands
Command Description
ADD_FILE
Adds files to the export dump set.
CONTINUE_CLIENT
Continues with interactive client mode.
EXIT_CLIENT
Exits the client session and returns to the operating-system prompt. Leaves the current job running.
FILESIZE
Defines file size for any subsequently created dump files.
HELP
Displays interactive export commands.
KILL_JOB
Terminates the current job.
PARALLEL
Increases or decreases the degree of parallelism.
START_JOB
Restarts the attached job.
STATUS
Displays the status of the currently attached job.
STOP_JOB
Stops a job from processing (you can later restart it). Using the IMMEDIATE
[=IMMEDIATE]
parameter quickly stops the job, but there may be some incomplete tasks.
313
CHAPTER 13 ■ DATA PUMP
You can press Ctrl+C for either an export or import job. For an import job, the interactive command-mode prompt is
Import>
To view all commands available, type HELP:
Import> help
The interactive command-mode import commands are summarized in table 13–2.
Table 13–2.
Import Interactive Commands
Command Description
CONTINUE_CLIENT
Continues with interactive logging mode.
EXIT_CLIENT
Exits the client session and returns to the operating-system prompt.
Leaves the current job running.
HELP
Displays the available interactive commands.
KILL_JOB
Terminates the job it’s currently connected to in the client.
PARALLEL
Increases or decreases the degree of parallelism.
START_JOB
Restarts a previously stopped job. START_JOB=SKIP_CURRENT restarts the job and skips any operations that were active when the job was stopped.
STATUS
Specifies the frequency at which the job status is monitored. Default mode is 0; the client reports job status changes whenever available in this mode.
STOP_JOB [=IMMEDIATE]
Stops a job from processing (you can later restart it). Using the IMMEDIATE
parameter quickly stops the job, but there may be some incomplete tasks.
Type EXIT to leave the Data Pump status utility:
Import> exit
You should now be at the operating-system prompt.
Attaching to a Running Job
One powerful feature of Data Pump is that you can attach to a currently running job and view its progress and status. If you have DBA privileges, you can even attach to a job if you aren’t the owner. You can attach to either an import or an export job via the ATTACH parameter.
Before you attach to a job, you must first determine the Data Pump job name (and owner name if you’re not the owner of the job). Run the following SQL query to display currently running jobs: SQL> select owner_name, operation, job_name, state from dba_datapump_jobs; 314
CHAPTER 13 ■ DATA PUMP
Here’s some sample output:
OWNER_NAME OPERATION JOB_NAME STATE
--------------- --------------- -------------------- ---------------
DARL IMPORT SYS_IMPORT_SCHEMA_02 EXECUTING
DARL IMPORT SYS_IMPORT_SCHEMA_01 NOT RUNNING
In this example, you’re the owner of the job, so you use the ATTACH parameter without prepending the owner name to it. This is an import job, so you use the impdp command to attach to the job name SYS_IMPORT_SCHEMA_02:
$ impdp darl/engdev attach=sys_import_schema_02
If you aren’t the owner of the job, you attach to the job by specifying the owner name and the job name:
$ impdp system/foobar attach=darl.sys_import_schema_02
You should now see the Data Pump command-line prompt:
Import>
Type STATUS to view the status of the currently attached job:
Import> status
Stopping and Restarting a Job
If you have a currently running Data Pump job that you want to temporarily stop, you can do so by first attaching to the interactive command mode. You may want to stop a job to resolve space issues or performance issues and then, after resolving the issues, restart the job. This example attaches to an import job:
$ impdp darl/foo attach=sys_import_table_01
Now, stop the job using the STOP_JOB parameter:
Import> stop_job
You should see this output:
Are you sure you wish to stop this job ([yes]/no):
Type YES to proceed with stopping the job. You can also specify that the job be stopped immediately:
Import> stop_job=immediate
When you stop a job with the IMMEDIATE option, there may be some incomplete tasks associated with the job. To restart a job, attach to interactive command mode and issue the START_JOB command: Import> start_job
If you want to resume logging job output to your terminal, issue the CONTINUE_CLIENT command: Import> continue_client
315
CHAPTER 13 ■ DATA PUMP
Terminating a Data Pump Job
You can instruct Data Pump to permanently kill an export or import job. First, attach to the job in interactive command mode, and then issue the KILL_JOB command:
Import> kill_job
You should be prompted with the following:
Are you sure you wish to stop this job ([yes]/no):
Type YES to permanently kill the job. Data Pump unceremoniously kills the job and drops the associated status table from the user running the export or import.
Tips for Getting Started
This section describes some common Data Pump features that I regularly use. These techniques can assist you in minimizing command-line errors and help verify what was exported or what objects and metadata are imported.
Use a Parameter File
Instead of typing commands on the command line, in many situations it’s preferable to store the commands in a file and then reference the file when executing Data Pump export or import. Using parameter files makes tasks more repeatable and less prone to errors. You can place the commands in a file once and then reference that file multiple times.