dba_ddl_locks
Name Null? Type
---------------- -------- ------------
SESSION_ID NUMBER
OWNER VARCHAR2(30)
NAME VARCHAR2(30)
TYPE VARCHAR2(40)
MODE_HELD VARCHAR2(9)
MODE_REQUESTED VARCHAR2(9)
Lists all DDL locks held in the database and all outstanding requests for locks. To see it in action, open two sqlplus sessions to a database. In one create and run the following procedure:
create or replace procedure test_proc is
i number;
begin
for i in 1 .. 60 loop
sys.dbms_lock.sleep(1);
end loop;
end;
/
exec test_proc
That will take 60 seconds to run. While the procedure is running a lock will be held on it to prevent anyone altering it. In the second window, run the following query:
select ses.username
, ddl.session_id SID
, ses.serial# SER#
, owner || '.' || ddl.name object
, ddl.type
, ddl.mode_held
, ddl.mode_requested
from dba_ddl_locks ddl
, v$session ses
where ddl.session_id = ses.sid
and ddl.name = 'TEST_PROC'
/
You should see something like this:
USERNAME SID SER# OBJECT TYPE MODE_HELD MODE_REQU
-------- --- ----- ------------- -------------------- --------- ---------
SYS 135 24519 SYS.TEST_PROC Table/Procedure/Type Null None
Taking this a step further, you can try re-running the create procedure statement while the procedure is still running. Because of the lock it will have to wait for the lock to be freed, and you will see second entry in dba_ddl_locks:
USERNAME SID SER# OBJECT TYPE MODE_HELD MODE_REQU
-------- --- ----- ------------- -------------------- --------- ---------
SYS 126 2623 SYS.TEST_PROC Table/Procedure/Type None Exclusive
SYS 135 24519 SYS.TEST_PROC Table/Procedure/Type Null None
For more useful DBA queries click here.
| 