• https://orahow.com/queries-to-monitor-expdp-datapump-jobs-status/

COL OWNER_NAME FORMAT A35
COL JOB_NAME FORMAT A40
COL OPERATION FORMAT A50
COL JOB_MODE FORMAT A50
COL STATE FORMAT A30
SELECT owner_name, job_name, operation, job_mode, state FROM dba_datapump_jobs;

col opname format a30
SELECT
    opname,
    sid,
    serial#,
    context,
    sofar,
    totalwork,
    round(sofar / totalwork * 100, 2) "%_COMPLETE"
FROM
    v$session_longops
WHERE
    opname IN (
        SELECT
            d.job_name
        FROM
            v$session               s,
            v$process               p,
            dba_datapump_sessions   d
        WHERE
            p.addr = s.paddr
            AND s.saddr = d.saddr
    )
    AND opname NOT LIKE '%aggregate%'
    AND totalwork != 0
    AND sofar <> totalwork;

col username format a32
col job_name format a30
col program format a50
COL ACTUALDATE FORMAT A20
COL SPID FORMAT A10
COL SID_SERIAL FORMAT A20
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS ACTUALDATE,
     s.program,
     s.sid || ',' || s.serial# SID_SERIAL,
     s.status,
     s.username,
     d.job_name,
     p.spid,
     p.pid
FROM V$SESSION s, V$PROCESS p, DBA_DATAPUMP_SESSIONS d
WHERE p.addr = s.paddr AND s.saddr = d.saddr;

Same but with sqltext

col program format a50
COL ACTUALDATE FORMAT A20
COL SPID FORMAT A10
col username format a20
SELECT s.program,
     S.SQL_ID,
     s.status,
     s.username,
     d.job_name,
     SQA.SQL_TEXT
FROM V$SESSION s, V$PROCESS p, DBA_DATAPUMP_SESSIONS d,  V$SQLAREA SQA
WHERE p.addr = s.paddr AND s.saddr = d.saddr
AND S.SQL_ID=SQA.SQL_ID;

and:

col event format a100
SELECT program, event
FROM   v$session
WHERE  program LIKE '%DW%';

This will list all available objects for an INCLUDE/EXCLUDE pattern:

COL HET_TYPE FORMAT A30
COL OBJECT_PATH FORMAT A120
SELECT HET_TYPE, OBJECT_PATH FROM DBA_EXPORT_OBJECTS ;

This is a user for impdp/expdp. Specially useful with DB_SECUREFILE='FORCE' cause it can have ASSM on its tablespace.

CREATE TABLESPACE SYSDP DATAFILE '+DG_DATA_01' 
  SIZE 1G 
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE 
  segment SPACE management auto;
 
CREATE USER SYSDP IDENTIFIED BY SUPERDBA DEFAULT tablespace SYSDP quota unlimited ON SYSDP;
 
GRANT CREATE TABLE TO SYSDP;
GRANT datapump_exp_full_database TO SYSDP;
GRANT datapump_imp_full_database TO SYSDP;
GRANT READ, WRITE ON directory EXPDP_DIR TO SYSDP;

impdp system/THE_PASSWORD ATTACH=JOBNAME

Then

Kill 

select yes and wait, it will take some time.

Will show only dp sessions with state='NOT RUNNING'

col username format a32
col job_name format a30
col program format a50
COL ACTUALDATE FORMAT A20
COL SPID FORMAT A10
COL SID_SERIAL FORMAT A20
SELECT 'ALTER SYSTEM KILL SESSION ''' || s.sid || ',' || s.serial# || ''' IMMEDIATE ;'
FROM V$SESSION s, V$PROCESS p, DBA_DATAPUMP_SESSIONS d
WHERE p.addr = s.paddr AND s.saddr = d.saddr
-- AND D.STATE = 'NOT RUNNING' ;
;

Full information:

SELECT T1.SID_SERIAL,
      T1.OSPID,
      T1.SQLID,
      T1.USERNAME,
      T1.OSUSER,
      T1.HOSTNAME,
      T1.PROGRAM,
      T1.CLIENT_INFO
FROM (
SELECT ses.sid || ',' || ses.serial# "SID_SERIAL",
      p.spid AS OSPID,
      SES.SQL_ID SQLID,
      SES.USERNAME USERNAME,
      SES.OSUSER OSUSER,
      SES.MACHINE HOSTNAME,
      SES.PROGRAM PROGRAM,
      SES.CLIENT_INFO
FROM V$SESSION SES, V$PROCESS P
WHERE SES.paddr = p.addr
ORDER BY SES.MACHINE
) t1,
(
SELECT s.sid || ',' || s.serial# "SID_SERIAL"
FROM V$SESSION s, V$PROCESS p, DBA_DATAPUMP_SESSIONS d
WHERE p.addr = s.paddr AND s.saddr = d.saddr
) t2
WHERE t1.SID_SERIAL=t2.SID_SERIAL
/

After killing a job

SELECT 'DROP TABLE ' || owner_name || '.' || job_name || ' ; '  FROM dba_datapump_jobs WHERE STATE = 'NOT RUNNING' ;