Oracle Process Locking

Sometimes our Oracle database will have SQL that locking the table (due insert or update statement). The SQL command as below will give you the information of the table locked and what is the SQL running that lock the table: -

Detect Oracle Process Locking

SQL> SELECT b.sid, b.serial#, a.spid, b.program, b.osuser, b.machine, 
b.type, b.event, b.action, b.p1text, b.p2text, b.p3text, b.state, c.sql_text,b.logon_time, 
b.STATUS,  e.owner, e.object_name "Table Lock"
FROM
v$process a,
v$session b,
v$sqltext c,
v$locked_object d,
dba_objects e
WHERE a.addr=b.paddr
AND b.sql_address = c.address
AND b.sid = d.session_id
AND d.object_id = e.object_id
AND b.STATUS = 'ACTIVE'
ORDER BY a.spid, c.piece

Leave a Comment

Recent Posts

Recent Comments

shubhod salian: Hi Indu, You need to execute adadmin on node B , simply because your forms server resides on this...
hari: May I know what is the referred meta link for the permanent solution.
guru: Hi Indu, you just need to run once if your multi node apps environment is running on shared APPL_TOP...
indu: hi guru, my application is in multi node environment. admin, concurrent, report servers are in node A. form and...
Hussain: Thanks Guru for your quick and kind help.

RSS

RSS