How query dead lock in oracle

1. query the connection that might has dead lock

  select username, lockwait, status, machine, program

from v$session

where sid in (select session_id from v$locked_object);

2. query the locked sql

    select sql_text

from v$sql

where hash_value in

    (select sql_hash_value from v$session where sid in (select session_id from v$locked_object));

3. The pending transactions (may be culprit)

  select s.sid,

      s.username,

      s.osuser,

      s.program,

      to_char(s.LOGON_TIME, 'yyyymmdd hh24:mi:ss')      as LOGON_TIME,

      to_char(t.START_DATE, 'yyyymmdd hh24:mi:ss')      as START_DATE,

      s.status,

      (select q.SQL_TEXT from v$sql q where q.LAST_ACTIVE_TIME = t.START_DATE

                                        and rownum <= 1) as SQL_TEXT

from v$session s,

    v$transaction t

where s.sADDR = t.SES_ADDR;

4. query the session id that has locked object

select sess.sid, sess.serial#, lo.oracle_username, lo.os_user_name, ao.object_name, lo.locked_mode, SESS.machine

from v$locked_object lo,

    dba_objects ao,

    v$session sess

where ao.object_id = lo.object_id

  and lo.session_id = sess.sid;

5. query the locked objects

    SELECT l.session_id sid,

            s.serial#,

            l.locked_mode,

            l.oracle_username,

            l.os_user_name,

            s.machine,

            s.terminal,

            o.object_name,

            s.logon_time

        FROM v$locked_object l, all_objects o, v$session s

      WHERE l.object_id = o.object_id AND l.session_id = s.sid

      ORDER BY sid, s.serial#;

6. kill the locking session

    alter system kill session '139,6151';    --  the number is [sid,serial#]