How to check table lock in oracle?
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?
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
Answer (1)
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.