Differences

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

--- oracle:import_and_export [2020/03/26 16:03] – dodger
+++ dba:oracle:docs:import_and_export [2023/01/31 08:22] – removed - external edit (Unknown date) 127.0.0.1
@@ 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>