DDL_LOCK_TIMEOUT parameter set for acquire lock in Oracle

First tried with DDL_LOCK_TIMEOUT method in production environment

A better solution for a production environment is to use the ddl_lock_timeout parameter at the session level. When adding a column to a busy table with many ongoing transactions, you should repeatedly issue the alter command until you obtain an exclusive lock on the table, as other sessions may access the 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 keep trying to lock for 30 seconds. If it can’t get the lock, you’ll received an error. You can extend this time to 60 seconds or more if needed.

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 get this parameter done for you.

2nd Method
You can follow the following steps if you do not 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

End the session if needed; otherwise, wait for it to finish the transaction.

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;