DDL LOCKS IN ORACLE
Whenever an object undergo DML operation like insert,update or delete .. , the table will be kept under row wide exclusive lock to maintain consistency between concurrent users. If an object is locked , then other session which try to modify the session cannot modify the table until the blocking session releases the lock
I update the table tabx
SQL> update tabx set order_priority='H' where order_id between 1000 and 10000000;
523789 rows updated.Now the table is under enq: TX – row lock contention and we cannot add a column on the table as the rows are exclusively locked until the first session issue a commit. So the add column command hang until rollback or commit of update session
kish@exdbx<>alter table tabx add order_cost varchar(20);
You can see the lock mode as 3 – ROW_X (SX): Row Exclusive Table Lock
kish@exdbx<>@colfrmt
Procedure created.
Elapsed: 00:00:00.04
Procedure created.
Elapsed: 00:00:00.02
kish@exdbx<>col ORACLE_USERNAME format a15
col OS_USER_NAME format a15
col OBJECT_NAME format a15
col NAME format a15
col TYPE format a15
col PROCESS format 999999
col MODE_HELD format a15
col MODE_REQUESTED format a15
select s.sid,s.serial#,do.OBJECT_NAME,lo.ORACLE_USERNAME,lo.PROCESS,lo.LOCKED_MODE,ddl.OWNER,ddl.TYPE,ddl.MODE_HELD,ddl.MODE_REQUESTED,s.event
from dba_ddl_locks ddl
inner join v$locked_object lo on ddl.SESSION_ID=lo.SESSION_ID
inner join dba_objects do on lo.object_id=do.object_id
inner join v$session s on lo.SESSION_ID = s.sid
where object_name='TABX';kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<> 2 3 4 5 6
SID SERIAL# OBJECT_NAME ORACLE_USERNAME PROCESS LOCKED_MODE OWNER TYPE MODE_HELD MODE_REQUESTED EVENT
---------- ---------- --------------- --------------- ------------------------ ----------- ------------------------------------------------------------------------------------------------------------------------ --------------- --------------- --------------- ----------------------------------------------------------------
33 303 TABX TEST 17397 3 TEST 18 Null None enq: TX - row lock contention
64 79 TABX TEST 18900:17096 3 TEST 18 Null None SQL*Net message from client
33 303 TABX TEST 17397 3 SYS Table/Procedure Null None enq: TX - row lock contention
/Type
33 303 TABX TEST 17397 3 SYS Body Null None enq: TX - row lock contention
64 79 TABX TEST 18900:17096 3 SYS Body Null None SQL*Net message from client
33 303 TABX TEST 17397 3 SYS Table/Procedure Null None enq: TX - row lock contention
/Type
33 303 TABX TEST 17397 3 SYS Table/Procedure Null None enq: TX - row lock contention
/Type
64 79 TABX TEST 18900:17096 3 NULL 73 Share None SQL*Net message from client
33 303 TABX TEST 17397 3 NULL 73 Share None enq: TX - row lock contention
33 303 TABX TEST 17397 3 NULL 73 Null None enq: TX - row lock contention
33 303 TABX TEST 17397 3 SYS Table/Procedure Null None enq: TX - row lock contention
/Type
64 79 TABX TEST 18900:17096 3 SYS Table/Procedure Null None SQL*Net message from client
/Type
33 303 TABX TEST 17397 3 MDSYS Table/Procedure Null None enq: TX - row lock contention
/Type
33 303 TABX TEST 17397 3 SYS Body Null None enq: TX - row lock contention
33 303 TABX TEST 17397 3 MDSYS Body Null None enq: TX - row lock contention
33 303 TABX TEST 17397 3 SYS 18 Null None enq: TX - row lock contention
64 79 TABX TEST 18900:17096 3 SYS 18 Null None SQL*Net message from client
33 303 TABX TEST 17397 3 SYS Table/Procedure Null None enq: TX - row lock contention
/Type
18 rows selected.
Elapsed: 00:00:00.18
There is no other way than to kill the session which lock the object or wait until the locks are released from the object
DML statements can be filtered using kglnaobj to get the queries which access the objects
kish@exdbx<>select distinct k.kglnaobj from x$kgllk k
inner join v$session s on k.kgllkuse=s.saddr
where s.sid in (33,64) and
k.kglnaobj like '%insert%' or k.kglnaobj like '%delete%' or k.kglnaobj like '%update%'; 2 3 4
KGLNAOBJ
------------------------------------------------------------
update WRI$_SCH_CONTROL set num_votes = :votes, la
update smon_scn_time set time_mp=:1, time_dp=:2, scn=:3, scn
update sys.col_usage$ set equality_preds = equality_pre
delete from WRI$_ALERT_OUTSTANDING where reason_id = :1 and
update tabx set order_priority='H' where order_id between 10
insert into sys.aud$( sessionid,entryid,statement,ntimestamp
delete from compression$ where obj#=:1
update sys.mon_mods$ set inserts = inserts + :ins, updates =
8 rows selected.
Elapsed: 00:00:00.07
Check the sid and serial# to kill
kish@exdbx<>select 'alter system kill session '||sid||','||serial#||';' from v$session where username='TEST';
'ALTERSYSTEMKILLSESSION'||SID||','||SERIAL#||';'
------------------------------------------------------------------------------------------------------------
alter system kill session 33,303;
alter system kill session 64,79;
Kill the blocker session
kish@exdbx<>select
(select username from v$session where sid=a.sid) blocker,
a.sid,
' is blocking ',
(select username from v$session where sid=b.sid) blockee,
b.sid
from
v$lock a,
v$lock b
where
a.block = 1
and
b.request > 0
and
a.id1 = b.id1
and
a.id2 = b.id2; 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
BLOCKER SID 'ISBLOCKING' BLOCKEE SID
------------------------------ ---------- ------------- ------------------------------ ----------
TEST 64 is blocking TEST 33
Kill the session
kish@exdbx<>alter system kill session '64,79';
Column addition is successful
kish@exdbx<>alter table tabx add order_cost varchar(20);
Table altered.