How to get what are the locks held in oracle database ?

You can use the Oracle database query below to get lock handles for a specific session or for all sessions in the database:

-- For a specific session
SELECT sid, type, id1, id2, lmode, request, ctime, block FROM v$lock WHERE sid = <session_id>;

-- For all sessions
SELECT sid, type, id1, id2, lmode, request, ctime, block FROM v$lock;

In these queries, v$lock is a dynamic performance view that shows information about locks currently held in the database. The columns in the view provide details about the type of lock, the object being locked, the mode of the lock, and other metadata.

To get the lock handles for a specific session, replace [session_id] with the session ID of the desired session. This will return a list of all locks held by that session.

To get the lock handles for all sessions, simply execute the second query without any modifications. This will return a list of all locks held by all sessions in the database.

It’s important to note that the v$lock view only shows information about currently held locks. If a lock has been released or timed out, it will not appear in the view. Additionally, the information in the view may not be completely up to date, as there may be a delay in the collection of lock information.