SELECT owner_name, job_name, operation, job_mode, state FROM dba_datapump_jobs;
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE
---------- -------------------- ---------- ---------- ------------
SYSTEM SYS_EXPORT_FULL_01 EXPORT FULL EXECUTING
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
????INTERACTIVE COMMAND MODE [CTRL+C]
?CONTINUE_CLIENT : Switch back to the normal client, with the log output echoed to the screen again.
?EXIT_CLIENT : The client is closed, but the database job continues, so the operation completes as normal.
?KILL_JOB : Detaches all clients and kills the database job.
?STOP_JOB : By default, the current actions are completed, then the job is stopped. It can be resumed later. If you use the ?STOP_JOB=IMMEDIATE option, all actions are stopped immediately. When the job resumed, some of those actions will need to be rerun to make the action consistent.
?START_JOB : Restarts a stopped job.
?STATUS : Displays basic information about the job, including the status of the workers.
Example:
Export> KILL_JOB
..or..
Export> STOP_JOB=IMMEDIATE
Are you sure you wish to stop this job ([yes]/no): yes
Example-02:
Export> status
Job: SYS_EXPORT_FULL_01
Operation: EXPORT
Mode: FULL
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /u01/app/oracle/dpump/admin.dmp
bytes written: 4,096
Worker 1 Status:
Process Name: DW00
State: EXECUTING
Object Schema: ADMIN
Object Name: TEST_01
Object Type: DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY
Completed Objects: 78
Worker Parallelism: 1
-- Querying DBA_DATAPUMP_JOBS view
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.
-- Querying V$SESSION_LONGOPS & V$SESSION views
SELECT b.username,
a.sid,
b.opname,
b.target,
round(b.SOFAR*100/b.TOTALWORK,0) || '%' as "%DONE", b.TIME_REMAINING,
to_char(b.start_time,'YYYY/MM/DD HH24:MI:SS') start_time
FROM v$session_longops b, v$session a
WHERE a.sid = b.sid
ORDER BY 6;
-- Querying V$SESSION_LONGOPS & V$DATAPUMP_JOB views:
SELECT sl.sid,
sl.serial#,
sl.sofar,
sl.totalwork,
dp.owner_name,
dp.state,
dp.job_mode
FROM v$session_longops sl, v$datapump_job dp
WHERE sl.opname = dp.job_name
AND sl.sofar != sl.totalwork;
-- Querying all the related views with a single query:-
select x.job_name,
b.state,
b.job_mode,
b.degree,
x.owner_name,
z.sql_text,
p.message,
p.totalwork,
p.sofar,
round((p.sofar/p.totalwork)*100,2) done,
p.time_remaining
from dba_datapump_jobs b
left join dba_datapump_sessions x on (x.job_name = b.job_name)
left join v$session y on (y.saddr = x.saddr)
left join v$sql z on (y.sql_id = z.sql_id)
left join v$session_longops p ON (p.sql_id = y.sql_id)
WHERE y.module='Data Pump Worker'
AND p.time_remaining > 0;
-- Also for any errors you can check the alert log and query the DBA_RESUMABLE view.
select name, sql_text, error_msg from dba_resumable;
???? 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