DB Locks

To check the Locks on Database


SELECT s1.username || '@' || s1.machine
    || ' ( SID=' || s1.sid || ' )  is blocking '
    || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
    FROM v$lock l1, v$session s1, v$lock l2, v$session s2
    WHERE s1.sid=l1.sid AND s2.sid=l2.sid
    AND l1.BLOCK=1 AND l2.request > 0
    AND l1.id1 = l2.id1
    AND l1.id2 = l2.id2;

Blocking Session Information 

col sid format 9999

col username format a10

col osuser format a10

col program format a25

col process format 9999999

col spid format 999999

col logon_time format a13

set lines 150

set heading off

set verify off

set feedback off

undefine sid_number

undefine spid_number

rem accept sid_number number prompt "pl_enter_sid:"

col sid NEW_VALUE sid_number noprint

col spid NEW_VALUE spid_number noprint

         select  s.sid   sid,

                p.spid  spid

--              ,decode(count(*), 1,'null','No Session Found with this info') " "

         FROM v$session s,

              v$process p

         WHERE s.sid LIKE NVL('&sid', '%')

         AND p.spid LIKE NVL ('&OS_ProcessID', '%')

         AND s.process LIKE NVL('&Client_Process', '%')

         AND s.paddr = p.addr

--       group by s.sid, p.spid;

PROMPT Session and Process Information

PROMPT -------------------------------

col event for a30

select '    SID                         : '||v.sid      || chr(10)||

       '    Serial Number               : '||v.serial#  || chr(10) ||

       '    Oracle User Name            : '||v.username         || chr(10) ||

       '    Client OS user name         : '||v.osuser   || chr(10) ||

       '    Client Process ID           : '||v.process  || chr(10) ||

       '    Client machine Name         : '||v.machine  || chr(10) ||

       '    Oracle PID                  : '||p.pid      || chr(10) ||

       '    OS Process ID(spid)         : '||p.spid     || chr(10) ||

       '    Session''s Status           : '||v.status   || chr(10) ||

       '    Logon Time                  : '||to_char(v.logon_time, 'MM/DD HH24:MIpm')   || chr(10) ||

       '    Program Name                : '||v.program  || chr(10)

from v$session v, v$process p

where v.paddr = p.addr

and v.serial# > 1

and p.background is null

and p.username is not null

and sid = &sid_number

order by logon_time, v.status, 1

/

PROMPT Sql Statement

PROMPT --------------

select sql_text

from v$sqltext , v$session

where v$sqltext.address = v$session.sql_address

and sid = &sid_number

order by piece

/

PROMPT

PROMPT Event Wait Information

PROMPT ----------------------

select '   SID '|| &sid_number ||' is waiting on event  : ' || x.event || chr(10) ||

       '   P1 Text                      : ' || x.p1text || chr(10) ||

       '   P1 Value                     : ' || x.p1 || chr(10) ||

       '   P2 Text                      : ' || x.p2text || chr(10) ||

       '   P2 Value                     : ' || x.p2 || chr(10) ||

       '   P3 Text                      : ' || x.p3text || chr(10) ||

       '   P3 Value                     : ' || x.p3

from v$session_wait x

where x.sid= &sid_number

/

PROMPT

PROMPT Session Statistics

PROMPT ------------------

select        '     '|| b.name  ||'             : '||decode(b.name, 'redo size', round(a.value/1024/1024,2)||' M', a.value)

from v$session s, v$sesstat a, v$statname b

where a.statistic# = b.statistic#

and name in ('redo size', 'parse count (total)', 'parse count (hard)', 'user commits')

and s.sid = &sid_number

and a.sid = &sid_number

--order by b.name

order by decode(b.name, 'redo size', 1, 2), b.name

/

COLUMN USERNAME FORMAT a10

COLUMN status FORMAT a8

column RBS_NAME format a10

PROMPT

PROMPT Transaction and Rollback Information

PROMPT ------------------------------------

select        '    Rollback Used                : '||t.used_ublk*8192/1024/1024 ||' M'          || chr(10) ||

              '    Rollback Records             : '||t.used_urec        || chr(10)||

              '    Rollback Segment Number      : '||t.xidusn           || chr(10)||

              '    Rollback Segment Name        : '||r.name             || chr(10)||

              '    Logical IOs                  : '||t.log_io           || chr(10)||

              '    Physical IOs                 : '||t.phy_io           || chr(10)||

              '    RBS Startng Extent ID        : '||t.start_uext       || chr(10)||

              '    Transaction Start Time       : '||t.start_time       || chr(10)||

              '    Transaction_Status           : '||t.status

FROM v$transaction t, v$session s, v$rollname r

WHERE t.addr = s.taddr

and r.usn = t.xidusn

and s.sid = &sid_number

/

PROMPT

PROMPT Sort Information

PROMPT ----------------

column username format a20

column user format a20

column tablespace format a20

SELECT        '    Sort Space Used(8k block size is asssumed    : '||u.blocks/1024*8 ||' M'             || chr(10) ||

              '    Sorting Tablespace                           : '||u.tablespace       || chr(10)||

              '    Sort Tablespace Type                 : '||u.contents || chr(10)||

              '    Total Extents Used for Sorting               : '||u.extents

FROM v$session s, v$sort_usage u

WHERE s.saddr = u.session_addr

AND s.sid = &sid_number

/

set heading on

set verify on

clear column