Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

Oracle Concepts - Managing the Database

Oracle Tips by Burleson Consulting

Managing the Database

 There are many tasks that a DBA must perform when managing the database. Among the most important (besides backup and recovery which we already covered) is the ability to resolve lock and deadlock situations. A lock situation is when one user is holding a lock on a resource when another user requires it. A lock situation usually involves one of two things, either a poorly written application or a user who has not committed a transaction. Both of these lock situations are resolved by finding the problem session or user and either picking up the phone and getting the user themselves to correct the situation or killing the users session via database commands.

Dead-lock situations occur when users require the same resource in such a manner that neither that can release it without the other releasing first. Usually Oracle will resolve deadlocks by rolling back the transaction of the first user process that detects the deadlock.

Find USER locking others/Kill problem USER

In order to find a lock holding user I suggest that the DBA be proactive and create the DBA_WAITERS and DBA_BLOCKERS views using the catblock.sql script which is located in the $ORACLE_HOME/rdbms/admin directory or its equivalent. Once the waiter and blocker views are created  a DBA can simply query either view to find the lock situation. Sources 29 and 30 show scripts to query these views.

rem NAME: blockers.sql
rem FUNCTION: Show all processes causing a dead lock
rem HISTORY: MRA 1/15/96 Created
rem
COLUMN username         FORMAT a10 HEADING 'Holding|User'
COLUMN session_id                         HEADING 'SID'
COLUMN mode_held        FORMAT a20 HEADING 'Mode|Held'
COLUMN mode_requested   FORMAT a20 HEADING 'Mode|Requested'
COLUMN lock_id1         FORMAT a20 HEADING 'Lock|ID1'
COLUMN lock_id2         FORMAT a20 HEADING 'Lock|ID2'
COLUMN type                               HEADING 'Lock|Type'
SET LINES 132 PAGES 59 FEEDBACK OFF ECHO OFF
START title132 'Sessions Blocking Other Sessions Report'
SPOOL rep_out\&db\blockers
SELECT
      a.session_id,
      username,
      type,
      mode_held,
      mode_requested,
      lock_id1,
      lock_id2
FROM
      sys.v_$session b,
      sys.dba_blockers c,
      sys.dba_lock a
WHERE
      c.holding_session=a.session_id and
      c.holding_session=b.sid
/
SPOOL OFF
PAUSE press enter/return to continue
CLEAR COLUMNS
SET LINES 80 PAGES 22 FEEDBACK ON ECHO ON

Source 29: Example script to report on blocking locks

rem
rem FUNCTION: Report on sessions waiting for locks
rem
COLUMN busername FORMAT A10 HEADING 'Holding|User'
COLUMN wusername FORMAT A10 HEADING 'Waiting|User'
COLUMN bsession_id HEADING 'Holding|Sid'
COLUMN wsession_id HEADING 'Waiting|Sid'
COLUMN mode_held FORMAT A20 HEADING 'Mode|Held'
COLUMN mode_requested FORMAT A20 HEADING 'Mode|Requested'
COLUMN lock_id1 FORMAT A20 HEADING 'Lock|Id1'
COLUMN lock_id2 FORMAT A20 HEADING 'Lock|Id2'
COLUMN type HEADING 'LOCK|TYPE'
SET LINES 132 PAGES 59 FEEDBACK OFF ECHO OFF
START TITLE132 'Processes Waiting on Locks Report'
SPOOL rep_out/&db/waiters
SELECT
      holding_session bsession_id,
      waiting_session wsession_id,
      b.username busername,
      a.username wusername,
      c.lock_type type,
      mode_held, mode_requested,
      lock_id1, lock_id2
FROM
sys.v_$session b, sys.dba_waiters c, sys.v_$session a
WHERE
c.holding_session=b.sid and
c.waiting_session=a.sid
/
SPOOL OFF
PAUSE press enter/return to continue
CLEAR COLUMNS
SET LINES 80 PAGES 22 FEEDBACK ON ECHO ON
TTITLE OFF

Source 30: Example script to report on blocking and waiting sessions.

Example output from the blockers report is shown in Listing 29 for a simple lock situation.

Date: 02/08/00                                                                            Page:   1
Time: 07:01 AM                Sessions Blocking Other Sessions Report                     SYSTEM
                                       aultdb database
 

          Holding    Lock       Mode         Mode                 Lock                 Lock
      SID User       Type       Held         Requested            ID1                  ID2
--------- ---------- ---------- ------------ -------------------- -------------------- ------------
        9 LABUSER    USER       Row-X (SX)   None                 2821                 0                                   
        9 LABUSER    USER       Exclusive    None                 196702               5547             
                  


Listing 29: example Blockers Report

The output for the same lock situation from the waiters report is shown in Listing 30.

Date:02/08/00
                                          Page:   1
Time: 07:01 AM                   Processes Waiting on Locks Report
SYSTEM
                                     aultdb database

  Holding   Waiting Holding    Waiting     Lock         ModMode                 Lock                 Lock
      SID       SID User       User        Type         Held                 Requested            ID1                  ID2
--------- --------- ---------- ----------- ------------ -------------------- -------------------- -------------------- -----------
        9        13 LABUSER    LABUSER2   Transaction   Exclusive            Exclusive            196702              5547

Listing 30: Example Waiters report

While both reports show who is holding the lock, I prefer the waiters report since it also shows who is being blocked . Using the reports it becomes a simple matter to retrieve the required information from the V$SESSION view to either notify the locking process owner or simply kill the offending process outright if it is a non-critical process. Tools such as Precise*SQL and Q from Savant provide lock status and the Q product allows killing of processes in a fairly easy manner.

 


This is an excerpt from the eBook "Oracle DBA made Simple".

For more details on Oracle database administration, see the "Easy Oracle Jumpstart" by Robert Freeman and Steve Karam.  It?s only $19.95 when you buy it directly from the publisher here.

 


 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.