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' ;