User Tools

Site Tools


import_and_export

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

and:

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

 
 
 
import_and_export.txt · Last modified: 2017/06/23 10:31 by dodger