expdp impdp datapump status check using query API

Hi All,

Below pl/sql code can be used to monitor datapump status.

Status of datapump is pulled from KU$_WORKERSTATUS1120, you can describe this and modify
"   dbms_output.put_line( ws(ind).worker_number || ',' || ws(ind).state || ',' || ws(ind).NAME || ',' || ws(ind).object_type || ',' || ws(ind).degree || ',' || ws(ind).
percent_done || ',' ||ws(ind).COMPLETED_ROWS);" To choose the columns to monitor.

set lines 160
set feed off
set serverout on ;
DECLARE
  ind NUMBER;              -- Loop index
  h1 NUMBER;               -- Data Pump job handle
  percent_done NUMBER;     -- Percentage of job complete
  job_state VARCHAR2(30);  -- To keep track of job state
  js ku$_JobStatus;        -- The job status from get_status
  ws ku$_WorkerStatusList; -- Worker status
  sts ku$_Status;          -- The status object returned by get_status
  counter NUMBER;          -- count executing workers
  job_n dba_datapump_jobs.job_name%TYPE;
  owner_n dba_datapump_jobs.owner_name%TYPE;
BEGIN
select job_name, owner_name into job_n, owner_n from sys.DBA_DATAPUMP_JOBS ;
dbms_output.put_line('Owner:            '||owner_n);
dbms_output.put_line('Job_name:         '||job_n);
h1 := DBMS_DATAPUMP.attach(job_n, owner_n); -- job name and 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;
counter := 0;
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;
DBMS_OUTPUT.put_line (to_char(SYSDATE,'DD-MON-YYYY:HH24:MI'));
    dbms_output.put_line('Number,State,Object Name,Object Type,Parallel,Percentage Done,completed_rows');
  while ind is not null loop
    dbms_output.put_line( ws(ind).worker_number || ',' || ws(ind).state || ',' || ws(ind).NAME || ',' || ws(ind).object_type || ',' || ws(ind).degree || ',' || ws(ind).
percent_done || ',' ||ws(ind).COMPLETED_ROWS);
                if ws(ind).state = 'EXECUTING' then
                                                counter := counter + 1 ;
                 end if ;
    ind := ws.next(ind);
                end loop;
                dbms_output.put_line ('Total Executing =' || counter );
DBMS_DATAPUMP.detach(h1);
end;
/

Regards
Manoj