DDL锁

    Every create,alter andso on statement is really executed as shown in this pseudo-code:

Begin
	Commit;
	DDL-STATEMENT
	Commit;
Exception
	When others then rollback;
END;

有三种DDL锁

       排他DDL锁 theseprevent other sessions from gaining a DDL lock or TM (DML) lock themselves. Thismeans that you may query a table during a DDL operation, but you may not modifyit in any way.

 

      共享DDL锁：this protectthe structure of the reference object against modification by other sessions,but allow modification to the data.

 

      Breakable parse locks:thisallow an object, such as a query plan cached in the shared pool, to registerits reliance on some other object. If you perform DDL against that object,oraclewill review the list of objects that have registered their dependence andinvalidate them.hence these locks are “breakable” –they do not prevent the DDLfrom occurring.

You can install this and other locking views by runing the catblock.sql crpit found in thedirectory[ORACLE_HOME]/rdbms/admin.

SQL> desc 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)

在这个视图中，owner列不是LOCK的拥有者，而是被锁住的object的属主。

 To see a breakable parse lock in action. Letus first create and run a stored procedure P:

SQL> create or replace procedure p is begin null;end;
  2  /

Procedure created.

SQL> exec p;

PL/SQL procedure successfully completed.

SQL> select * from dba_ddl_locks where name = 'P';

SESSION_ID OWNER                          NAME
---------- ------------------------------ ------------------------------
TYPE                                     MODE_HELD MODE_REQU
---------------------------------------- --------- ---------
       149 OPS                            P
Table/Procedure/Type                     Null      None


重新编译下存储过程P，重新查询一下

SQL> alter procedure p compile;

Procedure altered.

SQL> select * from dba_ddl_locks where name = 'P';

no rows selected

oracle  parse  lock  has  been  broken.