Select the session details with SQL command below. Use additional conditions to precise output.
select * from v$session where upper(username)='DILLI'
-- and type='USER';
-- and osuser='DRM'
-- and program='JDBC Thin Client';
List all the jobs that is running with user.
select * from dba_jobs where upper(LOG_USER)='DILLI';
Mark the job as broken so that oracle do not try to run this job again after killing the oracle session . Use dbms_job.broken to set oracle job as broken before removing the job.
exec dbms_job.broken(JOBID,TRUE);
Dbms_ijob package allows to set broken, run and remove other user's job.
exec dbms_ijob.broken(JOBID,TRUE);
Removing the job after setting the job to broken state.
exec sys.dbms_ijob.remove(JOBID);
exec dbms_job.remove(JOBID);
Create SQL command to kill session execute the output to kill the oracle session.
select 'alter system kill session ''' || sid || ',' || serial# || ''' immediate;' from v$session where upper(username)='DILLI';
Use command below to view view the rollback segments details.
SELECT r.NAME "RB Segment Name", dba_seg.size_mb,
DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') ||
TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') LOGON,
v$session.SID, v$session.SERIAL#, p.SPID, v$session.process,
v$session.USERNAME, v$session.STATUS, v$session.OSUSER, v$session.MACHINE,
v$session.PROGRAM, v$session.module, action
FROM v$lock l, v$process p, v$rollname r, v$session,
(SELECT segment_name, ROUND(bytes/(1024*1024),2) size_mb FROM dba_segments
WHERE segment_type = 'TYPE2 UNDO' ORDER BY bytes DESC) dba_seg
WHERE l.SID = p.pid(+) AND
v$session.SID = l.SID AND
TRUNC (l.id1(+)/65536)=r.usn AND
l.TYPE(+) = 'TX' AND
l.lmode(+) = 6
AND r.NAME = dba_seg.segment_name
AND v$session.username = 'DILLI'
--AND status = 'INACTIVE'
ORDER BY size_mb DESC;
SELECT s.username, s.SID, s.serial#, s.logon_time, t.xidusn, t.ubafil, d.tablespace_name,
t.ubablk, t.used_ublk, t.start_date, t.status
FROM v$session s, v$transaction t ,dba_rollback_segs d
WHERE s.saddr = t.ses_addr
and t.xidusn=d.segment_id;
Find the process and session details in case we need to manually kill OS PID. This happends when we kill the oracle session but the session stays on the PSEUDO state.
select p.*,s.* from v$process p, v$session s
where p.addr=s.paddr
and upper(s.status)='KILLED';
Get the SPID from v$process table and use in OS kill command. If the session goes to rollback we can kill process in OS so that it will forcefully terminate session and rollback can be performed faster.
KILL -9 {SPID}
To perform fast rollback we need to set Oracle parameter fast_start_parallel_rollback values to High. Check the fast_start_parallel_rollback parameter and set it to HIGH.
show parameter fast_start_parallel_rollback;
select * from v$parameter where name like '%fast%';
alter system set fast_start_parallel_rollback='HIGH';
Check the fast rollback state and ETA with command below.
SELECT usn, state, undoblockstotal "Total", undoblocksdone "Done",
undoblockstotal-undoblocksdone "ToDo",
DECODE(cputime,0,'unknown',SYSDATE+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400)) "Finish at"
FROM v$fast_start_transactions;
