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
while true
end loop;

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’;

———- —————————— ——————————
—————————————- ——— ———
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: Logo

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.