select a.username, a.sid, a.serial#, b.id1
 from v$session a, v$lock b
 where a.lockwait = b.kaddr

  select a.username, a.sid, a.serial#, b.id1
   from v$session a, v$lock b
   where b.id1 in
      (select distinct e.id1
       from v$session d, v$lock e
       where d.lockwait = e.kaddr)
    and a.sid = b.sid
    and b.request = 0

drop table my_session;
create table my_session
as
select a.username, a.sid, a.serial#,
a.lockwait, a.machine,a.status,
a.last_call_et,a.sql_hash_value,a.program
 from v$session a
 where 1=2 ;

create unique index my_session_u1 on my_session(sid);
create index my_session_n2 on my_session(lockwait);
create index my_session_n3 on my_session(sql_hash_value);

drop table my_lock;
create table my_lock
as
select id1, kaddr, sid, request,type
 from v$lock
 where 1=2;

create index my_lock_n1 on my_lock(sid);
create index my_lock_n2 on my_lock(kaddr);

drop table my_sqltext;
create table my_sqltext
as
select hash_value , sql_text
 from v$sqltext
 where 1=2;

create index my_sqltext_n1 on my_sqltext ( hash_value);

alter system kill session 'sid, serial#'; 

set echo off
set feedback off
prompt '删除旧记录.....'
truncate table my_session;
truncate table my_lock;
truncate table my_sqltext;

prompt '获取数据.....'
insert into my_session
select a.username, a.sid, a.serial#,
    a.lockwait, a.machine,a.status,
    a.last_call_et,a.sql_hash_value,a.program
 from v$session a
 where nvl(a.username,'NULL')< >'NULL;

insert into my_lock
select id1, kaddr, sid, request,type
 from v$lock;

insert into my_sqltext
select hash_value , sql_text
 from v$sqltext s, my_session m
 where s.hash_value=m.sql_hash_value;

column username format a10
column machine format a15
column last_call_et format 99999 heading "Seconds"
column sid format 9999

prompt "正在等待别人的用户"
select a.sid, a.serial#, 
a.machine,a.last_call_et, a.username, b.id1
 from my_session a, my_lock b
 where a.lockwait = b.kaddr;

prompt "被等待的用户"
select a.sid, a.serial#, 
a. machine, a.last_call_et,a.username,
b. b.type,a.status,b.id1
 from my_session a, my_lock b
 where b.id1 in
   (select distinct e.id1
     from my_session d, my_lock e
    where d.lockwait = e.kaddr)
  and a.sid = b.sid
  and b.request=0;

prompt "查出其 sql "
select a.username, a.sid, a.serial#,
 b.id1, b.type, c.sql_text
 from my_session a, my_lock b, my_sqltext c
 where b.id1 in
    (select distinct e.id1
     from my_session d, my_lock e
     where d.lockwait = e.kaddr)
  and a.sid = b.sid
  and b.request=0
  and c.hash_value =a.sql_hash_value;