Oracle datapump 모니터링

데이터펌프 모니터링

Active datapump job 세션 상태

set lines 150 pages 100
numwidth 7
col program for a38
col username for a10
col spid for a7
---- for sqlplus 
select to_char(sysdate,’YYYY-MM-DD HH24:MI:SS’) “DATE”
     , s.program, s.sid, s.status
     , s.username,d.job_name, p.spid
     , s.serial#, p.pid,s.event
  from v$session s, v$process p
     , dba_datapump_sessions d 
 where p.addr=s.paddr 
   and s.saddr=d.saddr;

datapump job 진행사항

select round(sofar/totalwork*100,2) percent_completed
     , v$session_longops.*
  from v$session_longops
 where sofar <> totalwork
 order by target,sid;

현재 수행되는 데이터펌프 잡 상세 조회

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