Oracle DBA 脚本：LOW COMPLEXITY LOCKING INFO

Rdbms:09.02.01
 

Execution Environment:
     <SQL, SQL*Plus>

Access Privileges:
     SELECT on V_$LOCK, V_$SESSION, SYS.USER$, and SYS.OBJ$ 

Usage:
     sqlplus <user>/<pw> @[SCRIPTFILE]
复制

Instructions:


PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text 
editors, e-mail packages, and operating systems handle text formatting (spaces, 
tabs, and carriage returns), this script may not be in an executable state
when you first receive it. Check over the script to ensure that errors of
this type are corrected.The script will produce an output file named [outputfile].
This file can be viewed in a browser or uploaded for support analysis.

NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product.  
Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.
复制

 ID1-ID2     Lock Type 
复制

Note:15476.1 FAQ about Detecting and Resolving Locking Conflicts
复制

Note:1020012.6 SCRIPT TO RETURN MEDIUM DETAIL LOCKING INFO
复制

Note:1020008.6 SCRIPT: FULLY DECODED LOCKING SCRIPT 
复制

Script:
----------- cut ---------------------- cut -------------- cut -------------- 
 
SET ECHO off 
REM NAME:   TFSLLOCK.SQL 
REM USAGE:@path/tfsllock
REM ------------------------------------------------------------------------ 
REM REQUIREMENTS: 
REM    SELECT on V$LOCK, V$SESSION, SYS.USER$, and SYS.OBJ$ 
REM ------------------------------------------------------------------------ 
REM PURPOSE: 
REM    The following locking information script provides the following  
REM    items:script: Username, Table Name, Lock Held, Lock Requested.  
REM    ID1-ID2, and Lock Type. 
REM 
REM    The TFTS series contains scripts to provide (more detailed) lock  
REM    information in a formats which are somewhat more difficult to read: 
REM    TFSMLOCK.SQL and TFSCLOCK.SQL. 
REM ------------------------------------------------------------------------ 
REM EXAMPLE: 
REM                             Lock Lock                  Lock 
REM    Username   Table Name              Held Req. ID1 - ID2        Type 
REM    ---------- ----------------------- ---- ---- ---------------- ---- 
REM    USER1      Rollback Segment           X  NONE 196615-215922    TX 
REM    USER1      Rollback Segment           X  NONE 196632-215907    TX  
REM    USER1      USER1.TABLE_CONFIGR        X  NONE 3364-0           TM  
REM    USER1      USER1.TT                  RX  NONE 3384-0           TM 
REM    SYS        Rollback Segment           X  NONE 131093-2324      TX 
REM    SYS        Rollback Segment        NONE    X  196615-215922    TX 
REM    SYS        USER1.TABLE_CONFIG        RX  NONE 3364-0           TM 
REM    SYSTEM     Rollback Segment           X  NONE 131100-2321      TX  
REM    SYSTEM     Rollback Segment        NONE    X  196615-215922    TX 
REM    SYSTEM     USER1.TABLE_CONFIG        RX  NONE 3364-0           TM 
REM    SYSTEM     USER1.TT                  RX  NONE 3384-0           TM 
REM    USUPPORT   Rollback Segment           X  NONE 131083-2316      TX   
REM  
REM ------------------------------------------------------------------------ 
REM DISCLAIMER: 
REM    This script is provided for educational purposes only. It is NOT  
REM    supported by Oracle World Wide Technical Support. 
REM    The script has been tested and appears to work as intended. 
REM    You should always run new scripts on a test instance initially. 
REM ------------------------------------------------------------------------ 
REM Main text of script follows: 

set linesize 80  
set pagesize 66  
column lmode heading 'Lock|Held' format a4  
column request heading 'Lock|Req.' format a4  
column username  format a10  heading 'Username'  
column tab format a30 heading 'Table Name'  
column LAddr heading 'ID1 - ID2' format a16  
column Lockt heading 'Lock|Type' format a4  
select nvl(S.USERNAME,'Internal') username,  
      decode(command,  
0,'None',decode(l.id2,0,U1.NAME||'.'||substr(T1.NAME,1,20),  
'Rollback Segment')) tab,  
      decode(L.LMODE,1,'NoLk', 2,' RS ', 3,' RX ',  
                4,'  S ', 5,' SRX', 6,'  X ','NONE') lmode,  
      decode(L.REQUEST,1,'NoLk', 2,' RSh ', 3,' RX ',  
        4,'  S ', 5,' SRX', 6,'  X ','NONE') request,  
l.id1||'-'||l.id2 Laddr, l.type Lockt  
from    V$LOCK L, V$SESSION S, SYS.USER$ U1, SYS.OBJ$ T1  
where   L.SID = S.SID and       T1.OBJ#  = decode(L.ID2,0,L.ID1,1)   
and     U1.USER# = T1.OWNER# and        S.TYPE != 'BACKGROUND'  
order by 1,2,5  
/ 

----------- cut ---------------------- cut -------------- cut -------------- 
Examples: 
                                          Lock Lock                  Lock 
Username   Table Name                     Held Req. ID1 - ID2        Type 
---------- ------------------------------ ---- ---- ---------------- ---- 
USER1 Rollback Segment                      X  NONE 196615-215922    TX 
USER1      Rollback Segment                 X  NONE 196632-215907    TX  
USER1      USER1.TABLE_CONFIG              RX  NONE 3364-0           TM  
USER1      USER1.TT                        RX  NONE 3384-0           TM 
SYS        Rollback Segment                 X  NONE 131093-2324      TX 
SYS        Rollback Segment               NONE X    196615-215922    TX 
SYS        USER1.TABLE_CONFIG              RX  NONE 3364-0           TM 
SYSTEM     Rollback Segment                 X  NONE 131100-2321      TX  
SYSTEM     Rollback Segment               NONE   X  196615-215922    TX 
SYSTEM     USER1.TABLE_CONFIG              RX  NONE 3364-0           TM 
SYSTEM     USER1.TT                        RX  NONE 3384-0           TM 
USUPPORT   Rollback Segment                 X  NONE 131083-2316      TX
复制

EXCEPT WHERE EXPRESSLY PROVIDED OTHERWISE, THE INFORMATION, SOFTWARE,
PROVIDED ON AN "AS IS" AND "AS AVAILABLE" BASIS. ORACLE EXPRESSLY DISCLAIMS
ALL WARRANTIES OF ANY KIND, WHETHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT
LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR
PURPOSE AND NON-INFRINGEMENT. ORACLE MAKES NO WARRANTY THAT: (A) THE RESULTS
THAT MAY BE OBTAINED FROM THE USE OF THE SOFTWARE WILL BE ACCURATE OR
RELIABLE; OR (B) THE INFORMATION, OR OTHER MATERIAL OBTAINED WILL MEET YOUR
EXPECTATIONS. ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE DOWNLOADED OR
OTHERWISE OBTAINED IS DONE AT YOUR OWN DISCRETION AND RISK. ORACLE SHALL HAVE
NO RESPONSIBILITY FOR ANY DAMAGE TO YOUR COMPUTER SYSTEM OR LOSS OF DATA THAT
RESULTS FROM THE DOWNLOAD OF ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE.

ORACLE RESERVES THE RIGHT TO MAKE CHANGES OR UPDATES TO THE SOFTWARE AT ANY
TIME WITHOUT NOTICE.
复制

IN NO EVENT SHALL ORACLE BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
SPECIAL OR CONSEQUENTIAL DAMAGES, OR DAMAGES FOR LOSS OF PROFITS, REVENUE,
DATA OR USE, INCURRED BY YOU OR ANY THIRD PARTY, WHETHER IN AN ACTION IN
CONTRACT OR TORT, ARISING FROM YOUR ACCESS TO, OR USE OF, THE SOFTWARE.

SOME JURISDICTIONS DO NOT ALLOW THE LIMITATION OR EXCLUSION OF LIABILITY.
ACCORDINGLY, SOME OF THE ABOVE LIMITATIONS MAY NOT APPLY TO YOU.
复制