How to find DDL lock on objects?

The following example shows how to find lock on a store procedure, the following store procedure has infinite loop.  A store-procedure is locked when it’s running that it can’t be modified/recompiled when it’s executing.

Window 1)
SQL> create or replace procedure p as
begin
while true
loop
null;
end loop;
end;
/

SQL> exec p;

Window 2)
— run after starting execution of store procedure p
— owner – owner of the lock
— session_id – session id of the lock
— name – object name
— type – object type
— mode_held – None (value when the store procedure is executing), Exclusive (value when store procedure is being compiled)
SQL> select * FROM dba_ddl_locks where name = ‘P’;

SESSION_ID OWNER NAME
———- —————————— ——————————
TYPE MODE_HELD MODE_REQU
—————————————- ——— ———
145 USER P
Table/Procedure/Type Null None

This view is not installed by default and can be created by executing the following $ORACLE_HOME/rdbms/admin/catblock.sql

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s