How to use 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, restart, or terminate a currently running job. Each of these activities is discussed in the following post.

Entering Interactive Command Mode

There are two ways to access 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 8-1):

Export> help

Type EXIT to leave interactive command mode:

Export> exit

You should now be at the OS prompt.

You can press Ctrl+C for either an export or an 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 8-2.

Type EXIT to leave the Data Pump status utility:

Import> exit

You should now be at the OS 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;

Here is some sample output:

OWNER_NAME OPERATION       JOB_NAME              STATE

---------- --------------- --------------------  --------------------

MV_MAINT   EXPORT          SYS_EXPORT_SCHEMA_01  EXECUTING

In this example the MV_MAINT user can directly attach to the export job, as shown:

$ expdp mv_maint/foo attach=sys_export_schema_01

If you aren’t the owner of the job, you attach to the job by specifying the owner name and the job name:

$ expdp system/foobar attach=mv_maint.sys_export_schema_01

You should now see the Data Pump command-line prompt:

Export>

Type STATUS to view the status of the currently attached job:

Export> 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 mv_maint/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

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 output:

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.

Вас заинтересует / Intresting for you:

How to use Data Pump Legacy Mo... 825 views dbstalker Fri, 04 Feb 2022, 18:28:14

Data Pump: Features for Manipu... 1407 views dbstalker Fri, 04 Feb 2022, 17:32:59

Data Pump: Filtering Oracle Da... 7272 views dbstalker Fri, 04 Feb 2022, 17:30:44

Data Pump: Common Data Pump Ta... 3445 views dbstalker Fri, 04 Feb 2022, 17:55:04