Find the session and SQL query which was previously locked.

This Query will help you to find lock history.

SQL> SELECT  DISTINCT A.INST_ID, A.SQL_ID ,A.INST_ID,A.BLOCKING_SESSION,A.BLOCKING_SESSION_SERIAL#,A.USER_ID,S.SQL_TEXT,A.MODULE
  2  FROM  GV$ACTIVE_SESSION_HISTORY A  ,GV$SQL S
  3  WHERE A.SQL_ID=S.SQL_ID
  4  AND BLOCKING_SESSION IS NOT NULL
  5  AND A.USER_ID <> 0;

   INST_ID SQL_ID           INST_ID BLOCKING_SESSION BLOCKING_SESSION_SERIAL#    USER_ID SQL_TEXT
---------- ------------- ---------- ---------------- ------------------------ ---------- -------------------------------------------------------
         1 0jgf8xdjf2h8w          1               74                        9         84  UPDATE REGIONS  SET REGION_NAME='TEST'

SQL>

Note : It will show data if your current log is enough.

Finding Lock In Oracle Database using SQL Statements.

1. Find All Object Which are Currently Locked By Session.

SELECT OBJECT_NAME,S.INST_ID,S.SID,S.SERIAL#,S.OSUSER,S.SERVER,S.MACHINE, S.STATUS,P.PNAME,SQL_TEXT, SQL_FULLTEXT
FROM GV$LOCKED_OBJECT L,GV$SESSION S,GV$PROCESS P,DBA_OBJECTS O, GV$SQLAREA T
WHERE L.OBJECT_ID=O.OBJECT_ID
AND S.SQL_ADDRESS =T.ADDRESS
AND S.SQL_HASH_VALUE =T.HASH_VALUE
AND L.SESSION_ID=S.SID
AND S.PADDR=P.ADDR;

2.  Lists all DML locks and all outstanding requests for a DML lock.

SELECT * FROM DBA_DML_LOCKS;

3. Holding a lock on an object for which another session is waiting.

SELECT * FROM DBA_BLOCKERS;

4. Blocking Session Details.

SELECT BLOCKING_SESSION, SID, SERIAL#, WAIT_CLASS,SECONDS_IN_WAIT
FROM  V$SESSION
WHERE  BLOCKING_SESSION IS NOT NULL
ORDER BY BLOCKING_SESSION;

Oracle Table Locking Details.

When an transaction (INSERT, UPDATE, DELETE, MERGE, and SELECT ... FOR UPDATE) occurred in table. Oracle database lock table. It's called DML lock. The purpose of a DML lock, also called a data lock, is to guarantee the integrity of data being accessed concurrently by multiple users.

Row Locks (TX): A row lock, also called a TX lock, is a lock on a single row of a table. A transaction acquires a row lock for each row modified by using DML( INSERT, UPDATE, DELETE, MERGE) and SELECT ... FOR UPDATE. The row lock exists until the transaction commits or rolls back.

Table Locks (TM): A transaction automatically acquires a table lock (TM lock) when a table is modified with the following statements: INSERT, UPDATE,DELETE, MERGE, and SELECT ... FOR UPDATE. These DML operations require table locks to reserve DML access to the table on behalf of a transaction and to prevent DDL operations that would conflict with the transaction.

Manual Lock : User can lock table manually for interrupting access.

Example:-

SQL> CONN RND/rnd@ORCL
Connected.
SQL>
SQL> CREATE TABLE CURR
  2  (
  3    CURRENCY_CD  VARCHAR2(9 BYTE),
  4    EXCH_RT_EUR  NUMBER,
  5    EXCH_RT_USD  NUMBER
  6  );

Table created.

SQL> INSERT INTO CURR(CURRENCY_CD, EXCH_RT_EUR, EXCH_RT_USD) VALUES ('BDT',1,80);

1 row created.
------------------------------------------------------------------------------------------------------------
Now connect sys schema using another terminal. Because when you exit from current terminal session will be close and transaction will be ROLLBACK.
-----------------------------------------------------------------------------------------------------------------
SQL> CONN /AS SYSDBA
Connected.
SQL> set line 200
SQL> SELECT A.SESSION_ID,
  2         A.ORACLE_USERNAME,
  3         A.OS_USER_NAME,
  4         B.OWNER "OBJECT OWNER",
  5         B.OBJECT_NAME,
  6         B.OBJECT_TYPE,
  7         A.LOCKED_MODE
  8    FROM (SELECT OBJECT_ID,
  9                 SESSION_ID,
 10                 ORACLE_USERNAME,
 11                 OS_USER_NAME,
 12                 LOCKED_MODE
 13            FROM V$LOCKED_OBJECT) A,
 14         (SELECT OBJECT_ID,
 15                 OWNER,
 16                 OBJECT_NAME,
 17                 OBJECT_TYPE
 18            FROM DBA_OBJECTS) B
 19   WHERE A.OBJECT_ID = B.OBJECT_ID;

SESSION_ID ORACLE_USERNAME                OS_USER_NAME                   OBJECT OWNER
---------- ------------------------------ ------------------------------ ------------------------------
OBJECT_NAME                                                                                                              OBJECT_TYPE         LOCKED_MODE
-------------------------------------------------------------------------------------------------------------------------------- ------------------- -----------
         8 RND                            SPFTL\rajib.pradhan            RND
CURR                                                                                                                     TABLE                         3


SQL>
------------------------------------------------------------------------------------------------------
Now apply commit from your previous terminal.
-------------------------------------------------------------------------------------------------------

SQL> commit;

Commit complete.

------------------------------------------------------------------------------------------------------
Now check lock object using sys terminal.
------------------------------------------------------------------------------------------------------

SQL>  SELECT A.SESSION_ID,
  2          A.ORACLE_USERNAME,
  3          A.OS_USER_NAME,
  4          B.OWNER "OBJECT OWNER",
  5          B.OBJECT_NAME,
  6          B.OBJECT_TYPE,
  7          A.LOCKED_MODE
  8     FROM (SELECT OBJECT_ID,
  9                  SESSION_ID,
 10                  ORACLE_USERNAME,
 11                  OS_USER_NAME,
 12                  LOCKED_MODE
 13             FROM V$LOCKED_OBJECT) A,
 14          (SELECT OBJECT_ID,
 15                  OWNER,
 16                  OBJECT_NAME,
 17                  OBJECT_TYPE
 18             FROM DBA_OBJECTS) B
 19    WHERE A.OBJECT_ID = B.OBJECT_ID;

no rows selected


SQL>

How to find object lock session ..

Using this query you can find all locking session


SELECT SID  FROM V$LOCK
 WHERE ID1 IN (SELECT OBJECT_ID FROM DBA_OBJECTS
 WHERE OWNER='RND'
 AND OBJECT_NAME='DEPT');

How to find Locked object in Oracle Database.

Hello dear, Using this example you can find out your locked object list from your database. You must to have DBA prevailed.
 

SQL> CREATE TABLE AAA(ID NUMBER);

Table created.

SQL> INSERT INTO  AAA VALUES(10);

1 row created.

SQL> SELECT A.SID,A.SERIAL#, A.USERNAME,C.OS_USER_NAME,A.TERMINAL,
  2  B.OBJECT_ID,B.OBJECT_NAME OBJECT_NAME
  3  FROM V$SESSION A, DBA_OBJECTS B, V$LOCKED_OBJECT C
  4  WHERE A.SID = C.SESSION_ID
  5  AND B.OBJECT_ID = C.OBJECT_ID;

       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
OS_USER_NAME                   TERMINAL          OBJECT_ID
------------------------------ ---------------- ----------
OBJECT_NAME
--------------------------------------------------------------------------------
       195          7 RND
SPFTL\rajib.pradhan            N1020                215166
AAA

SQL> commit;

Commit complete.

SQL> SELECT A.SID,A.SERIAL#, A.USERNAME,C.OS_USER_NAME,A.TERMINAL,
  2  B.OBJECT_ID,B.OBJECT_NAME OBJECT_NAME
  3  FROM V$SESSION A, DBA_OBJECTS B, V$LOCKED_OBJECT C
  4  WHERE A.SID = C.SESSION_ID
  5  AND B.OBJECT_ID = C.OBJECT_ID;

no rows selected

SQL>