Check for locks held after starting the transaction : dba_locks « System Tables Data Dictionary « Oracle PL/SQL Tutorial






SQL>
SQL> CREATE TABLE emp (
  2    id         NUMBER PRIMARY KEY,
  3    fname VARCHAR2(50),
  4    lname  VARCHAR2(50)
  5  );

Table created.

SQL>
SQL> INSERT INTO emp (id, fname, lname)VALUES (1, 'A', 'B');

1 row created.

SQL> INSERT INTO emp (id, fname, lname)VALUES (2, 'C', 'D');

1 row created.

SQL> INSERT INTO emp (id, fname, lname)VALUES (3, 'E', 'F');

1 row created.

SQL> INSERT INTO emp (id, fname, lname)VALUES (4, 'G', 'H');

1 row created.

SQL> INSERT INTO emp (id, fname, lname)VALUES (5, 'G', 'Z');

1 row created.

SQL>
SQL> UPDATE emp
  2  SET fname = 'Ronald'
  3  WHERE id = 44;

0 rows updated.

SQL>
SQL>
SQL> SELECT d.session_id sid, d.lock_type, d.mode_requested,
  2         d.mode_held, d.blocking_others
  3  FROM dba_locks d, v$session v
  4  WHERE v.username = 'PLSQL'
  5  AND d.session_id = v.sid;

no rows selected

SQL>
SQL>
SQL> drop table emp;

Table dropped.








30.36.dba_locks
30.36.1.Check for locks held after starting the transaction
30.36.2.Get the name and type of object locked