Script to kill Import(imp.exe) job immediately

Script to kill Import(imp.exe) job immediately

Check the Status of import job is working or stuck

col table_name format a20
select substr(sql_text,instr(sql_text,'INTO "'),30) table_name,
rows_processed,
round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,
trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_min
from sys.v_$sqlarea
where sql_text like 'INSERT %INTO "%'
and command_type = 2
and open_versions > 0;

TABLE_NAME ROWS_PROCESSED MINUTES ROWS_PER_MIN
------------------------------ -------------- ---------- ------------
INTO "EMP2" ("EMPLOYEE_ID", "F 22668765 5.8 3908407

Check the import(imp.exe) job and manually kill by alter command

column username format a15 word_wrapped
column module format a15 word_wrapped
column action format a15 word_wrapped
column client_info format a30 word_wrapped

select username||'('||sid||','||serial#||')' username,
module,
action,
client_info
from v$session
where module='imp.exe';

--For manually kill
alter system kill session 'sid,serial#';

Script to Kill the imp job immediately

column username format a20
column sql_text format a55 word_wrapped
set serveroutput on size 1000000
declare
x number;
begin
for x in (
select sid,Serial#,Module,Username,action,client_info,to_char(LOGON_TIME,' Day HH24:MI') logon_time, to_char(sysdate,' Day HH24:MI') current_time from v$session where module='imp.exe'
)
loop
EXECUTE IMMEDIATE 'alter system kill session ''' || x.sid || ',' || x.serial# || '''';
dbms_output.put_line( '---------IMP JOB KILLED DETAILS:---------' );
dbms_output.put_line( x.username );
dbms_output.put_line( x.sid || ' '|| x.serial# || ' session is killed' );
dbms_output.put_line( 'Login Time:'||x.Logon_time || ' Current Time:' || x.current_time);
end loop;
end;
/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.