ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【Oracle】Lock 脚本

【Oracle】Lock 脚本

原创 Linux操作系统 作者:果酱o 时间:2012-07-23 15:20:45 0 删除 编辑
  • 那些用户拿到了锁。
select l.ADDR,s.USER#,s.USERNAME,s.OSUSER,s.MACHINE,l.SID,l.TYPE,l.ID1,l.ID2,l.CTIME, s.TERMINAL
from v$lock l , v$session s
where l.SID=s.SID and
username is not null and
l.TYPE in ('TM','TX','IS','IX','SIX')
order by s.TERMINAL,l.CTIME ;

ADDR USER# USERNAME OSUSER MACHINE SID TY ID1 ID2
----------------- ----- ---------- ------------------ ------------ ----- --- ------ ------

0000000310B6E740 100 ERPADMIN SRBJDB02\ivy.dong SID\SRBJDB02 133 TX 458772 8995
000000001E26D318 100 ERPADMIN SRBJDB02\ivy.dong SID\SRBJDB02 133 TM 5003 0

CTIME TERMINAL
------- --------
533339 SRBJDB02
540186 SRBJDB02

--------------------------------------------------------------------------------------------------
  • 被锁的对象有哪些。
select ao.OWNER , ao.OBJECT_NAME , ao.OBJECT_ID , ao.OBJECT_TYPE , lo.OS_USER_NAME , lo.SESSION_ID , lo.LOCKED_MODE , lo.XIDUSN , lo.XIDSLOT , lo.XIDSQN , ao.LAST_DDL_TIME
from v$locked_object lo, all_objects ao
where lo.OBJECT_ID = ao.OBJECT_ID


OWNER OBJECT_NAME OBJECT_ID OBJECT_TYPE OS_USER_NAME SESSION_ID LOCKED_MODE
------ ----------- --------- ----------- ------------------ ---------- -----------
SYS PLAN_TABLE$ 5003 TABLE SRBJDB02\ivy.dong 133 3

XIDUSN XIDSLOT DSQN LAST_DDL_TIME
------ ------- ------- ------------------
7 20 8995 2010-3-30 10:09:45


--------------------------------------------------------------------------------------------------

  • 有哪些用户因为锁而发生了等待。
SELECT
  SUBSTR(s1.username,1,12) "WAITING USER"
  , SUBSTR(s1.osuser,1,8) "OS User"
  , SUBSTR(TO_CHAR(w.session_id),1,5) "Sid"
  , p1.spid "PID"
  , SUBSTR(s2.username,1,12) "HOLDING User"
  , SUBSTR(s2.osuser,1,8) "OS User"
  , SUBSTR(TO_CHAR(h.session_id),1,5) "Sid"
  , p2.spid "PID"
FROM
  sys.v_$process p1
  , sys.v_$process p2
  , sys.v_$session s1
  , sys.v_$session s2
  , dba_locks w
  , dba_locks h
WHERE
  h.mode_held != 'None'
  AND h.mode_held != 'Null'
  AND w.mode_requested != 'None'
  AND w.lock_type (+) = h.lock_type
  AND w.lock_id1 (+) = h.lock_id1
  AND w.lock_id2 (+) = h.lock_id2
  AND w.session_id = s1.sid (+)
  AND h.session_id = s2.sid (+)
  AND s1.paddr = p1.addr (+)
  AND s2.paddr = p2.addr (+)


AITING USER OS User Sid PID
------------------------ ---------------- ---------- -------
HOLDING User OS User Sid PID
------------------------ ---------------- ---------- -------
SCOTT lvy.dong 131 3472
SCOTT SRBJDB02 133 3880


--------------------------------------------------------------------------------------------------

  • Oracle--v$lock type字段详解
Name Description
AD ASM Disk AU Lock
AF Advisor Framework
AG Analytic Workspace Generation
AK GES Deadlock Test
AO MultiWriter Object Access
AS Service Operations
AT Alter Tablespace
AW Analytic Workspace
BR Backup/Restore
CF Controlfile Transaction
CI Cross-Instance Call Invocation
CL Label Security cache
CM ASM Instance Enqueue
CT Block Change Tracking
CU Cursor
DB DbsDriver
DD ASM Local Disk Group
DF Datafile Online in RAC
DG ASM Disk Group Modification
DI GES Internal
DL Direct Loader Index Creation
DM Database Mount/Open
DN Diskgroup number generator
DP LDAP Parameter
DQ ASM RBAL doorbell
DR Distributed Recovery
DS Database Suspend
DT Default Temporary Tablespace
DV Diana Versioning
DX Distributed Transaction
E Library Cache Lock 2
FA ASM File Access Lock
FB Format Block
FC Disk Group Chunk Mount
FD Flashback Database
FG ACD Relocation Gate Enqueue
FL Flashback database log
FM File Mapping
FR Disk Group Recovery
FS File Set / Dictionary Check
FT Disk Group Redo Generation
FU DBFUS
G Library Cache Pin 2
HD ASM Disk Header
HP Queue Page
HQ Hash Queue
HV Direct Loader High Water Mark
HW Segment High Water Mark
IA Internal
ID NID
IL Label Security
IM Kti blr lock
IR Instance Recovery
IS Instance State
IT In-Mem Temp Table Meta. Creation
IV Library Cache Invalidation
IZ INSTANCE LOCK
JD Job Queue Date
JI Materialized View
JQ Job Queue
JS Job Scheduler
KK Kick Instance to Switch Logs
KM Scheduler
KP Kupp Process Startup
KT Scheduler Plan
L Library Cache Lock 1
MD Materialized View Log DDL
MH AQ Notification Mail Host
ML AQ Notification Mail Port
MN LogMiner
MR Media Recovery
MS Materialized View Refresh Log
MW MWIN Schedule
N Library Cache Pin 1
OC Outline Cache
OL Outline Name
OQ OLAPI Histories
PD Property Lock
PE Parameter
PF Password File
PG Global Parameter
PH AQ Notification Proxy
PI Remote PX Process Spawn Status
PL Transportable Tablespace
PM ASM PST Signalling
PR Process Startup
PS PX Process Reservation
PT ASM Partnership and Status Table
PV KSV slave startup
PW Buffer Cache PreWarm
Q Row Cache
RB ASM Rollback Recovery
RF Data Guard Broker
RM GES Resource Remastering
RN Redo Log Nab Computation
RO Multiple Object Reuse
RP Resilver / Repair
RS Reclaimable Space
RT Redo Thread
SB LogicalStandby
SC System Change Number
SF AQ Notification Sender
SH Active Session History Flushing
SI Streams Table Instantiation
SK Shrink Segment
SM SMON Serialization
SQ Sequence Cache
SR Synchronized Replication
SS Sort Segment
ST Space Transaction
SU SaveUndo Segment
SV Sequence Ordering
SW Suspend Writes
TA Instance Undo
TB SQL Tuning Base Existence Cache
TC Tablespace Checkpoint
TD KTF map table enqueue
TE KTF broadcast
TF Temporary File
TL Log Lock
TM DML
TO Temp Object
TQ Queue table enqueue
TS Temporary Segment
TT Tablespace
TW Cross-Instance Transaction
TX Transaction
UL User-defined
UN User Name
US Undo Segment
WA AQ Notification Watermark
WF AWR Flush
WL Being Written Redo Log
WP AWR Purge
WS LogWriter Standby
XH AQ Notification No-Proxy
XR Quiesce / Force Logging
XY Internal Test

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/13304800/viewspace-736394/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-09-14

  • 博文量
    14
  • 访问量
    28092