Logo Background

Oracle Process Locking

  • By on August 20, 2008 | No Comments

    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"
    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
    » Oracle Memory Allocation
Leave a Comment