How to check table lock in oracle?

5.1K Asked by EmmaCornish in SQL Server , Asked on Oct 3, 2022

We've been using BI software and a repository database that are installed on Oracle Enterprise 11gR2.

Some of these batch reports will try to access a database table which may still be locked. How can I find out if an Oracle table is locked or not? Is there any SQL statement that displays like history details to analysis?

Answered by Emma Lewis

The answer to your question - how to check table lock in oracle is -

Following query gives details of all locks.

SELECT B.Owner, B.Object_Name, A.Oracle_Username, A.OS_User_Name  

FROM V$Locked_Object A, All_Objects B

WHERE A.Object_ID = B.Object_ID

Your Answer

Answer (1)

Ranjana

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.

6 Months