学步园

1锁表解除

select s.sid,

       s.serial#,

       do.object_name,

       l.oracle_username,

       s.machine,

       l.os_user_name,

       l.locked_mode

  from v$locked_object l, dba_objects do, v$session s

 where do.object_id = l.object_id

   and s.sid = l.session_id;

-- alter system kill session 'sid,#serial';

2查找未加索引的外键(可能导致死锁)

select table_name,

       constraint_name,

       cname1 || nvl2(cname2, ',' || cname2, null) ||

       nvl2(cname3, ',' || cname3, null) ||

       nvl2(cname4, ',' || cname4, null) ||

       nvl2(cname5, ',' || cname5, null) ||

       nvl2(cname6, ',' || cname6, null) ||

       nvl2(cname7, ',' || cname7, null) ||

       nvl2(cname8, ',' || cname8, null) columns

  from (select b.table_name,

               b.constraint_name,

               max(decode(position, 1, column_name, null)) cname1,

               max(decode(position, 2, column_name, null)) cname2,

               max(decode(position, 3, column_name, null)) cname3,

               max(decode(position, 4, column_name, null)) cname4,

               max(decode(position, 5, column_name, null)) cname5,

               max(decode(position, 6, column_name, null)) cname6,

               max(decode(position, 7, column_name, null)) cname7,

               max(decode(position, 8, column_name, null)) cname8,              

               count(*) col_cnt

          from (select substr(table_name, 1, 30) table_name,

                       substr(constraint_name, 1, 30) constraint_name,

                       substr(column_name, 1, 30) column_name,

                       position

                  from user_cons_columns) a,

               user_constraints b

         where a.constraint_name = b.constraint_name

           and b.constraint_type = 'R'

         group by b.table_name, b.constraint_name) cons

 where col_cnt > all

 (select count(*)

          from user_ind_columns i

         where i.table_name = cons.table_name

           and i.column_name in (cname1, cname2, cname3, cname4, cname5,

                cname6, cname7, cname8)

           and i.column_position <= cons.col_cnt

         group by i.index_name)

3查看造成阻塞与被阻塞的锁

select (select username from v$session where sid = a.sid) blocker,

       a.sid,

       ' is blocking ',

       (select username from v$session where sid = b.sid) blockee,

       b.sid

  from v$lock a, v$lock b

 where a.block = 1

   and b.request > 0

   and a.id1 = b.id1

   and a.id2 = b.id2;

4查看锁的状态

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-->