Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 Oracle PL/SQL: Show all User Locks Jump to:  
Category: >> Oracle PL/SQL >> Show all User Locks Bookmark and Share

<< lastnext >>

Snippet Name: Show all User Locks

Description: Does just what it says: shows all user locks on the system.

Also see:
» Add PSOUG Search to SQL Developer
» Converting Rows to Columns
» Database Links: CURRENT_USER
» Instant Test Database with DCBA
» Show info on current context
» Lookup Oracle error messages
» Display and release DBMS_LOCK locks
» Display locks and latches
» Show rollback segment stats
» Show active transactions
» List supported INIT.ORA parameters
» Display database SGA statistics
» Measure the Buffer Cache Hit Ratio
» List security related profile informat...
» Find users with deadly privileges
» Audit User Logins (User Login Trigger)
» Block TOAD and other tools
» Kill Session
» Extents
» DBA Users
» DBA Tablespaces
» DBA triggers
» DBA Sessions
» DBA Roles
» DBA Objects
» DBA Links
» DBA Jobs
» Job Queue
» DBA Free Space
» Data Files

Comment: (none)

Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: February 27th, 2009

SET trimspool ON 
ttitle off
SET linesize 155
SET pagesize 60
column osuser heading 'OS|Username' format a7 truncate
column process heading 'OS|Process' format a7 truncate
column machine heading 'OS|Machine' format a10 truncate
column program heading 'OS|Program' format a25 truncate
column object heading 'Database|Object' format a25 truncate
column lock_type heading 'Lock|Type' format a4 truncate
column mode_held heading 'Mode|Held' format a15 truncate
column mode_requested heading 'Mode|Requested' format a10 truncate
column sid heading 'SID' format 999
column username heading 'Oracle|Username' format a7 truncate
column image heading 'Active Image' format a20 truncate
column sid format 99999
col waiting_session head 'WAITER' format 9999
col holding_session head 'BLOCKER' format 9999
 
SELECT /*+ ordered */
     --b.kaddr,
     c.sid,
     lock_waiter.waiting_session,
     lock_blocker.holding_session,
     c.program,
     c.osuser,
     c.machine,
     c.process,
     DECODE(u.name,
          NULL,'',
          u.name||'.'||o.name
     ) object,
     c.username,
     DECODE
     (
          b.TYPE,
          'BL', 'Buffer hash table instance lock',
          'CF', 'Control file schema global enqueue lock',
          'CI', 'Cross-instance function invocation instance lock',
          'CU', 'Cursor bind lock',
          'DF', 'Data file instance lock',
          'DL', 'direct loader parallel index create lock',
          'DM', 'Mount/startup db primary/secondary instance lock',
          'DR', 'Distributed recovery process lock',
          'DX', 'Distributed transaction entry lock',
          'FS', 'File set lock',
          'IN', 'Instance number lock',
          'IR', 'Instance recovery serialization global enqueue lock',
          'IS', 'Instance state lock',
          'IV', 'Library cache invalidation instance lock',
          'JQ', 'Job queue lock',
          'KK', 'Thread kick lock',
          'LA','Library cache lock instance lock (A..P=namespace);',
          'LB','Library cache lock instance lock (A..P=namespace);',
          'LC','Library cache lock instance lock (A..P=namespace);',
          'LD','Library cache lock instance lock (A..P=namespace);',
          'LE','Library cache lock instance lock (A..P=namespace);',
          'LF','Library cache lock instance lock (A..P=namespace);',
          'LG','Library cache lock instance lock (A..P=namespace);',
          'LH','Library cache lock instance lock (A..P=namespace);',
          'LI','Library cache lock instance lock (A..P=namespace);',
          'LJ','Library cache lock instance lock (A..P=namespace);',
          'LK','Library cache lock instance lock (A..P=namespace);',
          'LL','Library cache lock instance lock (A..P=namespace);',
          'LM','Library cache lock instance lock (A..P=namespace);',
          'LN','Library cache lock instance lock (A..P=namespace);',
          'LO','Library cache lock instance lock (A..P=namespace);',
          'LP','Library cache lock instance lock (A..P=namespace);',
          'MM', 'Mount definition global enqueue lock',
          'MR', 'Media recovery lock',
          'NA', 'Library cache pin instance lock (A..Z=namespace)',
          'NB', 'Library cache pin instance lock (A..Z=namespace)',
          'NC', 'Library cache pin instance lock (A..Z=namespace)',
          'ND', 'Library cache pin instance lock (A..Z=namespace)',
          'NE', 'Library cache pin instance lock (A..Z=namespace)',
          'NF', 'Library cache pin instance lock (A..Z=namespace)',
          'NG', 'Library cache pin instance lock (A..Z=namespace)',
          'NH', 'Library cache pin instance lock (A..Z=namespace)',
          'NI', 'Library cache pin instance lock (A..Z=namespace)',
          'NJ', 'Library cache pin instance lock (A..Z=namespace)',
          'NK', 'Library cache pin instance lock (A..Z=namespace)',
          'NL', 'Library cache pin instance lock (A..Z=namespace)',
          'NM', 'Library cache pin instance lock (A..Z=namespace)',
          'NN', 'Library cache pin instance lock (A..Z=namespace)',
          'NO', 'Library cache pin instance lock (A..Z=namespace)',
          'NP', 'Library cache pin instance lock (A..Z=namespace)',
          'NQ', 'Library cache pin instance lock (A..Z=namespace)',
          'NR', 'Library cache pin instance lock (A..Z=namespace)',
          'NS', 'Library cache pin instance lock (A..Z=namespace)',
          'NT', 'Library cache pin instance lock (A..Z=namespace)',
          'NU', 'Library cache pin instance lock (A..Z=namespace)',
          'NV', 'Library cache pin instance lock (A..Z=namespace)',
          'NW', 'Library cache pin instance lock (A..Z=namespace)',
          'NX', 'Library cache pin instance lock (A..Z=namespace)',
          'NY', 'Library cache pin instance lock (A..Z=namespace)',
          'NZ', 'Library cache pin instance lock (A..Z=namespace)',
          'PF', 'Password File lock',
          'PI', 'Parallel operation locks',
          'PS', 'Parallel operation locks',
          'PR', 'Process startup lock',
          'QA','Row cache instance lock (A..Z=cache)',
          'QB','Row cache instance lock (A..Z=cache)',
          'QC','Row cache instance lock (A..Z=cache)',
          'QD','Row cache instance lock (A..Z=cache)',
          'QE','Row cache instance lock (A..Z=cache)',
          'QF','Row cache instance lock (A..Z=cache)',
          'QG','Row cache instance lock (A..Z=cache)',
          'QH','Row cache instance lock (A..Z=cache)',
          'QI','Row cache instance lock (A..Z=cache)',
          'QJ','Row cache instance lock (A..Z=cache)',
          'QK','Row cache instance lock (A..Z=cache)',
          'QL','Row cache instance lock (A..Z=cache)',
          'QM','Row cache instance lock (A..Z=cache)',
          'QN','Row cache instance lock (A..Z=cache)',
          'QP','Row cache instance lock (A..Z=cache)',
          'QQ','Row cache instance lock (A..Z=cache)',
          'QR','Row cache instance lock (A..Z=cache)',
          'QS','Row cache instance lock (A..Z=cache)',
          'QT','Row cache instance lock (A..Z=cache)',
          'QU','Row cache instance lock (A..Z=cache)',
          'QV','Row cache instance lock (A..Z=cache)',
          'QW','Row cache instance lock (A..Z=cache)',
          'QX','Row cache instance lock (A..Z=cache)',
          'QY','Row cache instance lock (A..Z=cache)',
          'QZ','Row cache instance lock (A..Z=cache)',
          'RT', 'Redo thread global enqueue lock',
          'SC', 'System commit number instance lock',
          'SM', 'SMON lock',
          'SN', 'Sequence number instance lock',
          'SQ', 'Sequence number enqueue lock',
          'SS', 'Sort segment locks',
          'ST', 'Space transaction enqueue lock',
          'SV', 'Sequence number value lock',
          'TA', 'Generic enqueue lock',
          'TS', 'Temporary segment enqueue lock (ID2=0)',
          'TS', 'New block allocation enqueue lock (ID2=1)',
          'TT', 'Temporary table enqueue lock',
          'UN', 'User name lock',
          'US', 'Undo segment DDL lock',
          'WL', 'Being-written redo log instance lock',
          b.TYPE
     ) lock_type,
     DECODE
     (
          b.lmode,
          0, 'None',           /* Mon Lock equivalent */
          1, 'Null',           /* N */
          2, 'Row-S (SS)',     /* L */
          3, 'Row-X (SX)',     /* R */
          4, 'Share',          /* S */
          5, 'S/Row-X (SRX)',  /* C */
          6, 'Exclusive',      /* X */
          TO_CHAR(b.lmode)
     ) mode_held,
     DECODE
     (
          b.request,
          0, 'None',           /* Mon Lock equivalent */
          1, 'Null',           /* N */
          2, 'Row-S (SS)',     /* L */
          3, 'Row-X (SX)',     /* R */
          4, 'Share',          /* S */
          5, 'S/Row-X (SSX)',  /* C */
          6, 'Exclusive',      /* X */
          TO_CHAR(b.request)
     ) mode_requested
FROM
     v$lock b
     ,v$session c
     ,sys.USER$ u
     ,sys.obj$ o
     ,( SELECT * FROM sys.dba_waiters) lock_blocker
     ,( SELECT * FROM sys.dba_waiters) lock_waiter
WHERE
b.sid = c.sid
AND u.USER# = c.USER#
AND o.obj#(+) = b.id1
AND lock_blocker.waiting_session(+) = c.sid
AND lock_waiter.holding_session(+) = c.sid
AND c.username != 'SYS'
ORDER BY kaddr, lockwait
 


Free
Oracle Magazine
Subscriptions
and Oracle White Papers


SQL University.net courses meet the most demanding needs of the business world for advanced education in a cost-effective manner. SQL University.net courses are available immediately for IT professionals and can be taken without disruption of your workplace schedule or processes.

Compared to traditional travel-based training, SQL University.net saves time and valuable corporate resources, allowing companies to do more with less. That's our mission, and that's what we deliver.

Click here to find out more
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 150 users online    © 2009 psoug.org

PSOUG LOGIN
Username: 
Password: 
Forgot your password?