Skip to content

How to Enter Interactive Mode While a Data Pump Job is Running

  • by
Data pump has very neat feature called interactive-command mode, DBA can change the status or content of the export/import job while it is running. The operation that DBA usually do in the interactive-command mode is to stop the export/import job for allowing himself to fix the problems he met, after all problems are fixed or given up, he can make the decision to let the job to resume or actually be killed.

Two ways can enter the interactive-command mode of the export. Either way will prompt you "Export>" for interaction.
  1. From an attached client, press Ctrl+C.
  2. The terminal could be in the logging mode of a specific running job. Let's try the method to enter interactive-command mode:
    [oracle@orcl datapump]$ expdp "/ as sysdba" DIRECTORY=DATA_PUMP_SHARED LOGFILE=DATA_PUMP_SHARED:ora_datapump-schemas-`date +%Y%m%d%H`.log PARFILE=expdp-schemas.par 2>&1 | tee -a ops_datapump-schemas-`date +%Y%m%d%H`.log
    ...
    Job: SYS_EXPORT_SCHEMA_01
      Operation: EXPORT
      Mode: SCHEMA
      State: EXECUTING
      Bytes Processed: 0
      Current Parallelism: 4
      Job Error Count: 0
      Dump File: /dump/datapump/exp_schemas01.dmp
        bytes written: 4,096
      Dump File: /dump/datapump/exp_schemas%u.dmp

    Worker 1 Status:
      State: EXECUTING
      Object Schema: POWERUSER
      Object Name: WORKER1_USERPERM
      Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
      Completed Objects: 2,960
      Worker Parallelism: 1
    ^C
    Export> stop_job=immediate

    To signal an interruption will not stop the running job, it just leave the logging mode and enter the interactive-command mode. You can enter CONTINUE_CLIENT under the prompt to get back to the logging mode.
  3. From another terminal, use expdp with attaching the job name.
  4. Let's demonstrate to reattach an existing export.
    [oracle@orcl datapump]$ expdp "/ as sysdba" attach=SYS_EXPORT_SCHEMA_01
    Export: Release 10.2.0.3.0 - 64bit Production on Sat, 01 Dec, 2012 20:39:52

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

    Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
    With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

    Job: SYS_EXPORT_SCHEMA_01
      Owner: SYS
      Operation: EXPORT
      Creator Privs: TRUE
      GUID: B5718B7D01C057F2E04400237D45D33A
      Start Time: Sat, 01 Dec, 2012 19:26:23
      Mode: SCHEMA
      Instance: orcl
      Max Parallelism: 4
      EXPORT Job Parameters:
      Parameter Name      Parameter Value:
         CLIENT_COMMAND        "/******** AS SYSDBA" DIRECTORY=DATA_PUMP_SHARED LOGFILE=DATA_PUMP_SHARED:ora_datapump-schemas-2012120119.log PARFILE=expdp-schemas.par
         INCLUDE_METADATA      0
      State: EXECUTING
      Bytes Processed: 0
      Current Parallelism: 4
      Job Error Count: 0
      Dump File: /dump/datapump/exp_schemas01.dmp
        bytes written: 4,096
      Dump File: /dump/datapump/exp_schemas%u.dmp

    Worker 1 Status:
      State: EXECUTING
      Object Schema: POWERUSER
      Object Name: WORKER1_USERPERM
      Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
      Completed Objects: 2,960
      Worker Parallelism: 1

    Export> stop_job=immediate
    Are you sure you wish to stop this job ([yes]/no): yes
    ...
    Here we take an action to stop the export immediately under the interactive-command mode.
Reattachable data pump makes the data migration more flexible to operate. It's very useful especially when you like to modify the job content or status.

Leave a Reply

Your email address will not be published.