Put your tips and tricks online - Share your knowledge! Login | Register
 
 
  Search     Advanced search
 

Home | Ask Question | Add tip | Questions | My tips | Recent tips & tricks | Suggest a category | FAQ | Forums

 
 
 
 Category : Home > Databases > Oracle > Performance Tuning     

Display TX blocking locks


-- Following script detects TX blocking locks:

set linesize 160
set trimspool on
column name format A28
column type format A18
column sid format 9999
column block format 99
column ctime format 99999
column serial format 99999
column id1 format 99999999
column id2 format 99999999
column LMODE format A12
column request format A12
spool log.lock

select /*+ ordered use_merge(X$KSUSE X$KSQEQ) */
    l.ctime as ctime,
    l.sid,
    s.SERIAL# as serial,
    substr(s.USERNAME,1,8) as  username,
    substr(s.osuser,1,8) as osuser,
    substr(s.PROGRAM,1,15) as program,
    l.id1,
    l.id2,
    o.name,
    decode(l.type,
    'MR', 'MR, Media Recovery',
    'RT','RT, Redo Thread',
    'UN', 'UN, User Name',
    'TX', 'TX, Transaction',
    'TM', 'TM, DML',
    'UL', 'UL, PL/SQL User Lock',
    'DX', 'DX, Distributed Xaction',
    'CF', 'CF, Control File',
    'IS', 'IS, Instance State',
    'FS', 'FS, File Set',
    'IR', 'IR, Instance Recovery',
    'ST', 'ST, Disk Space Transaction',
    'TS', 'TS, Temp Segment',
    'IV', 'IV, Library Cache Invalidation',
    'LS', 'LS, Log Start or Switch',
    'RW', 'RW, Row Wait',
    'SQ', 'SQ, Sequence Number',
    'TE', 'TE, Extend Table',
    'TT', 'TT, Temp Table',
    l.type) as type,
    decode(lmode,0,'None',
        1,'Null',
        2,'Row-S(SS)',
        3,'Row-X(SX)',
        4,'Share',
        5,'S/Row-X(SSX)',
        6,'Exclusive',
        'Unkwon') as Lmode,
    decode(request,0,'None',
        1,'Null',
        2,'Row-S(SS)',
        3,'Row-X(SX)',
        4,'Share',
        5,'S/Row-X(SSX)',
        6,'Exclusive',
        'Unkwon') as request,
    l.block
from v$lock l, v$session s, sys.obj$ o
where l.sid = s.sid
and l.id1 = o.obj# (+)
and ( request <> 0 OR l.block =1)
order by l.sid,o.name
/


  Options
 
   del.icio.us  |  newsvine  |  digg  |  furl  |  google  |  yahoo  |  Ma.gnolia  |  vigillar  |  reddit  |  technorati  |  icerocket  |  pubsub

       Rate this tip:    

Start discussion or add comment to this tip

  Details
Tip reference : #310
views : 1137
Added on : 05/29/15
Submited by : h8dk97
 
Send a message Send a message Printer friendly output Printer friendly output
Display this member's tips Display this member's tips (219)
 
 
<< Previous Next >>
 Most viewed tips 
  Databases > Oracle > Security : How to unlock Oracle user account  
  Databases > Oracle > Performance Tuning : How to enable trace in Oracle  
  Databases > Oracle > DR : Adding new datafiles to standby database  
  Operating Systems > Unix : How to kill Unix user session  
   
  All categories
Databases | Programming | Hardware | Operating Systems | Networking | Internet | ERP / CRM | Games & Multimedia | Graphics & Design | Miscellaneous | Mobile Phones / Tablets | Office Software
 
 

Home |  FAQ |  Terms of Use |  Privacy Policy

© 2005 tiplib.com