DBA_DATAPUMP_SESSIONS

SELECT w.sid, w.event, w.seconds_in_wait
FROM V$SESSION s, DBA_DATAPUMP_SESSIONS d, V$SESSION_WAIT w
WHERE s.saddr = d.saddr AND s.sid = w.sid;

Kill, cancel and resume or restart datapump expdp and impdp jobs

The expdp and impdp utilities are command-line driven, but when starting them from the OS-prompt, one does not notice it. When you want to kill, cancel, start or resume a job, you will and up in the datapump command prompt… now what?! All command shown here can be used with expdp and impdp datapump.

Identifying datapump jobs

Or when you use the JOB_NAME parameter when datapumping, you already identified the job with a name. You don’t need to look up afterwards…

Killing or stopping a running datapump job

The difference between Kill and Stop is simple to explain. When killing a job, you won’t be able to resume or start it again. Also logs and dumpfiles will be removed! When exporting (or importing), press Ctrl-c to show the datapump prompt and type KILL_JOB or STOP_JOB[=IMMEDIATE]. You will be prompted to confirm if you are sure… Adding ‘=IMMEDIATE‘ to STOP_JOB will not finish currently running ‘sub-job’ and must be redone when starting it again.

Kill Export Job

Analyze Import Log File

Prerequisite: import file is in the "imp_dir"

Step 1: Create external table

create or replace directory imp_dir as '/dba_cloudfs/datapump/vossit01/export/pvos/';

grant read on directory imp_dir to public;
grant write on directory imp_dir to public;

drop table import_log;

create table
   import_log(msg VARCHAR2(160))
organization external (
    type oracle_loader
    default directory imp_dir
   access parameters ( records delimited by newline)
    location ('import_vossit01.log')
)
reject limit 3000;

select * from import_log;

Step 2: Run Analysis

set lines 300
set pages 55

col mssg form a110

-- select substr(substr(msg,24),1,instr(substr(msg,24),':')-1), count(*)
-- from import_log
-- where substr(msg,1,9) = '&err_code'
-- group by substr(substr(msg,24),1,instr(substr(msg,24),':')-1);

select substr(msg,1,9), count(*) tally,
case substr(msg,1,9)
  when 'ORA-12911' then 'permanent tablespace cannot be temporary tablespace'||chr(10)||
                        'ACTIONS: ALTER USER  LOCAL TEMPORARY TABLESPACE ;'
  when 'ORA-39083' then 'Object type failed to create with error'||chr(10)||
                        'ACTIONS: Object type USER failed to create with error - related to local temporary tablespace '||chr(10)||
                        'Investigate missing privilges on DB schema objects such as procedure, package or view.'
  when 'ORA-31684' then 'Object type already exists'||chr(10)||
                        'ACTIONS: No action to take; ignore error message'
  when 'ORA-39082' then 'Object type created with compilation warnings'||chr(10)||
                        'ACTIONS: Investigate missing privilges on DB schema objects such as procedure, package or view.'
  when 'ORA-04042' then 'procedure, function, package, or package body does not exist'||chr(10)||
                        'ACTIONS: ALTER USER  LOCAL TEMPORARY TABLESPACE ;'
  when 'ORA-01775' then 'looping chain of synonyms'||chr(10)||
                        'ACTIONS: Investigate the reason for the synonym being orphaned.'
  when 'ORA-01919' then 'role NONE does not exist'||chr(10)||
                        'ACTIONS: Ignore errors after confirming these missing grants do not impact your import goals'||chr(10)||
                        ' Consult with MOS note: Doc ID 1916469.1'
  when 'ORA-04045' then 'Errors during recompilation/revalidation'||chr(10)||
                        'ACTIONS: No action to take; secondary error message seeen associated withn looping synonyms'
  when 'ORA-01950' then 'no privileges on tablespace'||chr(10)||
                        'ACTIONS: ALTER USER  QUOTA 100M ON '||chr(10)||
                        'GRANT UNLIMITED TABLESPACE TO '
  when 'ORA-27477' then 'DB_AUTOMATIC_MAINTENANCE_WNDW already exists'||chr(10)||
                        'ACTIONS: No action to take; ignore error message'
  when 'ORA-04021' then 'timeout occurred while waiting to lock object SYS.oracle/i18n/data/lx20001.glb'||chr(10)||
                        'ACTIONS: ALTER SESSION SET DDL_LOCK_TIMEOUT'||chr(10)||
                        'Kill the session holding the object'
  when 'ORA-39111' then 'Dependent object type OBJECT_GRANT:SYSTEM. SELECT skipped'||chr(10)||
                        'ACTIONS: Relatd to error messaage ORA-31684'
  when 'ORA-31685' then 'Object type USER:SYS failed due to insufficient privileges. Failing sql is:'||chr(10)||
                        'ACTIONS: Investigate each incident'
  when 'ORA-13830' then 'SQL profile or patch with category DEFAULT already exists for this SQL statement'||chr(10)||
                        'ACTIONS: No action to take ignore error message'
  else '** unknown **'
END mssg
from import_log
where msg like 'ORA-%'
group by substr(msg,1,9)
order by 2 DESC
/

select msg from import_log where substr(msg,1,9) = '&err_code';