spool c:\lock.txt
set line 1000
set echo off
set serveroutput on
--set feedback off

prompt '获取数据.....'
create table  oldnong_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 nvl(a.username,'NULL')< >'NULL';

create table  oldnong_lock as
select id1, kaddr, sid, request,type
  from v$lock;

/*
create table  oldnong_sqltext as
select hash_value , sql_text
  from v$sqltext s, oldnong_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 oldnong_session a, oldnong_lock b
 where a.lockwait = b.kaddr
/

prompt "被等待的用户"
select a.sid, a.serial#, 
a.machine,  a.last_call_et,a.username,
b.type,a.status,b.id1
  from oldnong_session a, oldnong_lock b
 where b.id1 in
      (select distinct e.id1
         from oldnong_session d, oldnong_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 oldnong_session a, oldnong_lock b, oldnong_sqltext c
 where b.id1 in
       (select distinct e.id1
          from oldnong_session d, oldnong_lock e
         where d.lockwait = e.kaddr)
   and a.sid = b.sid
   and b.request=0
   and c.hash_value =a.sql_hash_value
/
*/ 

prompt "删除临时表"
drop table oldnong_session;
drop table oldnong_lock;
--drop table oldnong_sqltext;

spool off