--Query to check import status

SELECT TO_CHAR(current_date,'mm/dd/yyyy hh24:mi:ss') as_of, t.*
FROM  dba_datapump_jobs t;

SELECT TO_CHAR(current_date,'mm/dd/yyyy hh24:mi:ss') as_of, t.*
FROM  dba_datapump_sessions t;


--PL/SQL code to check import status

SET SERVEROUTPUT ON;

DECLARE
  v_job_handle     NUMBER;
  v_pct_complete   NUMBER;
  v_mask           BINARY_INTEGER;

  v_job_status     VARCHAR2(30);
  v_jobname        VARCHAR2(30) := '&i_jobname';
  v_owner          VARCHAR2(30) := NVL(UPPER('&i_owner'),user);

  v_job_stat       ku$_JobStatus;
  v_wrkr_stat      ku$_workerStatusList;
  v_stat           ku$_Status;

BEGIN
  v_job_handle := DBMS_DATAPUMP.attach(v_jobname, v_owner);

  v_mask := DBMS_DATAPUMP.ku$_status_job_error +
            DBMS_DATAPUMP.ku$_status_job_status +
            DBMS_DATAPUMP.ku$_status_wip;

  DBMS_DATAPUMP.get_status(
      handle    => v_job_handle,
      mask      => v_mask,
      timeout   => 0,
      job_state => v_job_status,
      status    => v_stat
    );

  v_job_stat := v_stat.job_status;
  v_wrkr_stat := v_job_stat.worker_status_list;
 
  DBMS_OUTPUT.put_line('Job ['||v_jobname||'] percent done = '||
                                           TO_CHAR(v_job_stat.percent_done));
  DBMS_OUTPUT.put_line('Restarts = '||v_job_stat.restart_count);

  FOR idx IN v_wrkr_stat.FIRST .. v_wrkr_stat.COUNT 
   LOOP

    DBMS_OUTPUT.put_line('Completed => '||
       v_wrkr_stat(idx).completed_objects||
       '; Total Objects => '||v_wrkr_stat(idx).total_objects||
       '; Schema => '||v_wrkr_stat(idx).schema||'.'||v_wrkr_stat(idx).name||
       '; Pct => '||v_wrkr_stat(idx).percent_done||
       '; Rows => '||v_wrkr_stat(idx).completed_rows
      );

  END LOOP;

  DBMS_DATAPUMP.detach(v_job_handle);
END;
/