Database Reference
In-Depth Information
So, here we see four locks taken out against our object. The two DL locks are
direct load
locks. They are used to
prevent a direct path load into our base table while the index creation is taking place (which implies, of course, that
you cannot directly path load the table AND create the index simultaneously). The
OD
lock is a lock type first appeared
with Oracle 11
g
(you would not see that lock in 10
g
or 9
i
) that permits truly online DDL. In the past (10
g
and before),
online DDL such as
CREATE INDEX
ONLINE
was not 100 percent online. It would take a lock at the beginning and end
of the
CREATE
statement—preventing other concurrent activities (modifications of the base table data). It was
mostly
online
but not
completely online
. Starting with 11
g
, the
CREATE INDEX ONLINE
command is completely online; it does
not require exclusionary locks at the beginning/end of the command. Part of the implementation to accomplish this
feat was the introduction of the
OD
(Online DDL) lock; it is used internally to allow truly online DDL operations.
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.
