User Tools

Site Tools


import_and_export

DB querys

List running jobs

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;
 
 
 
 
import_and_export.txt · Last modified: 2017/03/24 10:10 by dodger