PL/SQL Kill Db connections

select max(ctime), sid from dbms_lock_allocated d,v$lock l
where d.lockid = l.id1 and ctime >30
group by sid

 
select
    s1.username || ‘@’ || s1.machine
    || ‘ ( SID,S#=’ || s1.sid || ‘,’ || s1.serial# || ‘ )  is blocking ‘
    || s2.username || ‘@’ || s2.machine
    || ‘ ( SID,S#=’ || s2.sid || ‘,’ || s2.serial# || ‘ )’
        AS blocking_status
from
    v$lock l1,
    v$session s1,
    v$lock l2,
    v$session s2
where
    s1.sid = l1.sid
    and s2.sid = l2.sid
    and l1.BLOCK = 1
    and l2.request > 0
    and l1.id1 = l2.id1
    and l2.id2 = l2.id2;
select  ‘ALTER SYSTEM KILL SESSION ”’ || sid  || ‘,’ || serial# || ”’;’ from v$session where sid in ( select sid from dbms_lock_allocated d,v$lock l where d.lockid = l.id1 and ctime >300);
column oracle_username format a15
column os_user_name format a15
column object_name format a37
column object_type format a37
select a.session_id,a.oracle_username, a.os_user_name, b.owner “OBJECT OWNER”, b.object_name,b.object_type,a.locked_mode from
(select object_id, SESSION_ID, ORACLE_USERNAME, OS_USER_NAME, LOCKED_MODE from v$locked_object) a,
(select object_id, owner, object_name,object_type from dba_objects) b
where a.object_id=b.object_id
select c.owner, c.object_name, c.object_type,b.sid, b.serial#, b.status, b.osuser, b.machine
from v$locked_object a , v$session b, dba_objects c
where b.sid = a.session_id
and a.object_id = c.object_id;
select
     username,
     v$lock.sid,
     trunc(id1/power(2,16)) rbs,
     bitand(id1,to_number(‘ffff’,’xxxx’))+0 slot,
     id2 seq,
     lmode,
     request
from v$lock, v$session
where v$lock.type = ‘TX’
and v$lock.sid = v$session.sid
and v$session.username = USER
/

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *