Ciberterminal Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== [DOC] IMPDP/EXPDP ======

===== External Docs =====

  * [[https://orahow.com/queries-to-monitor-expdp-datapump-jobs-status/]]



====== DB querys ======
===== List running jobs =====
<code sql>
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;
</code>


===== List job completion progress =====
<code sql>
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;
</code>

===== List what is doing each job =====

<code sql>
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;
</code>
Same but with sqltext
<code sql>

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;
</code>


and:
<code sql>
col event format a100
select program, event
from   v$session
where  program like '%DW%';
</code>

===== Available Export Objects =====
This will list all available objects for an INCLUDE/EXCLUDE pattern:
<code sql>
COL HET_TYPE FORMAT A30
COL OBJECT_PATH FORMAT A120
SELECT HET_TYPE, OBJECT_PATH FROM DBA_EXPORT_OBJECTS ;
</code>



===== create a datapump user =====

This is a user for impdp/expdp.
Specially useful with ''DB_SECUREFILE='FORCE''' cause it can have ''ASSM'' on its tablespace.
<code sql>
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;
</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 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' ;
;
</code>


Full information:
<code sql>
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
/
</code>


===== Drop table associated to a  DP job =====
After killing a job
<code sql>
SELECT 'DROP TABLE ' || owner_name || '.' || job_name || ' ; '  FROM dba_datapump_jobs WHERE STATE = 'NOT RUNNING' ;
</code>


====== do not remove ======


<code sql>
</code>
<code sql>
</code>
<code sql>
</code>