• 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 DBSECUREFILE='FORCE' cause it can have ASSM on its tablespace. <code sql> CREATE TABLESPACE SYSDP DATAFILE '+DGDATA01' 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 datapumpexpfulldatabase to SYSDP; grant datapumpimpfulldatabase to SYSDP; grant read, write on directory EXPDP_DIR to SYSDP; </code> ====== Killing jobs ====== ===== Stopping a job ===== <code>impdp system/THE_PASSWORD ATTACH=JOBNAME </code> Then <code> Kill </code> select yes and wait, it will take some time. ===== Killing sessions from oracle ===== Will show only dp sessions with state='NOT RUNNING' <code sql> col username format a32 col jobname format a30 col program format a50 COL ACTUALDATE FORMAT A20 COL SPID FORMAT A10 COL SIDSERIAL FORMAT A20 SELECT 'ALTER SYSTEM KILL SESSION ' || s.sid || ',' || s.serial# || ''' IMMEDIATE ;' FROM V$SESSION s, V$PROCESS p, DBADATAPUMP_SESSIONS d WHERE p.addr = s.paddr AND s.saddr = d.saddr – AND D.STATE = 'NOT RUNNING' ; ; </code> Full information: <code sql> SELECT T1.SIDSERIAL, T1.OSPID, T1.SQLID, T1.USERNAME, T1.OSUSER, T1.HOSTNAME, T1.PROGRAM, T1.CLIENTINFO from ( SELECT ses.sid || ',' || ses.serial# “SIDSERIAL”, p.spid AS OSPID, SES.SQLID SQLID, SES.USERNAME USERNAME, SES.OSUSER OSUSER, SES.MACHINE HOSTNAME, SES.PROGRAM PROGRAM, SES.CLIENTINFO FROM V$SESSION SES, V$PROCESS P WHERE SES.paddr = p.addr ORDER BY SES.MACHINE ) t1, ( SELECT s.sid || ',' || s.serial# “SIDSERIAL” FROM V$SESSION s, V$PROCESS p, DBADATAPUMPSESSIONS d WHERE p.addr = s.paddr AND s.saddr = d.saddr ) t2 where t1.SIDSERIAL=t2.SIDSERIAL / </code> ===== Drop table associated to a DP job ===== After killing a job <code sql> SELECT 'DROP TABLE ' || ownername || '.' || jobname || ' ; ' FROM dbadatapumpjobs WHERE STATE = 'NOT RUNNING' ; </code> ====== do not remove ====== <code sql> </code> <code sql> </code> <code sql> </code>