Ciberterminal Wiki

User Tools

Site Tools

Trace:
dba:oracle:docs:import_and_export

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
dba:oracle:docs:import_and_export [2023/01/31 08:22] – removed - external edit (Unknown date) 127.0.0.1dba:oracle:docs:import_and_export [2023/01/31 08:22] (current) – ↷ Page moved from dba:oracle:import_and_export to dba:oracle:docs:import_and_export dodger
Line 1: Line 1:
 +====== [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>