DDL_LOCK_TIMEOUT parameter set for acquire lock in Oracle

First tried with DDL_LOCK_TIMEOUT method in production environment.

Better Solution for production Environment is use the ddl_lock_timeout parameter at session level. In production environment if you want to add a column in a table and that table is busy table which have lot of transaction running then you have to fire alter command continue until you got the exclusive lock on that table. May other session take that table.

1st Method:
1. To acquire lock you fire the following command at session level.
Alter session set DDL_LOCK_TIMEOUT = 30;
2. Now in the same session you fire the alter table command immediate after step 1 command as follows:
ALTER TABLE SALES ADD (TAX_CODE varchar2(10))
3. Oracle will automatically fire by query for 30 seconds until it get lock or you will get error in return after 30 seconds. You can increase its value to 60 seconds or more as you required.

Note: The statement hangs and does not error out after 30 seconds means command is executing. Instead of DBA tried repeatedly to get the exclusive lock. Oracle 11g get this parameter done for you.

2nd Method
You can follow the following steps if you donot able to fixed with above process:

1. Checked the lock object with following query:

SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME,
S.MACHINE,S.PORT , S.LOGON_TIME,SQ.SQL_FULLTEXT
FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S,
V$PROCESS P, V$SQL SQ
WHERE L.OBJECT_ID = O.OBJECT_ID
AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR
AND S.SQL_ADDRESS = SQ.ADDRESS;

Note: Kill Session if important otherwise wait for the session complete its transaction with longops query or transaction query.

How much time it will lock the object to complete transaction

1. Check the session with longops query:

set line 999 pages 999
select sofar Blocks_Processed,totalwork Total_Work,
totalwork-sofar Total_Work_Left,start_time Start_Time,round((elapsed_seconds/60),0) Elapsed_Minutes,
substr(message,1,33) Message,username, round(sofar/totalwork*100,2) || '%' "Completed"
from v$session_longops where totalwork-sofar > 0 order by start_time desc


2. Check the session with transaction query:

select * from v$transaction;


For killing the session
1. Identify the session which is locking the object

select a.sid, a.serial#
from v$session a, v$locked_object b, dba_objects c
where b.object_id = c.object_id
and a.sid = b.session_id
and OBJECT_NAME='EMP';


2. Kill that session which using objects if required.

alter system kill session 'sid,serial#' immediate;