Datapump Attach Job ...
Attach and Manage a running Datapump job
impdp/expdp: Attaching to a running Job
http://anuj-singh.blogspot.co.uk/2014/08/oracle-data-pump-process-info.html
--- command for import !!!
impdp system/vihaan@ORCL directory=DATA_PUMP_EXP dumpfile=XXX.dmp logfile=DATA_PUMP_EXP:XXX_IMPORT.log REMAP_TABLESPACE=USERS:ANUJ schemas=XXX CLUSTER=N full=N status=10 metrics=Y JOB_NAME=ANUJIMP &
=======================================================================================
Import: Release 19.0.0.0.0 - Production on Mon Dec 1 16:51:18 2025
Version 19.28.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation: nonexistent file or path [29434]
use the connection for node1 then try again
check connection
sqlplus 'system/vihaan123@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=172.10.111.6)(Port=1521))(CONNECT_DATA=(SID=ORCL1)))'
VIP
impdp system/vihaan123@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=172.10.111.6)(Port=1521))(CONNECT_DATA=(SID=ORCL1))) directory=DATA_PUMP_EXP dumpfile=XXX.dmp logfile=DATA_PUMP_EXP:XXX_IMPORT.log REMAP_TABLESPACE=USERS:ANUJ schemas=XXX CLUSTER=N full=N status=10 metrics=Y JOB_NAME=ANUJIMP &
======================================================
Oracle Data Pump process info
set lines 150 pages 100 numwidth 7
col program for a38
col username for a10
col spid for a7
col kill for a15
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill,to_char(sysdate,'DD-MM-YYYY HH24:MI:SS') "To_DATE", s.program, s.status, s.username, d.job_name, p.spid, p.pid from gv$session s, gv$process p, dba_datapump_sessions d
where 1=1
and p.addr=s.paddr
and s.saddr=d.saddr
and p.inst_id=s.inst_id;
set pages 200 lines 500
col OPNAME for a10
col UNITS for a10
col TARGET_DESC for a10
col OWNER_NAME for a10
col STATE for a10
col JOB_MODE for a10
col JOBNAME for a20
col MESSAGE for a30
col kill for a15
col JOB_NAME for a15
alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';
select ''''||sl.sid ||','|| sl.serial#||',@'||sl.inst_id ||'''' kill,sofar,totalwork,units,target_desc,start_time,last_update_time,round(time_remaining/60,2) "REMAINING MINS",round(elapsed_seconds/60,2) "ELAPSED MINS",round((time_remaining+elapsed_seconds)/60,2)"TOTAL MINS",
dp.owner_name,dp.state,dp.job_name,dp.job_mode,dp.workers,message
from gv$session_longops sl,gv$datapump_job dp
where 1=1
and sl.opname=dp.job_name
and sofar != totalwork
/
--- > 12c With con_id
set pages 300 lines 500
col OPNAME for a10
col UNITS for a10
col TARGET_DESC for a10
col OWNER_NAME for a10
col STATE for a10
col JOB_MODE for a10
col JOBNAME for a20
col MESSAGE for a30
col kill for a15
col JOB_NAME for a15
alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';
select ''''||sl.sid ||','|| sl.serial#||',@'||sl.inst_id ||'''' kill,sl.CON_ID,sofar,totalwork,units,target_desc,start_time,last_update_time,round(time_remaining/60,2) "REMAINING MINS",round(elapsed_seconds/60,2) "ELAPSED MINS",round((time_remaining+elapsed_seconds)/60,2)"TOTAL MINS",
dp.owner_name,dp.state,dp.job_name,dp.job_mode,dp.workers,message
from gv$session_longops sl,gv$datapump_job dp
where 1=1
and sl.opname=dp.job_name
and sofar != totalwork
/
DBA_DATAPUMP_JOBS DBA_DATAPUMP_SESSIONS
set lines 200
col owner_name format a10
col state format a11
col operation like state
col job_mode like state
select owner_name, job_name, operation, job_mode, state, attached_sessions from dba_datapump_jobs
where job_name not like 'BIN$%'
order by 1,2;
set linesize 200 pagesize 200
col "Dropcommand" for a50
SELECT o.status, o.object_id, o.object_type,CREATED,'DROP TABLE ' ||o.owner||'.'||object_name||' ;' "Dropcommand" FROM dba_objects o, dba_datapump_jobs j
WHERE o.owner=j.owner_name
AND o.object_name=j.job_name
and state = 'NOT RUNNING'
order by created ;
alter session set nls_date_format='dd-mm-yyyy HH24:mi' ;
set pagesize 200 linesize 200
col "Long Operations w/SQL" for a150
select
-- distinct
'Kill_id : '||''''||a.sid ||','|| a.serial#||',@'||a.inst_id ||''''||chr(10)||
'USERNAME : '||a.username ||chr(10)||
'PROGRAM : '||a.program ||chr(10)||
'MACHINE : '||a.machine ||chr(10)||
'OS User : '||a.osuser ||chr(10)||
'Operation : '||e.opname ||chr(10)||
'Target : '||e.target ||chr(10)||
'Total Work : '||e.totalwork ||chr(10)||
'Sofar : '||e.sofar ||chr(10)||
'Perc Complete% : '||round(( e.sofar / decode ( nvl2(e.totalwork,e.totalwork,1) ,0,1, nvl2(e.totalwork,e.totalwork,1))) * 100) ||chr(10)||
'Start Time : '||e.start_time ||chr(10)||
'Last Update Time : '||e.last_update_time ||chr(10)||
'Remaining Minutes : '||round(e.time_remaining/60) ||chr(10)||
'Elapsed Minutes : '||round(e.elapsed_seconds/60) ||chr(10)||
'Status : '||a.status ||chr(10)||
'Process : '||a.Process ||chr(10)||
'Event : '||a.event ||chr(10)||
'SQL ID : '||a.sql_id ||chr(10)||
'Current Statement : '||d.sql_text "Long Operations w/SQL"
from gv$session a, gv$sess_io b,gv$sesstat c,gv$sql d,sys.gv$session_longops e
where e.sid = a.sid (+)
and e.serial# =a.serial#(+)
and e.sofar != e.totalwork
and a.sid = b.sid(+)
and a.sid = c.sid(+)
and (c.statistic# = 12 OR c.statistic# IS NULL)
and a.sql_address = d.address(+)
and a.sql_hash_value = d.hash_value(+)
and (d.child_number = 0 OR d.child_number IS NULL)
-- and a.SCHEMANAME not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
ORDER BY a.username;
===
Prompt with con id
alter session set nls_date_format='dd-mm-yyyy HH24:mi' ;
set pagesize 200 linesize 200
col "Long Operations w/SQL" for a150
select
-- distinct
'Kill_id : '||''''||a.sid ||','|| a.serial#||',@'||a.inst_id ||''''||chr(10)||
'Con Id** : '|| a.con_id ||chr(10)||
'USERNAME : '||a.username ||chr(10)||
'PROGRAM : '||a.program ||chr(10)||
'MACHINE : '||a.machine ||chr(10)||
'OS User : '||a.osuser ||chr(10)||
'Operation : '||e.opname ||chr(10)||
'Target : '||e.target ||chr(10)||
'Total Work : '||e.totalwork ||chr(10)||
'Sofar : '||e.sofar ||chr(10)||
'Perc Complete% : '||round(( e.sofar / decode ( nvl2(e.totalwork,e.totalwork,1) ,0,1, nvl2(e.totalwork,e.totalwork,1))) * 100) ||chr(10)||
'Start Time : '||e.start_time ||chr(10)||
'Last Update Time : '||e.last_update_time ||chr(10)||
'Remaining Minutes : '||round(e.time_remaining/60) ||chr(10)||
'Elapsed Minutes : '||round(e.elapsed_seconds/60) ||chr(10)||
'Status : '||a.status ||chr(10)||
'Process : '||a.Process ||chr(10)||
'SQL ID : '||a.sql_id ||chr(10)||
'Current Statement : '||d.sql_text "Long Operations w/SQL"
from gv$session a, gv$sess_io b,gv$sesstat c,gv$sql d,sys.gv$session_longops e
where e.sid = a.sid (+)
and e.serial# =a.serial#(+)
and e.sofar != e.totalwork
and a.sid = b.sid(+)
and a.sid = c.sid(+)
and (c.statistic# = 12 OR c.statistic# IS NULL)
and a.sql_address = d.address(+)
and a.sql_hash_value = d.hash_value(+)
and (d.child_number = 0 OR d.child_number IS NULL)
--and a.module like '%Worker%'
-- and a.SCHEMANAME not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
ORDER BY a.username;
set linesize 300 pagesize 300
col sql_text for a50 wrap
col kill for a15
col event for a20
col username for a20
select distinct ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill, dp.job_name, dp.session_type, s.username, s.event, s.sql_id,dj.operation, dj.state ,q.sql_text
from gv$session s, dba_datapump_sessions dp, dba_datapump_jobs dj, gv$sql q
where s.saddr = dp.saddr
and dp.job_name = dj.job_name
and s.sql_id = q.sql_id
-- and s.inst_id in (1)
order by s.inst_id;
set linesize 300 pagesize 1000
COLUMN instance_name FORMAT a10 HEADING 'Instance'
COLUMN sid HEADING 'Oracle|SID'
COLUMN serial_num HEADING 'Serial|#'
--COLUMN opname FORMAT a30 HEADING 'RMAN|Operation'
COLUMN start_time FORMAT a18 HEADING 'Start|Time'
COLUMN totalwork HEADING 'Total|Work'
COLUMN sofar HEADING 'So|Far'
COLUMN pct_done HEADING 'Percent|Done'
COLUMN elapsed_seconds HEADING 'Elapsed|Seconds'
COLUMN time_remaining HEADING 'Seconds|Remaining'
COLUMN done_at FORMAT a18 HEADING 'Done|At'
col kill for a15
col EVENT for a30
col PROGRAM for a20
SELECT ''''||a.sid ||','|| a.serial#||',@'||a.inst_id ||'''' kill
,a.sql_id
,PREV_SQL_ID
, b.opname opname
, TO_CHAR(b.start_time, 'dd/mm/yy HH24:MI:SS') start_time
, b.totalwork totalwork
, b.sofar sofar
, ROUND( (b.sofar/DECODE( b.totalwork, 0, 0.001, b.totalwork)*100),2) pct_done
, b.elapsed_seconds elapsed_seconds
, b.time_remaining time_remaining
,a.event
, DECODE( b.time_remaining, 0, TO_CHAR((b.start_time + b.elapsed_seconds/3600/24), 'dd/mm/yy HH24:MI:SS')
, TO_CHAR((SYSDATE + b.time_remaining/3600/24), 'dd/mm/yy HH24:MI:SS')) done_at
,PROGRAM
FROM gv$session a, gv$session_longops b
where 1=1
and a.sid=b.sid
and a.serial#=b.serial#
and a.inst_id=b.inst_id
-- and a.program LIKE 'rman%' AND b.opname LIKE 'RMAN%' AND b.opname NOT LIKE '%aggregate%'
AND b.totalwork > 0
ORDER BY b.start_time;
Attach and Manage a running Datapump job
impdp/expdp: Attaching to a running Job
set pages 200 lines 200
TTITLE 'Currently Active DataPump Operations'
COL owner_name FORMAT A06 HEADING 'Owner'
COL job_name FORMAT A20 HEADING 'JobName'
COL operation FORMAT A12 HEADING 'Operation'
COL job_mode FORMAT A12 HEADING 'JobMode'
COL state FORMAT A12 HEADING 'State'
COL degree FORMAT 9999 HEADING 'Degr'
COL attached_sessions FORMAT 9999 HEADING 'Sess'
SELECT
owner_name
,job_name
,operation
,job_mode
,state
,degree
,attached_sessions
FROM dba_datapump_jobs
/
— jobname from dba_datapump_jobs
expdp \"/ as sysdba\" attach='JOB_NAME'
or
impdp \"/ as sysdba\" attach='JOB_NAME'
for pdbs!!!
export ORACLE_PDB_SID=ANUJ
impdp \"/ as sysdba\" attach='SYS_IMPORT_FULL_01'
Import> status
Job: SYS_IMPORT_FULL_01
Operation: IMPORT
Mode: FULL
State: EXECUTING
To kill a datapump job
STOP_JOB=IMMEDIATE
KILL_JOB
To stop the datapump job
STOP_JOB
Export> parallel=4
Export> start_job
Export> status
set lines 200 pagesize 300
col owner_name for a10
col job_name for a20
col state for a12
col operation like owner_name
col job_mode like owner_name
select owner_name, job_name, operation, job_mode,state, attached_sessions from dba_datapump_jobs;
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED_SESSIONS
---------- -------------------- ---------- ---------- ------------ -----------------
SYS ANUJ EXPORT SCHEMA EXECUTING 1
expdp \'/ as sysdba\' attach=ANUJ
Export> status
KILL_JOB Detach and delete job.
PARALLEL Change the number of active workers for current job.
REUSE_DUMPFILES Overwrite destination dump file if it exists [N].
START_JOB Start or resume current job. Valid keyword values are: SKIP_CURRENT.
STATUS Frequency (secs) job status is to be monitored where the default [0] will show new status when available.
STOP_JOB Orderly shutdown of job execution and exits the client.Valid keyword values are: IMMEDIATE.
Export> stop_job Are you sure you wish to stop this job ([yes]/no): yes
restart the job
Import> continue_client
============
set linesize 120 pagesize 120
col name heading 'Name' format a18
col status heading 'Status' format a20
col timeout heading 'Timeout' format 999999
col error_number heading 'Error Number' format 999999
col error_msg heading 'Message' format a44
select session_id, name,status, timeout, error_number, error_msg from dba_resumable;
set linesize 200 pagesize 120
col object_name heading 'Object Name' format a14
col object_type heading 'Object Type' format a14
col reason heading 'Reason' format a34
col suggested_action heading 'Suggested action' format a34
select object_name,object_type,reason,suggested_action from dba_outstanding_alerts;
set linesize 120 pagesize 120
col seq# heading 'Seq#' format 9999
col event heading 'Wait Event' format a50
col wait_time heading 'Wait time' format 9999
col seconds_in_wait heading 'Seconds' format 9999
col state heading 'State' format a18
col seq# heading 'Seq#' format 9999
col seq# heading 'Seq#' format 9999
col seq# heading 'Seq#' format 9999
col seq# heading 'Seq#' format 9999
col kill for a16
select
''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill ,
s.con_id,
sw.seq#,sw.event, sw.wait_time,sw.seconds_in_wait,sw.state from gv$session_wait sw, gv$session s,dba_datapump_sessions d
where sw.wait_class <> 'Idle'
and sw.sid=s.sid
and sw.inst_id=s.inst_id
and s.saddr=d.saddr;
How To Break And Restart A DataPump Export Or Import Job (Doc ID 1400974.1)
set lines 500 pagesize 300
col opname format a35
col target format a25
col units format a10
col kill for a17
col message for a20
col kill for a17
select * from (
select
''''||sid ||','||serial#||',@'||inst_id ||'''' kill , sql_id,
opname, target, sofar, totalwork, round(sofar/totalwork, 4)*100 pct, units, round(elapsed_seconds/60,2) elap_min, round(time_remaining/60,2) remaining_min
,sql_plan_hash_value, sql_plan_operation, sql_plan_options, sql_plan_line_id, to_char(sql_exec_start, 'dd-mm-yyyy hh24:mi:ss') sql_exec_start
,message
from gv$session_longops
WHERE sofar < totalwork
order by start_time desc
)
/
set lines 200
col logn_time for a25
col sid_serial for a20
col username for a20
col backup_type for a15
col job_name for a30
col kill for a17
select
''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill ,
to_char(s.logon_time,'dd-mm-yyyy hh24:mi:ss') logon_time
,s.status
,s.username
,s.sid||','||s.serial# sid_serial
,case when s.program like 'rman%' then 'RMAN'
when s.program like 'ude%' or s.program like '%DM%' or s.program like '%DW%' then 'DATAPUMP'
else '-'
end backup_type
,dps.job_name job_name
from gv$session s, dba_datapump_sessions dps
where (program like 'rman%' or program like 'ude%' or program like '%DM%' or program like '%DW%')
and dps.saddr(+) = s.saddr
order by backup_type, logon_time
;
CLEAR BREAKS
CLEAR COLUMNS
