Oracle PL/SQL Tutorial/System Tables Data Dictionary/dba locks

Check for locks held after starting the transaction

   <source lang="sql">

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.</source>

Get the name and type of object locked

   <source lang="sql">

SQL> SQL> CREATE TABLE emp (

 2    id         NUMBER PRIMARY KEY,
 3    fname VARCHAR2(50),
 4    lname  VARCHAR2(50)
 5  );

Table created. SQL> SQL> SQL> SQL> SELECT dbl.lock_type, dbl.mode_held, dbl.blocking_others,

 2         dbo.object_name object_locked, dbo.object_type
 3  FROM dba_locks dbl, v$session v, dba_objects dbo
 4  WHERE v.username = "PLSQL"
 5  AND dbl.session_id = v.sid
 6  AND dbo.object_id = dbl.lock_id1;

no rows selected SQL> SQL> SQL> drop table emp; Table dropped. SQL></source>