8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 10g » Here

Data Pump (expdp, impdp) Interactive Command Mode

The expdp and impdp utilities are just a wrapper over the underlying APIs. All data pump actions are performed by database DBMS_SCHEDULER jobs. These jobs are controlled by a master control process which uses Advanced Queuing. At runtime an advanced queue table, named after the job name, is created and used by the master control process. The table is dropped on completion of the data pump job. The job and the advanced queue can be named using the JOB_NAME parameter. Cancelling the client process does not stop the associated data pump job. Issuing "CTRL+C" on the client during a job stops the client output and puts you into interactive command mode.

Related articles.

Interactive Command Mode (CTRL+C)

When running the expdp or impdp utilities the log output is also echoed to the screen, but we can stop this and switch to interactive mode by issuing CTRL+C.

Once in interactive mode we have access to a number of commands described here (expdp, impdp). There are some differences between the commands available for each utility, but some of the commonly used commands are listed below.

Attach to Existing Job

You can start a new Data Pump client and attach it to an existing Data Pump job.

If you have used the JOB_NAME parameter when creating the export or import job you will already know what the job name is. If not, you can identify the running job by querying the DBA_DATAPUMP_JOBS view. The following query is available as the datapump_jobs.sql script.

SET LINESIZE 150

COLUMN owner_name FORMAT A20
COLUMN job_name FORMAT A30
COLUMN operation FORMAT A10
COLUMN job_mode FORMAT A10
COLUMN state FORMAT A12

SELECT owner_name,
       job_name,
       TRIM(operation) AS operation,
       TRIM(job_mode) AS job_mode,
       state,
       degree,
       attached_sessions,
       datapump_sessions
FROM   dba_datapump_jobs
ORDER BY 1, 2;

OWNER_NAME           JOB_NAME                       OPERATION  JOB_MODE   STATE            DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
-------------------- ------------------------------ ---------- ---------- ------------ ---------- ----------------- -----------------
DUMMY_DBA            SYS_EXPORT_SCHEMA_01           EXPORT     SCHEMA     EXECUTING             4                 1                 6

1 row selected.

SQL>

Once you know the job name, you can attach the client to the job using the ATTACH={JOB_NAME} parameter as follows.

expdp user/password@service attach=SYS_EXPORT_SCHEMA_01

impdp user/password@service attach=SYS_IMPORT_SCHEMA_01

Once attached, you have access to all the commands, including CONTINUE_CLIENT to echo the log to the screen.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.