How to check table lock in oracle?

If you suspect a table in Oracle is locked and want to check its status, Oracle provides several ways to identify and troubleshoot table locks. Here’s how you can do it:

1. Query the v$locked_object View

The v$locked_object view provides information about locked objects in the database. Use the query below to identify locked tables:

SELECT lo.object_id, 
       lo.session_id, 
       lo.oracle_username, 
       lo.locked_mode, 
       o.object_name, 
       o.object_type 
FROM v$locked_object lo
JOIN dba_objects o ON lo.object_id = o.object_id;

This will return details like the table name (object_name), the user causing the lock, and the mode of the lock.

2. Check Session Information (v$session)

To find the sessions holding locks, join v$session with v$locked_object:

SELECT s.sid, 
       s.serial#, 
       s.username, 
       s.status, 
       o.object_name, 
       l.locked_mode 
FROM v$session s
JOIN v$locked_object l ON s.sid = l.session_id
JOIN dba_objects o ON l.object_id = o.object_id;

This helps identify the session ID and user causing the lock.

3. Use dba_waiters for Blocking Locks

If you want to find sessions waiting on locks, query the dba_waiters view:

SELECT * 
FROM dba_waiters;

4. Query Blocking Sessions (v$lock)

To check blocking locks directly, use this query:

SELECT l1.sid AS blocker_sid, 
       l2.sid AS waiter_sid 
FROM v$lock l1 
JOIN v$lock l2 ON l1.id1 = l2.id1 AND l1.id2 = l2.id2
WHERE l1.block = 1 AND l2.request > 0;

5. Kill the Blocking Session (if Necessary)

If a lock is causing issues, you can terminate the session causing the lock:

  ALTER SYSTEM KILL SESSION 'sid,serial#';

By using these queries, you can easily monitor and troubleshoot table locks in Oracle to maintain database performance. Let me know if you need more help!

In Oracle, you can check for table locks by querying the V$LOCK and V$SESSION views. Here's how you can do it:

SELECT
    s.username,
    l.session_id,
    s.sid,
    s.serial#,
    s.status,
    s.osuser,
    s.machine,
    s.program,
    l.type,
    l.mode_held,
    l.mode_requested,
    o.owner,
    o.object_name
FROM
    v$lock l
JOIN
    v$session s ON (l.sid = s.sid)
JOIN
    dba_objects o ON (l.id1 = o.object_id)
WHERE
    o.object_type = 'TABLE';

This query joins the V$LOCK, V$SESSION, and DBA_OBJECTS views to retrieve information about locked tables. It provides details about the session holding the lock, the type of lock (e.g., shared or exclusive), and the object being locked.

You can further refine the query based on your specific requirements, such as filtering by a particular table or user, or focusing on a specific type of lock.

Keep in mind that querying system views like V$LOCK and V$SESSION may require appropriate privileges, such as SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY.