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>