Datapump Attach Job ...
Attach and Manage a running Datapump jobimpdp/expdp: Attaching to a running Jobhttp://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 againcheck connectionsqlplus 'system/vihaan123@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=172.10.111.6)(Port=1521))(CONNECT_DATA=(SID=ORCL1)))'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 a15VIPimpdp 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 &impdp via EZCONNECTimpdp system/anuj1@//vip:1521/pdb1http://anuj-singh.blogspot.com/2011/02/connect-to-sqlplus-with-no-tnsnames.htmlvip export TWO_TASK='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.11.111.6)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=test)))' impdp system/anuj directory=DATA_PUMP_EXP dumpfile=anuj.dmp logfile=anuj1.log REMAP_TABLESPACE=USERS:ANUJ schemas=ANUJ1 CLUSTER=N full=N status=10 metrics=Y & export TWO_TASK=======================================================
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
SET PAGESIZE 66 HEADING ON VERIFY OFF LINESIZE 400 FEEDBACK ON SQLCASE UPPER NEWPAGE 1 SET SQLCASE mixed ALTER SESSION SET nls_date_format='DD.MM.YYYY HH24:MI:SS'; ALTER SESSION SET nls_timestamp_format='DD.MM.YYYY HH24:MI:SS'; COLUMN sid FORMAT 999999 WRAP HEADING "SID" COLUMN serial# FORMAT 999999 WRAP HEADING "Serial" COLUMN file_id FORMAT 9999 heading "File ID" COLUMN start_time FORMAT A20 heading "Start Time" COLUMN end_time FORMAT A20 heading "Estimated End Time" COLUMN elapsed_seconds FORMAT 999999999999 heading "Elapsed (Seconds)" COLUMN formatted_elapsed_time FORMAT A15 heading "Elapsed" COLUMN throughput FORMAT 999999999999 heading "Throughput (Bytes)" COLUMN throughput_formatted FORMAT A12 heading "Throughput" COLUMN sofar FORMAT 999999999999 heading "Processed (Bytes)" COLUMN sofar_formatted FORMAT A12 heading "Processed" COLUMN totalwork FORMAT 999999999999 WRAP HEADING "Total (Bytes)" COLUMN totalwork_formatted FORMAT A12 WRAP HEADING "Total" COLUMN percent FORMAT 999.99 WRAP HEADING "Completted (%)" COLUMN status FORMAT A10 WRAP HEADING "Status" COLUMN opname FORMAT A20 WRAP HEADING "Operation Name" col kill for a17 col USERNAME for a20 col MESSAGE for a30 TTITLE ' Long Running ' SELECT ''''||sid ||','|| serial#||',@'||inst_id ||'''' kill, con_id, USERNAME, to_number(regexp_substr(message, 'data file (\d+)', 1, 1, NULL, 1)) as file_id, start_time, start_time + (time_remaining/86400) AS end_time, --elapsed_seconds, lpad(CASE WHEN elapsed_seconds < 60 THEN elapsed_seconds || 's' WHEN elapsed_seconds < 3600 THEN FLOOR(elapsed_seconds / 60) || 'm ' || MOD(elapsed_seconds, 60) || 's' WHEN elapsed_seconds < 86400 THEN FLOOR(elapsed_seconds / 3600) || 'h ' || FLOOR(MOD(elapsed_seconds, 3600) / 60) || 'm ' || MOD(elapsed_seconds, 60) || 's' ELSE FLOOR(elapsed_seconds / 86400) || 'd ' || FLOOR(MOD(elapsed_seconds, 86400) / 3600) || 'h ' || FLOOR(MOD(elapsed_seconds, 3600) / 60) || 'm ' || MOD(elapsed_seconds, 60) || 's' END,15,' ') AS formatted_elapsed_time, --sofar/elapsed_seconds as throughput, lpad(CASE WHEN sofar/nullif(elapsed_seconds, 0) < POWER(1024, 1) THEN ROUND(sofar/nullif(elapsed_seconds, 0), 2) || ' B' WHEN sofar/nullif(elapsed_seconds, 0) < POWER(1024, 2) THEN ROUND(sofar/nullif(elapsed_seconds, 0) / POWER(1024, 1), 2) || ' KB' WHEN sofar/nullif(elapsed_seconds, 0) < POWER(1024, 3) THEN ROUND(sofar/nullif(elapsed_seconds, 0) / POWER(1024, 2), 2) || ' MB' WHEN sofar/nullif(elapsed_seconds, 0) < POWER(1024, 4) THEN ROUND(sofar/nullif(elapsed_seconds, 0)/ POWER(1024, 3), 2) || ' GB' ELSE ROUND(sofar/nullif(elapsed_seconds, 0) / POWER(1024, 4), 2) || ' TB' END,12,' ') AS throughput_formatted, --sofar, lpad(CASE WHEN sofar < POWER(1024, 1) THEN ROUND(sofar, 2) || ' B' WHEN sofar < POWER(1024, 2) THEN ROUND(sofar / POWER(1024, 1), 2) || ' KB' WHEN sofar < POWER(1024, 3) THEN ROUND(sofar / POWER(1024, 2), 2) || ' MB' WHEN sofar < POWER(1024, 4) THEN ROUND(sofar / POWER(1024, 3), 2) || ' GB' ELSE ROUND(sofar / POWER(1024, 4), 2) || ' TB' END,12,' ') AS sofar_formatted, --totalwork, lpad(CASE WHEN totalwork < POWER(1024, 1) THEN ROUND(totalwork, 2) || ' B' WHEN totalwork < POWER(1024, 2) THEN ROUND(totalwork / POWER(1024, 1), 2) || ' KB' WHEN totalwork < POWER(1024, 3) THEN ROUND(totalwork / POWER(1024, 2), 2) || ' MB' WHEN totalwork < POWER(1024, 4) THEN ROUND(totalwork / POWER(1024, 3), 2) || ' GB' ELSE ROUND(totalwork / POWER(1024, 4), 2) || ' TB' END,12,' ') AS totalwork_formatted, sofar/totalwork*100 as percent, CASE WHEN time_remaining>0 THEN 'running' ELSE 'finished' END AS status ,opname ,sql_id ,substr(MESSAGE,1,30) MESSAGE FROM gv$session_longops WHERE 1=1 --opname LIKE 'XXX%' and sofar != totalwork ORDER BY file_id ASC, status DESC, start_time DESC; set linesize 300 pagesize 300 col SQL_TEXT for a50 col FIRST_LOAD_TIME for a25 col LAST_LOAD_TIME for a25 col CPU_TIME for 99999999999 select elapsed_time/1000000 seconds, s.inst_id, s.users_executing, s.cpu_time, s.first_load_time, s.last_load_time,MODULE, s.sql_text from gv$sql s where users_executing > 0 and MODULE='Data Pump Worker' union select elapsed_time/1000000 seconds, s.inst_id, s.users_executing, s.cpu_time, s.first_load_time, s.last_load_time,MODULE, s.sql_text from gv$sql s where rownum < 50 and MODULE='Data Pump Worker' order by last_load_time /
-- Parameters Affecting Datapump Performance:
set pagesize 0
select 'ALTER SYSTEM SET STREAMS_POOL_SIZE='||(max(to_number(trim(c.ksppstvl)))+67108864)/1024/1024||' mb ; '
from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
and lower(a.ksppinm) in ('__streams_pool_size','streams_pool_size')
;
col OPNAME for a20 col kill for a17 SELECT ''''||sid ||','|| serial#||',@'||'1'||'''' kill ,OPNAME,CONTEXT,SOFAR,TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) "PERCENTAGE_COMPLETE" FROM V$SESSION_LONGOPS WHERE OPNAME in ( select d.job_name from v$session s, v$process p, dba_datapump_sessions d where p.addr=s.paddr and s.saddr=d.saddr ) AND OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND SOFAR <> TOTALWORK;
