{"id":2688,"date":"2018-02-02T21:47:57","date_gmt":"2018-02-02T12:47:57","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=2688"},"modified":"2018-02-02T21:53:44","modified_gmt":"2018-02-02T12:53:44","slug":"lock-%eb%ac%b8%ec%a0%9c-%ed%99%95%ec%9d%b8%ea%b3%bc-%ed%95%b4%ea%b2%b0-%eb%b0%a9%eb%b2%95","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=2688","title":{"rendered":"LOCK \ubb38\uc81c \ud655\uc778\uacfc \ud574\uacb0 \ubc29\ubc95"},"content":{"rendered":"
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n

– \uc751\uc6a9\ud504\ub85c\uadf8\ub7a8\ub4e4\uc758 \uae30\ub2a5\uc774 \ubcf5\uc7a1\ud574\uc838\uac00\uba74\uc11c Lock \ubb38\uc81c\uac00 \ube48\ubc88\ud558\uac8c \ubc1c\uc0dd\ud568\uc5d0 \ub530\ub77c \uc774\ub97c \ud574\uacb0\ud558\ub294 \uac83\uc774 \ub9e4\uc6b0 \uc911\uc694\ud55c \uc77c\uc774 \ub418\uc5c8\ub2e4. \ub3c4\ub300\uccb4 \uc5b4\ub5a0\ud55c \ud504\ub85c\uadf8\ub7a8\uc774 Lock\uc744 \ubc1c\uc0dd\uc2dc\ucf1c \ub370\uc774\ud0c0\ubca0\uc774\uc2a4\ub97c Hangup \uc0c1\ud0dc\uc5d0 \uc774\ub974\uac8c \ud558\ub294\uac00\ub97c \uc54c\uc544\ub0b4\ub294 \uac83\uc740 \uc26c\uc6b4 \uc77c\uc774 \uc544\ub2c8\uc9c0\ub9cc \uc9c0\uae08\ubd80\ud130 \uc774 \ubb38\uc81c\ub97c \ud574\uacb0\ud558\ub294 \ubc29\ubc95\uc5d0 \ub300\ud558\uc5ec \uc54c\uc544\ubcf4\ub3c4\ub85d \ud55c\ub2e4.<\/p>\n

SQLDBA MONITOR SCREENS<\/p>\n

———————-<\/p>\n

$ TERM=vt100;export TERM<\/p>\n

$ sqldba<\/p>\n

[ connect internal ] -> [ key-0(ins key) ]<\/p>\n

[ MONITOR SESSION ]<\/p>\n

=========================================================================<\/p>\n

Session Serial Process Status Username Lock Current<\/p>\n

ID Number ID Waited Statement<\/p>\n

————————————————————————-<\/p>\n

6 35 28 ACTIVE LTO2 C2D2B3B4 UPDATE<\/p>\n

8 70 19 INACTIVE SYSTEM SELECT<\/p>\n

12 15 25 INACTIVE LTO INSERT<\/p>\n

14 17 27 ACTIVE LTO3 C2D2B438 DELETE<\/p>\n

15 30 26 ACTIVE SYS UNKNOWN<\/p>\n

=========================================================================<\/p>\n

– Session Id : SID – Session Identifier<\/p>\n

– Serial Nr : Session Serial Nmber. Session \uc758 Object \ubcc4\ub85c \uc9c0\uc815\ub41c\ub2e4.<\/p>\n

– Process Id : V$PROCESS\uc5d0\uc11c PID(Oracle Process Identifier)<\/p>\n

– Status : Session\uc758 \uc0c1\ud0dc<\/p>\n

– Username : Oracle Username<\/p>\n

– Lock waited: \ub300\uae30\uc911\uc778 Lock Address<\/p>\n

\uc5ec\uae30\uc11c \uac00\uc7a5 \uc911\uc694\ud55c \uceec\ub7fc\uc740 Lock Waited\uc774\ub2e4. \uc5ec\uae30\uc5d0 \ubb34\uc5c7\uc778\uac00 \uae30\ub85d\uc774 \ub418\uc5b4<\/p>\n

\uc788\ub2e4\uba74 \uc774 \uc138\uc158\uc740 \ud2b9\uc815 \ub9ac\uc18c\uc2a4\ub97c \uc5bb\uae30 \uc704\ud558\uc5ec \ub300\uae30 \uc911\uc778 \uc0c1\ud0dc\uc784\uc744 \uc54c \uc218 \uc788\ub2e4.<\/p>\n

\uc704\uc758 \uc608\uc5d0\uc11c LT02,LT03\uc774 \uadf8 \uacbd\uc6b0\uc5d0 \ud574\ub2f9 \ub41c\ub2e4.<\/p>\n

[ MONITOR LOCK ]<\/p>\n

========================================================================<\/p>\n

Session Serial Lock Resource Resource Mode Mode<\/p>\n

Username ID Number Type ID 1 ID 2 Held Requested<\/p>\n

————————————————————————<\/p>\n

LTO2 6 35 TM 2294 0 RX NONE<\/p>\n

LTO2 6 35 TM 2295 0 RX NONE<\/p>\n

LTO2 6 35 TX 262167 87 NONE X<\/p>\n

LTO2 6 35 TX 327682 90 X NONE<\/p>\n

LTO2 12 15 TM 2294 0 RX NONE<\/p>\n

LTO 12 15 TM 2295 0 RX NONE<\/p>\n

LTO 12 15 TX 262167 87 X NONE<\/p>\n

LTO3 14 17 TM 2294 0 RX NONE<\/p>\n

LTO3 14 17 TM 2295 0 RX NONE<\/p>\n

LTO3 14 17 TX 262167 87 NONE X<\/p>\n

LTO3 14 17 TX 196636 87 X NONE<\/p>\n

========================================================================<\/p>\n

– Username : Oracle Username.<\/p>\n

– Session Id : SID – Session Identifier.<\/p>\n

– Serial Nr : \uc55e\uc758 \uc124\uba85 \ucc38\uc870<\/p>\n

===============+==========================+==============================<\/p>\n

Type of Lock | Resource ID1 | Resource ID 2<\/p>\n

—————+————————–+——————————<\/p>\n

TX(Transaction)| \ub864\ubc31 \uc138\uadf8\uba3c\ud2b8 \ubc88\ud638\uc640 \uc2ac\ub86f| Wrap Number\ub97c 10\uc9c4\uc218\ub85c \ubcc0\ud658\ud55c<\/p>\n

| \ubc88\ud638\ub97c 10\uc9c4\uc218\ub85c \ubcc0\ud658\ud55c \uac12| \uac12(\ub864\ubc31\uc2ac\ub86f\uc774 \uc7ac\uc0ac\uc6a9\ub41c \ud69f\uc218)<\/p>\n

—————+————————–+——————————<\/p>\n

TM(Table Locks)| \uc218\uc815\ub41c \ud14c\uc774\ube14\uc758 Object ID| \ud56d\uc0c1 0<\/p>\n

—————+————————–+——————————<\/p>\n

RW(Row Wait) | File #, Block #\ub97c 10\uc9c4\uc218 | \ube14\ub7ed\ub0b4\uc758 Row\ub97c 10 \uc218\ub85c \ubcc0\ud658<\/p>\n

| \ub85c \ubcc0\ud658\ud55c \uac12 (V6) | \ud55c \uac12<\/p>\n

—————+————————–+——————————<\/p>\n

UL(User Defined| Oracle7 Concepts Manual Ch10, Oracle7 Admin Guide<\/p>\n

Locks) | Appendix B 81 \uc758 \ub9ac\uc2a4\ud2b8 \ucc38\uc870<\/p>\n

—————+———————————————————<\/p>\n

Mode Held | \uac78\uace0 \uc788\ub294 Lock Mode<\/p>\n

—————+———————————————————<\/p>\n

Mode Requested | \uc694\uad6c\ud55c Lock Mode<\/p>\n

=========================================================================<\/p>\n

* \ub2e4\uc74c \uc0ac\uc6a9\uc790\ub4e4\uc740 Lock\uc744 \uae30\ub2e4\ub9ac\uace0 \uc788\ub294 \uc0c1\ud0dc\uc774\ub2e4.<\/p>\n

LTO2 6 35 TX 262167 87 NONE X<\/p>\n

LTO3 4 17 TX 262167 87 NONE X<\/p>\n

* \uc774\ub4e4\uc774 \uae30\ub2e4\ub9ac\uace0 \uc788\ub294 Lock\uc740 \ub2e4\uc74c\uacfc \uac19\ub2e4.<\/p>\n

LTO 12 15 TM 2294 0 RX NONE<\/p>\n

LTO 12 15 TM 2295 0 RX NONE<\/p>\n

LTO 12 15 TX 262167 87 X NONE<\/p>\n

TX Lock\uc758 \uacbd\uc6b0, \ub3d9\uc77c\ud55c \ub9ac\uc18c\uc2a4 \ub300\ud55c Lock\uc744 \uc694\uad6c\ud558\ub294 \uacbd\uc6b0 \uac01\uac01\uc758 ID1,ID2\ub294 \ub3d9\uc77c\ud55c \uac12\uc744 \uac16\ub294\ub2e4. \ud558\ub098\uc758 \ub370\uc774\ud0c0 \ube14\ub7ed \ub0b4\uc5d0\ub294 \uadf8 \ube14\ub7ed\uc744 \uc0ac\uc6a9\ud558\uace0 \uc788\ub294 \ud2b8\ub79c\uc7ad\uc158\uc5d0 \ub300\ud55c\uc815\ubcf4\uac00 \ub4e4\uc5b4\uc788\ub294\ub370 \ub9cc\uc57d \uc774 \ud2b8\ub79c\uc7ad\uc158\uc774 Commit, Rollback \ub418\uc9c0 \uc54a\uc73c\uba74 \ub2e4\ub978 \ud2b8\ub79c\uc7ad\uc158\ub4e4\uc740 \uadf8 \ud2b8\ub79c\uc7ad\uc158\uc774 \uc7a1\uace0 \uc788\ub294 \ub9ac\uc18c\uc2a4\uc758 Lock\uc774 \ud574\uc81c \ub418\uae30\ub97c \uae30\ub2e4\ub9ac\uac8c \ub41c\ub2e4.<\/p>\n

\ud558\ub098\uc758 \ud2b8\ub79c\uc7ad\uc158\uc774 \uc5ec\ub7ec\uac1c\uc758 \ud14c\uc774\ube14\uc5d0 \ub300\ud574\uc11c \uc791\uc5c5\uc744 \ud558\uace0 \uc788\ub294 \uacbd\uc6b0\uc5d0\ub294 Lock\uacfc \uc5f0\uad00\ub41c \ub9ac\uc18c\uc2a4\uac00 \uc5b4\ub5a4 \uac83\uc778\uc9c0\ub97c \uc27d\uac8c \ud30c\uc545\ud558\uae30\uac00 \ud798\ub4e4\ub2e4. \uc774 \ub54c\uc5d0\ub294 Monitor Session\uacfc Monitor Table\uc744 \ubaa8\ub450 \ubcf4\uc544\uc57c\uc9c0\ub9cc \ud655\uc2e4\ud558\uac8c \uc54c \uc218 \uc788\ub2e4.<\/p>\n

[ MONITOR SESSION ]<\/p>\n

=========================================================================<\/p>\n

Session Serial Process Lock Current<\/p>\n

ID Number ID Status Username Waited Statement<\/p>\n

————————————————————————-<\/p>\n

5 31 19 INACTIVE LTO INSERT<\/p>\n

6 43 25 ACTIVE LTO2 C3D320F4 UPDATE<\/p>\n

9 1 26 ACTIVE LTO3 C3D320C8 DELETE<\/p>\n

=========================================================================<\/p>\n

[ MONITOR TABLE ]<\/p>\n

=========================================================================<\/p>\n

Session ID Schema Name Table Name<\/p>\n

————————————————————————-<\/p>\n

5 LTO DEPT<\/p>\n

6 LTO EMP<\/p>\n

9 LTO EMP<\/p>\n

————————————————————————-<\/p>\n

* \uc704\uc758 \uacbd\uc6b0\ub97c \ubcf4\uba74 Session 6\uc640 Session 9\uac00 LTO.EMP\uc744 \uc0ac\uc6a9\ud558\ub824\uace0 \uae30\ub2e4\ub9ac\uace0 \uc788\uc74c\uc744 \uc54c \uc218 \uc788\ub2e4.<\/p>\n

 <\/p>\n

\u25a0 \ud574\uacb0\ubc29\ubc95<\/p>\n

\ub300\ubd80\ubd84\uc758 Lock \ubb38\uc81c\ub294 \uc751\uc6a9\ud504\ub85c\uadf8\ub7a8\uc758 \uc798\ubabb\ub41c \uc124\uacc4 \ub54c\ubb38\uc5d0 \ubc1c\uc0dd\ud558\uac8c \ub41c\ub2e4. \uc774\ub97c \ud574\uacb0\ud558\uae30 \uc704\ud574\uc11c \ub2e4\uc74c\uacfc \uac19\uc740 \ubc29\ubc95\uc73c\ub85c Lock\uc744 Release \uc2dc\ucf1c\uc57c \ud55c\ub2e4.<\/p>\n

1. Lock Holder\uc5d0\uac8c Commit, Rollback\uc744 \uc694\uccad\ud55c\ub2e4.<\/p>\n

2. Lock Holder\uc758 \uc138\uc158\uc744 Kill \uc2dc\ud0a8\ub2e4.<\/p>\n

SQL>ALTER SYSTEM KILL SESSION ‘sid,serial#’;<\/p>\n

3. \uc720\ub2c9\uc2a4 Shadow Process(Server Process)\ub97c Kill \uc2dc\ud0a4\ub294 \uac83\uc740 \ubc14\ub78c\uc9c1\ud558\uc9c0<\/p>\n

\uc54a\ub2e4. \ud2b9\ud788 MTS \ud658\uacbd\ud558\uc5d0\uc11c\ub294 \uac01\ubcc4\ud55c \uc8fc\uc758\uac00 \ud544\uc694\ud558\ub2e4.<\/p>\n

4. 2PC Pending \ud2b8\ub79c\uc7ad\uc158\uc778 \uacbd\uc6b0\ub294 Rollback Force, Commit Force \ub85c \ud574\uacb0 \ud55c\ub2e4.<\/p>\n

 <\/p>\n

\u25a0 LOCK \ubb38\uc81c\ub97c \uc77c\uc73c\ud0a4\ub294 SQL \uba85\ub839 \ucc3e\uae30<\/p>\n

* \ub2e4\uc74c Query\ub294 Lock\uacfc \uad00\ub828\ub41c \ud2b8\ub79c\uc7ad\uc158\uc744 \ucd9c\ub825\ud574\uc900\ub2e4.<\/p>\n

column username format a10<\/p>\n

column sid format 999<\/p>\n

column lock_type format a15<\/p>\n

column MODE_HELD format a11<\/p>\n

column MODE_REQUESTED format a10<\/p>\n

column LOCK_ID1 format a8<\/p>\n

column LOCK_ID2 format a8<\/p>\n

select a.sid,<\/p>\n

decode(a.type,<\/p>\n

‘MR’, ‘Media Recovery’,<\/p>\n

‘RT’, ‘Redo Thread’,<\/p>\n

‘UN’, ‘User Name’,<\/p>\n

‘TX’, ‘Transaction’,<\/p>\n

‘TM’, ‘DML’,<\/p>\n

‘UL’, ‘PL\/SQL User Lock’,<\/p>\n

‘DX’, ‘Distributed Xaction’,<\/p>\n

‘CF’, ‘Control File’,<\/p>\n

‘IS’, ‘Instance State’,<\/p>\n

‘FS’, ‘File Set’,<\/p>\n

‘IR’, ‘Instance Recovery’,<\/p>\n

‘ST’, ‘Disk Space Transaction’,<\/p>\n

‘IR’, ‘Instance Recovery’,<\/p>\n

‘ST’, ‘Disk Space Transaction’,<\/p>\n

‘TS’, ‘Temp Segment’,<\/p>\n

‘IV’, ‘Library Cache Invalidation’,<\/p>\n

‘LS’, ‘Log Start or Switch’,<\/p>\n

‘RW’, ‘Row Wait’,<\/p>\n

‘SQ’, ‘Sequence Number’,<\/p>\n

‘TE’, ‘Extend Table’,<\/p>\n

‘TT’, ‘Temp Table’,<\/p>\n

a.type) lock_type,<\/p>\n

decode(a.lmode,<\/p>\n

0, ‘None’, \/* Mon Lock equivalent *\/<\/p>\n

1, ‘Null’, \/* N *\/<\/p>\n

2, ‘Row-S (SS)’, \/* L *\/<\/p>\n

3, ‘Row-X (SX)’, \/* R *\/<\/p>\n

3, ‘Row-X (SX)’, \/* R *\/<\/p>\n

4, ‘Share’, \/* S *\/<\/p>\n

5, ‘S\/Row-X (SSX)’, \/* C *\/<\/p>\n

6, ‘Exclusive’, \/* X *\/<\/p>\n

to_char(a.lmode)) mode_held,<\/p>\n

decode(a.request,<\/p>\n

0, ‘None’, \/* Mon Lock equivalent *\/<\/p>\n

1, ‘Null’, \/* N *\/<\/p>\n

2, ‘Row-S (SS)’, \/* L *\/<\/p>\n

3, ‘Row-X (SX)’, \/* R *\/<\/p>\n

4, ‘Share’, \/* S *\/<\/p>\n

5, ‘S\/Row-X (SSX)’, \/* C *\/<\/p>\n

6, ‘Exclusive’, \/* X *\/<\/p>\n

to_char(a.request)) mode_requested,<\/p>\n

to_char(a.id1) lock_id1, to_char(a.id2) lock_id2<\/p>\n

from v$lock a<\/p>\n

where (id1,id2) in<\/p>\n

(select b.id1, b.id2 from v$lock b where b.id1=a.id1 and<\/p>\n

b.id2=a.id2 and b.request>0)<\/p>\n

\/<\/p>\n

( \uc704\uc758 Query\ub97c \uc2e4\ud589\uc2dc\ud0a4\uba74 \ub2e4\uc74c\uacfc \uac19\uc740 \ub0b4\uc6a9\uc774 \ucd9c\ub825\ub41c\ub2e4.<\/p>\n

SID LOCK_TYPE MODE_HELD MODE_REQUE LOCK_ID1 LOCK_ID2<\/p>\n

— ————— ———- ———- ——– ——–<\/p>\n

5 Transaction Exclusive None 262172 90<\/p>\n

6 Transaction None Exclusive 262172 90<\/p>\n

9 Transaction None Exclusive 262172 90<\/p>\n

SID 6\uacfc 9\ub294 SID 5\uac00 \uac78\uace0 \uc788\ub294 Lock\uc774 \ud480\ub9ac\uae30\ub97c \uae30\ub2e4\ub9ac\uace0 \uc788\uc74c\uc744 \uc54c \uc218 \uc788\ub2e4.<\/p>\n

 <\/p>\n

* \ub2e4\uc74c Query\ub294 Lock\uacfc \uad00\ub828\ub41c \ud14c\uc774\ube14\uc744 \ucd9c\ub825\ud574 \uc900\ub2e4.<\/p>\n

column username format a10<\/p>\n

column lockwait format a10<\/p>\n

column sql_text format a80<\/p>\n

column object_owner format a14<\/p>\n

column object format a15<\/p>\n

select b.username username, c.sid sid, c.owner object_owner,<\/p>\n

c.object object, b.lockwait, a.sql_text SQL<\/p>\n

from v$sqltext a, v$session b, v$access c<\/p>\n

where a.address=b.sql_address and<\/p>\n

a.hash_value=b.sql_hash_value and<\/p>\n

b.sid = c.sid and c.owner != ‘SYS’<\/p>\n

\/<\/p>\n

( \uc704\uc758 Query\ub97c \uc2e4\ud589\ud558\uba74 \ub2e4\uc74c\uacfc \uac19\uc740 \uacb0\uacfc\uac00 \ucd9c\ub825\ub41c\ub2e4.<\/p>\n

USERNAME SID OBJECT_OWNER OBJECT LOCKWAIT<\/p>\n

————— — ————- ————– ———-<\/p>\n

SQL<\/p>\n

—————————————————————<\/p>\n

LTO2 6 LTO EMP C3D320F4<\/p>\n

update lto.emp set empno =25 where empno=7788<\/p>\n

LTO3 9 LTO EMP C3D320C8<\/p>\n

delete from lto.emp where empno=7788<\/p>\n

LTO 5 LTO DEPT<\/p>\n

insert into lto.dept values (60,’PROGRAMMER’,’LOS ANGELOS’)<\/p>\n

\uc5ec\uae30\uc11c\ub294 USERNAME\uc5d0 \ub098\uc640\uc788\ub294 \uc720\uc800\uac00 OBJECT\uc5d0 \ub098\uc640\uc788\ub294 \ud14c\uc774\ube14\uc744 \uc218\uc815\ud558\ub824\uace0 \ud568\uc744 \ub098\ud0c0\ub0b8\ub2e4. LT02,LT03\ub294 LT0\uac00 Commit,Rollback \ud558\uae30\ub97c \uae30\ub2e4\ub9ac\uace0 \uc788\uc74c\uc744 \uc54c \uc218 \uc788\ub2e4. \ud558\uc9c0\ub9cc \uc5ec\uae30\uc5d0\ub294 \uac00\uc7a5 \ucd5c\uadfc\uc758 DML \uba85\ub839 \ud558\ub098\ub9cc \ub098\uc640\uc788\uae30 \ub54c\ubb38\uc5d0 \uc5ec\uae30 \ub098\uc628 \uba85\ub839\uc774 \ubc18\ub4dc\uc2dc Lock\uc744 \uac78\uace0 \uc788\ub294 \uba85\ub839\uc774\ub77c\uace0 \ub2e8\uc815\uc9c0\uc744 \uc218\ub294 \uc5c6\ub2e4.<\/p>\n

 <\/p>\n

\u25a0 \uad00\ub828\ub41c \ud504\ub85c\uc138\uc2a4<\/p>\n

* \ub2e4\uc74c Query\ub97c \ud589\ud574 \ubcf4\uba74 \ud504\ub85c\uc138\uc2a4\uc640 \uad00\ub828\ub41c \uc815\ubcf4\ub97c \uc5bb\uc744 \uc218 \uc788\ub2e4.<\/p>\n

column “ORACLE USER” format a11<\/p>\n

column SERIAL# format 9999999<\/p>\n

column “OS USER” format a8<\/p>\n

select substr(s.username,1,11) “ORACLE USER”, p.pid “PROCESS ID”,<\/p>\n

s.sid “SESSION ID”, s.serial#, osuser “OS USER”,<\/p>\n

p.spid “PROC SPID”,s.process “SESS SPID”, s.lockwait “LOCK WAIT”<\/p>\n

from v$process p, v$session s, v$access a<\/p>\n

where a.sid=s.sid and<\/p>\n

p.addr=s.paddr and<\/p>\n

s.username != ‘SYS’<\/p>\n

\/<\/p>\n

* \uc704\uc758 Query\ub97c \uc2e4\ud589\ud558\uba74 \ub2e4\uc74c\uacfc \uac19\uc740 \uacb0\uacfc\uac00 \ucd9c\ub825\ub41c\ub2e4.<\/p>\n

ORACLE PROCESS SESSION SERIAL# OS USER PROC SESS LOCKWT<\/p>\n

USER ID ID SPID SPID<\/p>\n

—— ——- ——- ——- ——- —- —- ——<\/p>\n

LTO 19 5 31 usupport 17312 17309<\/p>\n

LTO2 25 6 43 usupport 17313 17310 C3D320F4<\/p>\n

LTO3 26 9 1 usupport 17314 17311 C3D320D8<\/p>\n

 <\/p>\n

\uc774 \ub54c\ub294 \ub2e4\uc74c\uacfc \uac19\uc774 \uc870\uce58\ud55c\ub2e4.<\/p>\n

1. LTO\uc5d0\uac8c Commit,Rollback \ud560 \uac83\uc744 \uc694\uad6c\ud55c\ub2e4.<\/p>\n

2. SQL>ALTER SYSTEM KILL SESSION ‘5,31’;<\/p>\n

3. %kill -9 17309 (\uc720\ub2c9\uc2a4\uc0c1\uc758 Shadown Process)<\/p>\n

stop\/id= (PROC SPID=SESS SPID on vms running single task)<\/p>\n

\uc5ec\uae30\uc11c SYS \uc720\uc800\ub294 \uc81c\uc678\uc2dc\ucf30\ub294\ub370 \ud544\uc694\ud558\ub2e4\uba74 Query\uc758 Where \uc870\uac74\uc5d0\uc11c s.username != ‘SYS’ \ubd80\ubd84\uc744 \uc0ad\uc81c\ud558\uba74 \ub41c\ub2e4.<\/p>\n

 <\/p>\n

\u25a0 CATBLOCK.SQL & UTLLOCKT.SQL<\/p>\n

$ORACLE_HOME\/rdbms\/admin \ub514\ub809\ud1a0\ub9ac\uc5d0 \uc788\ub294 \uc2a4\ud06c\ub9bd\ud2b8 \uac00\uc6b4\ub370 catblock.sql \uacfc utlockt.sql\uc744 \uc0ac\uc6a9\ud558\uc5ec\uc11c Lock \uc0c1\ud669\uc744 \uc27d\uac8c \ud30c\uc545\ud560 \uc218 \uc788\ub2e4. \uc774\ub4e4\uc740 \ub2e4\uc74c\uacfc \uac19\uc774 \uc2e4\ud589\ud55c\ub2e4.<\/p>\n

 <\/p>\n

%cd $ORACLE_HOME\/rdbms\/admin<\/p>\n

%sqldba lmode=y<\/p>\n

SQL>connect internal<\/p>\n

SQL>@catblock<\/p>\n

SQL>@catproc<\/p>\n

\uacb0\uacfc\ub294 \ub2e4\uc74c Query \ubb38\uc73c\ub85c \uc54c\uc544 \ubcf8\ub2e4.<\/p>\n

column waiting_session format a8<\/p>\n

select lpad(‘ ‘,3*(level-1)) || waiting_session,<\/p>\n

lock_type,<\/p>\n

mode_requested,<\/p>\n

mode_held,<\/p>\n

lock_id1,<\/p>\n

lock_id1,<\/p>\n

lock_id2<\/p>\n

from lock_holders<\/p>\n

connect by prior waiting_session = holding_session<\/p>\n

start with holding_session is null;<\/p>\n

\uc704\uc758 Query\uc5d0 \uc758\ud55c \ucd9c\ub825\uc740 \ub2e4\uc74c\uacfc \uac19\ub2e4.<\/p>\n

WAITING_ LOCK_TYPE MODE_REQUE MODE_HELD LOCK_ID1 LOCK_ID2<\/p>\n

——– —————– ———- ———- ———- ———-<\/p>\n

5 None<\/p>\n

6 Transaction Exclusive Exclusive 262172 90<\/p>\n

9 Transaction Exclusive Exclusive 262172 90<\/p>\n

\uc5ec\uae30\uc11c Session 6, Session 9\uac00 Session 5\ub97c \uae30\ub2e4\ub9ac\uace0 \uc788\uc74c\uc744 \uc54c \uc218 \uc788\ub2e4.<\/p>\n

 <\/p>\n

\u25a0 Lock & Hanging \ubb38\uc81c\ub97c \ucd94\uc815\ud558\ub294 \ubc29\ubc95<\/p>\n

\ud504\ub85c\uadf8\ub7a8 \uc0c1\uc5d0\uc11c \uc5b4\ub290 \ubd80\ubd84\uc774 Lock, Hanging \ubb38\uc81c\ub97c \uc77c\uc73c\ud0a4\ub294\uc9c0 \uc54c\uc544\ub0b4\uae30\uac00 \uc5ec\uc758\uce58 \uc54a\uc744\ub54c \ub2e4\uc74c\uacfc \uac19\uc740 \ubc29\ubc95\uc744 \uc0ac\uc6a9\ud574 \ubcf4\uae30 \ubc14\ub780\ub2e4.<\/p>\n

1. init.ora\uc758 sql_trace=ture\ub85c \uc138\ud305\ud558\uba74 \uc5f0\uad00\ub41c SQL \uba85\ub839\uc774 \ucd9c\ub825\ub420 \uac83\uc774\ub2e4.<\/p>\n

2. OS \uc0c1\uc5d0\uc11c\ub3c4 Process \uc0c1\ud0dc\ub97c \uc810\uac80\ud558\uc5ec \ubcf8\ub2e4.<\/p>\n

3. OS \uc0c1\uc758 Debugging \uae30\ub2a5\uc744 \uc0ac\uc6a9\ud558\uac70\ub098 \ub9cc\uc57d \uac00\ub2a5\ud558\ub2e4\uba74 oradbx\ub97c \uc0ac\uc6a9\ud55c\ub2e4. Platform \uc5d0 \ub530\ub77c\uc11c \uc5c6\uc744 \uc218\ub3c4 \uc788\ub2e4.<\/p>\n

4. \uc5ec\ub7ec\uac00\uc9c0 Monitoring \ubc29\ubc95\uacfc Locking\/Blocking \uc2a4\ud06c\ub9bd\ud2b8\ub97c \uc774\uc6a9\ud55c\ub2e4.<\/p>\n

 <\/p>\n

\u25a0 PROCESS STACKS \ud655\uc778<\/p>\n

\ub54c\ub85c\ub294 Hanging Process\ub098 \ub9ac\uc18c\uc2a4\ub97c \uc810\uc720\ud558\uace0 \uc788\ub294 Process\uc758 Process Stack\uc744 \uc810\uac80\ud574 \ubcf4\ub294 \uac83\uc774 \ubb38\uc81c \ud574\uacb0\uc5d0 \ub3c4\uc6c0\uc774 \ub418\ub294 \uacbd\uc6b0\uac00 \uc788\ub2e4.<\/p>\n

1. OS Debugging \ud504\ub85c\uadf8\ub7a8\uc744 \uc774\uc6a9\ud558\uc5ec Hangup \ub418\uae30 \uc804\uc758 \ub9c8\uc9c0\ub9c9 Call\uc744 \ud655\uc778 \ud55c\ub2e4.<\/p>\n

ex)%truss -p<\/p>\n

 <\/p>\n

2. oradbx(\uc624\ub77c\ud074 debugging \ud504\ub85c\uadf8\ub7a8)\uc740 Support,Development \uc2dc\uc5d0\ub9cc \uc0ac\uc6a9 \ub41c\ub2e4.<\/p>\n

 <\/p>\n

select substr(s.username,1,11) “ORACLE USER” ,<\/p>\n

p.pid “PROCESS ID”, s.sid “SESSION ID”, s.serial#,<\/p>\n

osuser “OS USER”, p.spid “PROC SPID”<\/p>\n

from v$session s, v$access a<\/p>\n

where a.sid=s.sid and<\/p>\n

p.addr=s.paddr and<\/p>\n

s.username != ‘SYS’<\/p>\n

\/<\/p>\n

\uc704\uc758 Query\ub97c \uc2e4\ud589\ud558\uba74 \ub2e4\uc74c\uacfc \uac19\uc740 \uacb0\uacfc\uac00 \ucd9c\ub825\ub41c\ub2e4.<\/p>\n

ORACLE PROCESS SESSION SERIAL# OS USER PROC SESS LOCKWT<\/p>\n

USER ID ID SPID SPID<\/p>\n

—— ——- ——- ——- ——- —- —- ——<\/p>\n

LTO 19 5 31 usupport 17312 17309<\/p>\n

LTO2 25 6 43 usupport 17313 17310 C3D320F4<\/p>\n

LTO3 26 9 1 usupport 17314 17311 C3D320D8<\/p>\n

\ub9cc\uc57d oradbx\uac00 \uc5c6\ub2e4\uba74 \ub2e4\uc74c\uacfc \uac19\uc774 \ud574\uc11c \ub9cc\ub4e4\uc5b4 \uc900\ub2e4.<\/p>\n

%cd $ORACLE_HOME\/rdbms\/lib<\/p>\n

%make -f oracle.mk oradbx<\/p>\n

LTO Process\uac00 \ubb34\uc5c7\uc744 \ud558\uace0 \uc788\ub294\uc9c0 \uc54c\uace0 \uc2f6\uc73c\uba74 Process Stack\uc744 \ubcf4\uba74 \uc54c \uc218 \uc788\ub2e4.<\/p>\n

ps -ef | grep 17312<\/p>\n

usupport 17312 17309 0 Sep 15 ? 0:00 oracleV713(DESCRIPTION=(LOCAL=YE type debug 17312 (\uc774 \uc720\uc800\uc758 oracle shadow process)<\/p>\n

dump stack<\/p>\n

dump procstat<\/p>\n

\uc704\uc5d0\uc11c \uc0dd\uc131\ub41c \ud2b8\ub808\uc774\uc2a4 \ud654\uc77c(user_dump_dest \uc5d0 \uc0dd\uc131\ub428)\uc744 \uc774\uc6a9\ud558\uba74 Hanging \ubb38\uc81c\ub97c \ud574\uacb0\ud558\ub294\ub370 \ud070 \ub3c4\uc6c0\uc774 \ub41c\ub2e4.<\/p>\n

 <\/p>\n

\u25a0 \uc790\uc8fc \ubc1c\uc0dd\ud558\ub294 LOCK \ubb38\uc81c\ub4e4<\/p>\n

1. Parent-Child \uad00\uacc4\ub85c \ubb36\uc778 \ud14c\uc774\ube14\uc5d0\uc11c Child \ud14c\uc774\ube14\uc5d0 Index\uac00 \uc5c6\ub294 \uc0c1\ud669\uc11c Child \ud14c\uc774\ube14\uc744 \uc218\uc815\ud558\uac8c \ub418\uba74 Parent\ud14c\uc774\ube14\uc5d0 TABLE LEVEL SHARE LOCK\uc774 \uac78\ub9ac\uac8c \ub418\uc5b4\uc11c Parent \ud14c\uc774\ube14\uc5d0 \ub300\ud55c \ubaa8\ub4e0 Update\uac00 \uae08\uc9c0\ub41c\ub2e4.<\/p>\n

2. \ube14\ub7ed\uc758 PCTFREE\uac00 \ub9e4\uc6b0 \uc791\ub2e4\uba74 \ud55c \ube14\ub7ed\uc5d0 \uc5ec\ub7ec\uac1c\uc758 \ub808\ucf54\ub4dc\uac00 \ub4e4\uc5b4 \uc788\uae30 \ub54c\ubb38\uc5d0 \ud55c \ube14\ub7ed\uc5d0 \uacfc\ub3c4\ud55c \ud2b8\ub79c\uc7ad\uc158\uc774 \ub4e4\uc5b4\uc640\uc11c \ube14\ub7ed\uc758 Transaction Layer\uac00 Release \ub418\uae30\ub97c \uae30\ub2e4\ub9ac\uac8c \ub418\ub294 \uacbd\uc6b0\ub3c4 \uc788\ub2e4.<\/p>\n

 <\/p>\n

Ex) create table test (a number) initrans 1 maxtrans 1;<\/p>\n

SYSTEM: insert into test values (5); \/* completed *\/<\/p>\n

SCOTT: insert into SYSTEM.test values (10); \/* Scott waits *\/<\/p>\n

SID OWNER LOCK_TYPE MODE_HELD MODE_REQUE LOCK_ID1 LOCK_ID2<\/p>\n

—- —– ————- ———– ———- ——– ——–<\/p>\n

7 System Transaction Exclusive None 196639 54<\/p>\n

10 Scott Transaction None Share 196639 54<\/p>\n

 <\/p>\n

db_file_simultaneous_writes \ud30c\ub77c\ubbf8\ud130\ub294 \uc778\ub371\uc2a4\uc758 \uc7ac\uad6c\uc131\uc2dc \ud37c\ud3ec\uba3c\uc2a4\uc5d0 \ubcc4\ub85c \uc601\ud5a5\uc744 \ub07c\uce58\uc9c0 \uc54a\ub294\ub2e4.<\/p>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

– \uc751\uc6a9\ud504\ub85c\uadf8\ub7a8\ub4e4\uc758 \uae30\ub2a5\uc774 \ubcf5\uc7a1\ud574\uc838\uac00\uba74\uc11c Lock \ubb38\uc81c\uac00 \ube48\ubc88\ud558\uac8c \ubc1c\uc0dd\ud568\uc5d0 \ub530\ub77c \uc774\ub97c \ud574\uacb0\ud558\ub294 \uac83\uc774 \ub9e4\uc6b0 \uc911\uc694\ud55c \uc77c\uc774 \ub418\uc5c8\ub2e4. \ub3c4\ub300\uccb4 \uc5b4\ub5a0\ud55c \ud504\ub85c\uadf8\ub7a8\uc774 Lock\uc744 \ubc1c\uc0dd\uc2dc\ucf1c \ub370\uc774\ud0c0\ubca0\uc774\uc2a4\ub97c Hangup \uc0c1\ud0dc\uc5d0 \uc774\ub974\uac8c \ud558\ub294\uac00\ub97c \uc54c\uc544\ub0b4\ub294 \uac83\uc740 \uc26c\uc6b4 \uc77c\uc774 \uc544\ub2c8\uc9c0\ub9cc \uc9c0\uae08\ubd80\ud130 \uc774 \ubb38\uc81c\ub97c \ud574\uacb0\ud558\ub294 \ubc29\ubc95\uc5d0 \ub300\ud558\uc5ec \uc54c\uc544\ubcf4\ub3c4\ub85d \ud55c\ub2e4. SQLDBA MONITOR SCREENS ———————- $ TERM=vt100;export TERM $ sqldba [ connect internal ] -> [ key-0(ins […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[11],"tags":[],"jetpack_featured_media_url":"","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2688"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2688"}],"version-history":[{"count":4,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2688\/revisions"}],"predecessor-version":[{"id":2692,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2688\/revisions\/2692"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2688"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2688"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2688"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}