查看持有行锁的SQL

<pre name="code" class="sql">SQL> select sid, id1, ctime from v$lock where type in ('TM', 'TX');

       SID	  ID1	   CTIME
---------- ---------- ----------
	23	76239	       6
	23     327693	       6



SQL>   select sql_text, to_char(c.LAST_LOAD_TIME,'hh24:mi:ss') from v$sqlarea c  where sql_text like '%delete%from%t100';

SQL_TEXT		       TO_CHAR(
------------------------------ --------
delete from t100	       23:56:05


SQL>  select to_char(sysdate,'hh24:mi:ss' ),ctime,to_char(sysdate- ctime/24/60/60,'HH24:MI:SS') from v$lock  where type in ('TM', 'TX');

TO_CHAR(      CTIME TO_CHAR(
-------- ---------- --------
23:56:23	 17 23:56:06
23:56:23	 17 23:56:06

只需要激活游标的时间<=行锁开始的时间即可


select c.sql_text,
       a.*,
       b.object_name,
       to_date(to_char(sysdate - a.ctime / 24 / 60 / 60, 'HH24:MI:SS'),
               'HH24:MI:SS') as lock_time
  from (select sid, id1, ctime from v$lock where type in ('TM', 'TX')) a,
       dba_objects b,
       v$sqlarea c
 where a.id1 = b.object_id
   and to_date(to_char(c.LAST_LOAD_TIME, 'hh24:mi:ss'), 'hh24:mi:ss') <=
       to_date(to_char(sysdate - a.ctime / 24 / 60 / 60, 'HH24:MI:SS'),
               'HH24:MI:SS')
   and c.PARSING_SCHEMA_NAME = 'TEST'
   and (c.sql_text like '%update%' or c.sql_text like '%delete%' or
       c.sql_text like '%insert%')
   and c.sql_text like '%t100%'

在有绑定变量的情况下,游标有可能时刻刷新,那么active_time的时间会大于lock_time的时间无法判断

select c.sql_text,
       a.*,
       b.object_name,
       to_date(to_char(c.LAST_active_TIME, 'hh24:mi:ss'), 'hh24:mi:ss') active_time,
       to_date(to_char(sysdate - a.ctime / 24 / 60 / 60, 'HH24:MI:SS'),
               'HH24:MI:SS') as lock_time,
       to_date(to_char(sysdate - a.ctime / 24 / 60 / 60 - 10 / 24 / 60 / 60,
                       'HH24:MI:SS'),
               'HH24:MI:SS') min_time
  from (select sid, id1, ctime from v$lock where type in ('TM')) a,
       dba_objects b,
       v$sqlarea c
 where a.id1 = b.object_id
/*   and to_date(to_char(c.LAST_active_TIME, 'hh24:mi:ss'), 'hh24:mi:ss') <=
       to_date(to_char(sysdate - a.ctime / 24 / 60 / 60, 'HH24:MI:SS'),
               'HH24:MI:SS')*/
   and c.PARSING_SCHEMA_NAME = 'TEST'
   and (c.sql_text like '%update%' or c.sql_text like '%delete%' or
       c.sql_text like '%insert%')
   and c.sql_text like '%t100%'
/*   and to_date(to_char(c.LAST_active_TIME + 10 / 24 / 60 / 60, 'hh24:mi:ss'),
               'hh24:mi:ss') >=
       to_date(to_char(sysdate - a.ctime / 24 / 60 / 60 - 10 / 24 / 60 / 60,
                       'HH24:MI:SS'),
               'HH24:MI:SS')*/
  and c.sql_text like '%delete from t100 where object_id = :n%';

select to_char(sysdate, 'hh24:mi:ss'),
ctime,
b.object_name,
to_char(sysdate - ctime / 24 / 60 / 60, 'HH24:MI:SS') lock_running_sql
from v$lock a, dba_objects b
where a.type in ('TM', 'TX')
and b.owner = 'TEST'
and a.id1 = b.object_id
and b.object_name='&object'

在根据sql运行的时间去v$sqlarea 按last_active_time 找,找到相依的SQL

select c.sql_text,
a.*,
b.object_name,
to_date(to_char(c.LAST_active_TIME, 'hh24:mi:ss'), 'hh24:mi:ss') active_time,
to_date(to_char(sysdate - a.ctime / 24 / 60 / 60, 'HH24:MI:SS'),
'HH24:MI:SS') as lock_time,
to_date(to_char(sysdate - a.ctime / 24 / 60 / 60 - 10 / 24 / 60 / 60,
'HH24:MI:SS'),
'HH24:MI:SS') min_time
from (select sid, id1, ctime from v$lock where type in ('TM')) a,
dba_objects b,
v$sqlarea c
where a.id1 = b.object_id
and to_date(to_char(c.LAST_active_TIME, 'hh24:mi:ss'), 'hh24:mi:ss') <=
to_date(to_char(sysdate - a.ctime / 24 / 60 / 60, 'HH24:MI:SS'),
'HH24:MI:SS')
and c.PARSING_SCHEMA_NAME = 'TEST'
and (c.sql_text like '%update%' or c.sql_text like '%delete%' or
c.sql_text like '%insert%')
and c.sql_text like '%t100%'
and to_date(to_char(c.LAST_active_TIME + 10 / 24 / 60 / 60, 'hh24:mi:ss'),
'hh24:mi:ss') >=
to_date(to_char(sysdate - a.ctime / 24 / 60 / 60 - 10 / 24 / 60 / 60,
'HH24:MI:SS'),
'HH24:MI:SS')