[오라클]Lock Object 확인

0003. LOCK object 확인

select do.OWNER, do.OBJECT_NAME, do.OBJECT_ID,
lo.SESSION_ID, lo.ORACLE_USERNAME, lo.OS_USER_NAME,
lo.PROCESS, lo.LOCKED_MODE
from dba_objects do, v$locked_object lo
where do.OBJECT_ID = lo.OBJECT_ID
and do.OWNER = 'IBFUSR'
and do.OBJECT_NAME = 'BT_TAB_SYNC_INFO';

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_ID SESSION_ID ORACLE_USERNAME
---------- ---------- ------------------------------
OS_USER_NAME PROCESS LOCKED_MODE
------------------------------ ------------ -----------
IBFUSR
BT_TAB_SYNC_INFO
125142 66 IBFUSR
안민혁 2952:852 3

select SID, SERIAL# from v$session where sid = 66;

SID SERIAL#
---------- ----------
66 48801

alter system kill session '66,48801';
ALTER SYSTEM KILL SESSION '66,48801' IMMEDIATE;
ALTER SYSTEM DISCONNECT SESSION '66,48801' IMMEDIATE;

Situation 1

To check objects currently being used, e.g. by a DML statement, before executing a DDL command, you can query v$locked_object as in the example below.


Session A

SQL> connect miles/oracle;
Connected.

SQL> drop table test;

Table dropped.

SQL> create table test (a number);

Table created.

SQL> insert into test values (1);

1 row created.

Session B

SQL> connect / as sysdba
Connected.

SQL> truncate table miles.test;
truncate table miles.test
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
--> this is expected because miles.test is locked in Session A


SQL>
select do.OWNER, do.OBJECT_NAME, do.OBJECT_ID,
lo.SESSION_ID, lo.ORACLE_USERNAME, lo.OS_USER_NAME,
lo.PROCESS, lo.LOCKED_MODE
from dba_objects do, v$locked_object lo
where do.OBJECT_ID = lo.OBJECT_ID
and do.OWNER = 'MILES'
and do.OBJECT_NAME = 'TEST';


OWNER OBJECT_NAME OBJECT_ID SESSION_ID ORACLE_USERNAME
-------- ------------ ---------- ---------- ------------------------------
OS_USER_NAME PROCESS LOCKED_MODE
-------------------- -------------- -----------
MILES TEST 63754 1079 MILES
oracle 23013 3

From above, you can see miles.test is locked by Oracle user 'MILES' with OS user name 'oracle'.
Session id is 1079, OS process id is 23013.


You can kill the session to release the lock.

SQL> select SID, SERIAL# from v$session where sid = 1079;

SID SERIAL#
---------- ----------
1079 663


SQL> alter system kill session '1079,663';

System altered.

Then the DDL can be executed successfully:

SQL> truncate table miles.test;

Table truncated.

 

Situation 2

If a DDL statement on an object, e.g. create or replace procedure, is hanging because another user is executing the procedure, you can find the blocking session as in the example below.

Using 3 sessions:

1. Session A: connect as sysdba
2. Session B: connect as user rob/rob
3. Session C: connect as user tony/tony

Session A

SQL>
connect / as sysdba
create user rob identified by rob;
grant connect, resource to rob;
create user tony identified by tony;
grant connect, resource to tony;
grant execute on dbms_lock to rob;
grant execute on dbms_lock to tony;

Session B

SQL>
connect rob/rob

create or replace procedure rob_test_p1 is
begin
null;
end;
/

grant execute on rob_test_p1 to tony;

Session C

SQL>
connect tony/tony
begin
rob.rob_test_p1;
dbms_lock.sleep(120);
end;
/

Session A

SQL>
connect / as sysdba

SQL>
select distinct ses.ksusenum sid, ses.ksuseser serial#,
ob.kglnaobj obj_name, ob.KGLNAOWN obj_owner,
ses.KSUUDNAM cur_user
from x$kglpn pn, x$kglob ob, x$ksuse ses
where ob.KGLNAOBJ='ROB_TEST_P1'
and (ob.KGLHDPMD <> 0
or
(ob.KGLHDPMD = 0 and ob.KGLHDLMD not in (0,1))
)
and ob.kglhdadr = pn.kglpnhdl
and pn.kglpnuse = ses.addr;

SID SERIAL# OBJ_NAME OBJ_OWNER CUR_USER
---------- ---------- -------------------- --------------- ------------------
1094 5909 ROB_TEST_P1 ROB TONY


SQL>
select sql_text
from v$sql a, v$session b
where a.sql_id=b.sql_id
and b.serial#=5909;

SQL_TEXT
--------------------------------------------------------------------------------
begin rob.rob_test_p1; dbms_lock.sleep(120); end;

If the DDL is already hanging due to a lock, you can follow below test case to find the lock details.

Session C

SQL> connect tony/tony

SQL>
begin
rob.rob_test_p1;
dbms_lock.sleep(120);
end;
/

Session B

SQL> connect rob/rob

SQL> alter procedure rob_test_p1 compile;
--> This is hanging

Session A

col obj_name format a30
col pin_cnt format 999
col pin_mode format 999
col pin_req format 999
col state format a30
col event format a30
col wait_time format 999999999
col seconds_in_wait format 999999999

SQL>
select distinct ses.ksusenum sid, ses.ksuseser serial#,
ob.kglnaobj obj_name,
pn.kglpncnt pin_cnt, pn.kglpnmod pin_mode, pn.kglpnreq pin_req,
w.state, w.event, w.wait_time, w.seconds_in_Wait
from x$kglpn pn, x$kglob ob, x$ksuse ses, v$session_wait w
where pn.kglpnhdl in (select kglpnhdl from x$kglpn where kglpnreq > 0)
and ob.kglhdadr = pn.kglpnhdl
and pn.kglpnuse = ses.addr
and w.sid = ses.indx
order by seconds_in_wait desc;


SID SERIAL# OBJ_NAME PIN_CNT PIN_MODE PIN_REQ
---------- ---------- ------------------------------ ------- -------- -------
STATE EVENT WAIT_TIME
------------------------------ ------------------------------ ----------
SECONDS_IN_WAIT
---------------
1082 6765 ROB_TEST_P1 3 2 0
WAITING PL/SQL lock timer 0
39

1074 6060 ROB_TEST_P1 0 0 3
WAITING library cache pin 0
27


SQL>
select sql_text
from v$sql a, v$session b
where a.sql_id=b.sql_id
and b.serial#=6765;

SQL_TEXT
--------------------------------------------------------------------------------
begin rob_test_p1; dbms_lock.sleep(120); end;


SQL>
select sql_text
from v$sql a, v$session b
where a.sql_id=b.sql_id
and b.serial#=6060;

SQL_TEXT
--------------------------------------------------------------------------------
alter procedure rob_test_p1 compile