Lock Types - Oracle Database Transactions and Locking Revealed - page 40

Other types of DDL take share DDL locks. These are taken out against dependent objects when you create stored,
compiled objects, such as procedures and views. For example, if you execute the following, share DDL locks will be
placed against both EMP and DEPT while the CREATE VIEW command is being processed:
Create view MyView
as
select emp.empno, emp.ename, dept.deptno, dept.dname
from emp, dept
where emp.deptno = dept.deptno;
You can modify the contents of these tables, but you cannot modify their structure.
The last type of DDL lock is a breakable parse lock. When your session parses a statement, a parse lock is
taken against every object referenced by that statement. These locks are taken in order to allow the parsed, cached
statement to be invalidated (flushed) in the shared pool if a referenced object is dropped or altered in some way.
A view that is invaluable for looking at this information is DBA_DDL_LOCKS . There is no V$ view. The DBA_DDL_LOCKS
view is built on the more mysterious X$ tables and, by default, it might not be installed in your database. You can install
this and other locking views by running the catblock.sql script found in the directory [ORACLE_HOME]/rdbms/admin .
This script must be executed as the user SYS in order to succeed. Once you have executed this script, you can run a
query against the view. For example, in a freshly connected session, I might see the following:
EODA@ORA12CR1> connect eoda/foo
Connected.
EODA@ORA12CR1> set linesize 1000
EODA@ORA12CR1> select session_id sid, owner, name, type,
2 mode_held held, mode_requested request
3 from dba_ddl_locks
4 where session_id = (select sid from v$mystat where rownum=1)
5 /
SID OWNER NAME TYPE HELD REQUEST
------ -------- --------------------- -------------------- ---------- --------
22 SYS DBMS_OUTPUT Body Null None
22 SYS DBMS_OUTPUT Table/Procedure/Type Null None
22 EODA EODA 18 Null None
22 SYS DBMS_APPLICATION_INFO Body Null None
22 SYS PLITBLM Table/Procedure/Type Null None
22 SYS DBMS_APPLICATION_INFO Table/Procedure/Type Null None
22 EODA 73 Share None
22 SYS DATABASE 18 Null None
8 rows selected.
These are all the objects that my session is locking. I have breakable parse locks on a couple of the
DBMS_* packages. These are a side effect of using SQL*Plus; it might call DBMS_APPLICATION_INFO , for example, when
you initially log in (to enable/disable DBMS_OUTPUT via the SET SERVEROUTPUT command). I may see more than one
copy of various objects here; this is normal, and it just means I have more than one thing I'm using in the shared
pool that references these objects. Note that in the view, the OWNER column is not the owner of the lock; rather, it
is the owner of the object being locked. This is why you see many SYS rows. SYS owns these packages, but they all
belong to my session.