Thursday, 1 May 2014

monitoring oracle datapump jobs

Scripts to monitoring oracle datapump jobs :-

sqlplus / as sysdba
SET lines 200
COL owner_name FORMAT a20;
COL job_name FORMAT a20
COL state FORMAT a15
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;

SELECT * FROM DBA_DATAPUMP_SESSIONS;

select username,opname,target_desc,sofar,totalwork,message from V$SESSION_LONGOPS;

select 
   substr(sql_text,instr(sql_text,'into "'),30) table_name, 
   rows_processed, round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,
   trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_minute
from   
   sys.v_$sqlarea 
where  
   sql_text like 'insert %into "%' and command_type = 2 and open_versions > 0;