Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Locking tables in Oracle

RE: Locking tables in Oracle

From: Gogala, Mladen <MGogala_at_oxhp.com>
Date: Thu, 27 Mar 2003 14:08:50 -0800
Message-ID: <F001.00574CD3.20030327140850@fatcity.com>


Dynamic Performance (V$) Views, 95 of 237


BM_1093896
V$LOCK BM_1093897This view lists the locks currently held by the Oracle server and outstanding requests for a lock or latch.

BM_1093987BM_1093900Column	 BM_1093902Datatype
BM_1093904Description	
BM_1093906

ADDR BM_1093908RAW(4) BM_1093910Address of lock state object

BM_1093912
KADDR BM_1093914RAW(4) BM_1093916Address of lock

BM_1093918
SID BM_1093920NUMBER BM_1093922Identifier for session holding or acquiring the lock

BM_1093924
TYPE BM_1093926VARCHAR2(2) BM_1093928Type of user or system lock BM_1093929

The locks on the user types are obtained by user applications. Any process that is blocking others is likely to be holding one of these locks. The user type locks are: BM_1093930

TM - DML enqueue BM_1093931

TX - Transaction enqueue BM_1093932

UL - User supplied BM_1093933

The locks on the system types are held for extremely short periods of time. The system type locks are listed in Table <http://oxlink/IS/Technical_Documentation/oracle/Oracle9i_doc/DOC/server.920 /a96536/ch395.htm#1093994> 3-1.

BM_1093938
ID1

BM_1093940NUMBER BM_1093942Lock identifier #1 (depends on type)

BM_1093944
ID2

BM_1093946NUMBER BM_1093948Lock identifier #2 (depends on type)

BM_1093950
LMODE BM_1093952NUMBER BM_1093954Lock mode in which the session holds the lock:

BM_1093963
REQUEST BM_1093965NUMBER BM_1093967Lock mode in which the process requests the lock:

BM_1093976
CTIME BM_1093978NUMBER BM_1093980Time since current mode was granted

BM_1093982
BLOCK BM_1093984NUMBER BM_1093986The lock is blocking another lock

BM_1094169
BM_1094169BM_1093994Table 3-1 Values for the TYPE Column: System Types

BM_1094002System Type	 BM_1094004Description	 BM_1094006System Type
BM_1094008Description	
BM_1094010

BL

BM_1094012Buffer hash table instance

BM_1094014NA..NZ

BM_1094016Library cache pin instance (A..Z = namespace)

BM_1094018
CF

BM_1094020Control file schema global enqueue

BM_1094022PF

BM_1094024Password File

BM_1094026
CI

BM_1094028Cross-instance function invocation instance

BM_1094030PI, PS

BM_1094032Parallel operation

BM_1094034
CU

BM_1094036Cursor bind

BM_1094038PR

BM_1094040Process startup

BM_1094042
DF

BM_1094044Data file instance

BM_1094046QA..QZ

BM_1094048Row cache instance (A..Z = cache)

BM_1094050
DL

BM_1094052Direct loader parallel index create

BM_1094054RT

BM_1094056Redo thread global enqueue

BM_1094058
DM

BM_1094060Mount/startup db primary/secondary instance

BM_1094062SC

BM_1094064System commit number instance

BM_1094066
DR

BM_1094068Distributed recovery process

BM_1094070SM

BM_1094072SMON BM_1094074
DX

BM_1094076Distributed transaction entry

BM_1094078SN

BM_1094080Sequence number instance

BM_1094082
FS

BM_1094084File set

BM_1094086SQ

BM_1094088Sequence number enqueue

BM_1094090
HW

BM_1094092Space management operations on a specific segment

BM_1094094SS

BM_1094096Sort segment

BM_1094098
IN

BM_1094100Instance number

BM_1094102ST

BM_1094104Space transaction enqueue

BM_1094106
IR

BM_1094108Instance recovery serialization global enqueue

BM_1094110SV

BM_1094112Sequence number value

BM_1094114
IS

BM_1094116Instance state

BM_1094118TA

BM_1094120Generic enqueue

BM_1094122
IV

BM_1094124Library cache invalidation instance

BM_1094126TS

BM_1094128Temporary segment enqueue (ID2=0)

BM_1094130
JQ

BM_1094132Job queue

BM_1094134TS

BM_1094136New block allocation enqueue (ID2=1)

BM_1094138
KK

BM_1094140Thread kick

BM_1094142TT

BM_1094144Temporary table enqueue

BM_1094146
LA .. LP

BM_1094148Library cache lock instance lock (A..P = namespace)

BM_1094150UN

BM_1094152User name

BM_1094154
MM

BM_1094156Mount definition global enqueue

BM_1094158US

BM_1094160Undo segment DDL

BM_1094162
MR

BM_1094164Media recovery

BM_1094166WL

BM_1094168Being-written redo log instance

-----Original Message-----
Sent: Thursday, March 27, 2003 4:29 PM
To: Multiple recipients of list ORACLE-L

Mladen,

    What does it mean if locked_mode column is populated with 0 or 3 or 6. What kind of locking does it shows (RS, X etc...)?  

Thanks a lot,
Rajesh

-----Original Message-----
Sent: Thursday, March 27, 2003 1:09 PM
To: Multiple recipients of list ORACLE-L

V$LOCKED_OBJECT BM_1094230

This view lists all locks acquired by every transaction on the system.

BM_1094292

BM_1094233Column BM_1094235Datatype BM_1094237Description BM_1094239
XIDUSN BM_1094241NUMBER BM_1094243Undo segment number

BM_1094245
XIDSLOT BM_1094247NUMBER BM_1094249Slot number

BM_1094251
XIDSQN BM_1094253NUMBER BM_1094255Sequence number

BM_1094257
OBJECT_ID BM_1094259NUMBER BM_1094261Object ID being locked

BM_1094263
SESSION_ID BM_1094265NUMBER BM_1094267Session ID

BM_1094269
ORACLE_USERNAME BM_1094271VARCHAR2(30) BM_1094273Oracle user name

BM_1094275
OS_USER_NAME BM_1094277VARCHAR2(15) BM_1094279OS user name

BM_1094281
PROCESS BM_1094283VARCHAR2(9) BM_1094285OS process ID

BM_1094287
LOCKED_MODE BM_1094289NUMBER BM_1094291Lock mode

-----Original Message-----
<mailto:Rajesh.Pillai_at_nordstrom.com> ]
Sent: Thursday, March 27, 2003 3:14 PM
To: Multiple recipients of list ORACLE-L

Hi All,
  Does anybody has any insight of dynamic view v$locked_object? It populates its locked_mode column with numbers what does those numbers mean?

TIA,
Rajesh

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
<http://www.orafaq.net> 
--
Author: Pillai, Rajesh
  INET: Rajesh.Pillai_at_nordstrom.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
<http://www.fatcity.com> 
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Gogala, Mladen
  INET: MGogala_at_oxhp.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Thu Mar 27 2003 - 16:08:50 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US