DBA_DDL_LOCKS

Oracle 11gR1

DBA_DDL_LOCKS lists all DDL locks held in the database and all outstanding requests for a DDL lock.

Column	Datatype	NULL	Description
SESSION_ID	NUMBER		Session identifier
OWNER	VARCHAR2(30)		Owner of the lock
NAME	VARCHAR2(30)		Name of the lock
TYPE	VARCHAR2(40)		Lock type: · Cursor · Table/Procedure/Type · Body · Trigger · Index · Cluster · Java Source · Java Resource · Java Data
MODE_HELD	VARCHAR2(9)		Lock mode: · None · Null · Share · Exclusive
MODE_REQUESTED	VARCHAR2(9)		Lock request type: · None · Null · Share · Exclusive

Note:

1. Show all ddl locks in the system

select ses.username , ddl.session_id , ses.serial# , owner || '.' || ddl.name object , ddl.type , ddl.mode_held from dba_ddl_locks ddl , v$session ses where owner like '%userid%' and ddl.session_id = ses.sid;

Oracle dynamic performance views

Oracle data dictionary views