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.htmlOracle 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
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;
-- 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')
;
