[ORACLE] EXPDP TIME CHECK

-- EXPORT JOB의 해당 완료율 확인 
SQL> 
select  OWNER_NAME, JOB_NAME, OPERATION,JOB_MODE,STATE FROM DBA_DATAPUMP_JOBS;
( STATE가 EXECTING 인 것인 JOB_NAME를 아래 조건중 b.opname에 넣고 검색한다)
col username for a10
col opname for a33
col target for a10
col %DONE for a10
select b.username, a.sid, b.opname, b.target,
round(b.SOFAR*100/b.TOTALWORK,0) || '%' as "%DONE", b.time_remaining,
to_char(b.start_time,'YYYY/MM/DD HH24:MI:SS') start_time
from v$session_longops b, v$session a
where a.sid = b.sid      
and b.opname='SYS_EXPORT_SCHEMA_01'
order by 6;

-- 현재 JOB의 완료퍼센트 확인
SQL>

select  OWNER_NAME, JOB_NAME, OPERATION,JOB_MODE,STATE FROM DBA_DATAPUMP_JOBS;
SET SERVEROUTPUT ON
DECLARE
  ind NUMBER;              
  h1 NUMBER;               
  percent_done NUMBER;     
  job_state VARCHAR2(30);  
  js ku$_JobStatus;        
  ws ku$_WorkerStatusList; 
  sts ku$_Status;          
BEGIN
h1 := DBMS_DATAPUMP.attach('JOB_NAME', 'JOB_OWNER'); -- JOB_NAME과 JOB_OWNER에 각각 해당 값을 넣어줌
dbms_datapump.get_status(h1,
           dbms_datapump.ku$_status_job_error +
           dbms_datapump.ku$_status_job_status +
           dbms_datapump.ku$_status_wip, 0, job_state, sts);
js := sts.job_status;
ws := js.worker_status_list;
      dbms_output.put_line('*** Job percent done = ' ||
                           to_char(js.percent_done));
      dbms_output.put_line('restarts - '||js.restart_count);
ind := ws.first;
  while ind is not null loop
    dbms_output.put_line('rows completed - '||ws(ind).completed_rows);
    ind := ws.next(ind);
  end loop;
DBMS_DATAPUMP.detach(h1);
end;
/

 

참고 : https://positivemh.tistory.com/440