Get the name and type of object locked : dba_locks « System Tables Data Dictionary « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. System Tables Data Dictionary
3. dba_locks

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>