Further notes on Sessions and Blocking

-- The following are just a few commands that are useful.
-- take particular note of the blocking locks statement that doesn't use the lock table. It's just different.

-- Show all my sessions
select *
from v$session sess
where sess.osuser = 'j36563';

-- Show specific details for my sessions
select sess.sid, sess.serial#, sess.username, sess.osuser, sess.schemaname, sess.program, sess.sql_id
from v$session sess
where sess.osuser = 'j36563';

-- Show sql details for my sessions.
select sess.sid, sess.serial#, sess.username, sess.osuser, sess.schemaname, sess.program, sess.sql_id,
stxt.sql_text
from v$session sess,
V$sqltext stxt
where sess.osuser = 'j36563'
and sess.sql_id = stxt.sql_id;

-- Show v$accesss locks for a given object
SELECT * FROM v$access WHERE lower(object) = 'pkg_rege_loss_objection';

-- Show detailed information regarding access locks for a given package
select sess.sid, sess.serial#, sess.username, sess.osuser, sess.schemaname, sess.program, sess.sql_id,
'alter system kill session '''||sid||','||serial#||''' immediate;' comm
from v$session sess where sid in (
SELECT sid FROM v$access WHERE lower(object) = 'pkg_xml_schema_util');

-- show blocking locks
select sess1.username || ' on ' || sess1.machine || ' (' || sess1.sid || '/' || sess1.serial# || ') ' ||
'Is blocking ' ||
sess2.username || ' on ' || sess2.machine || ' (' || sess2.sid || '/' || sess2.serial# || ') ' ||
' => ' || dobj1.owner || '.' || dobj1.object_name || ' (' || dobj1.object_type || ')' "object"
from v$lock lock1,
v$session sess1,
v$lock lock2,
v$session sess2,
v$locked_object lobj1,
dba_objects dobj1
where sess1.sid=lock1.sid and sess2.sid=lock2.sid
and lock1.BLOCK=1 and lock2.request > 0
and lock1.id1 = lock2.id1
and lock2.id2 = lock2.id2
and sess1.sid = lobj1.session_id
and lobj1.object_id = dobj1.object_id
/

-- Another way to view blocking locks
select sid, serial#, username, program, blocking_session_status, blocking_instance, blocking_session, p2text, p2, sql_exec_start,
'alter system kill session '''||sid||','||serial#||''' immediate;' comm
from v$session sess
where blocking_session_status = 'VALID';
--where (sess.osuser = 'j36563' or sid = 20);

-- or
select sess2.username || ' on ' || sess2.machine || ' (' || sess.blocking_session || '/' || sess2.serial# || ') ' ||
'Is blocking ' ||
sess.username || ' on ' || sess.machine || ' (' || sess.sid || '/' || sess.serial# || ') '
from v$session sess, v$session sess2
where sess.blocking_session_status = 'VALID'
and sess.blocking_session = sess2.sid;

-- Kill a session (change the sid,serial# for values)
alter system kill session 'sid,serial#' immediate;


happyjohn