Oracle Data Pump process info

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


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

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