====== [DOC] IMPDP/EXPDP ======
===== External Docs =====
* [[https://orahow.com/queries-to-monitor-expdp-datapump-jobs-status/]]
====== DB querys ======
===== List running jobs =====
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;
===== List job completion progress =====
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;
===== List what is doing each job =====
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%';
===== Available Export Objects =====
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 ;
===== 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.
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;
====== Killing jobs ======
===== Stopping a job =====
impdp system/THE_PASSWORD ATTACH=JOBNAME
Then
Kill
select ''yes'' and wait, it will take some time.
===== Killing sessions from oracle =====
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
/
===== Drop table associated to a DP job =====
After killing a job
SELECT 'DROP TABLE ' || owner_name || '.' || job_name || ' ; ' FROM dba_datapump_jobs WHERE STATE = 'NOT RUNNING' ;
====== do not remove ======