从DBA_DDL_LOCKS视图获得DDL锁定信息

http://liwenshui322.iteye.com/blog/1166934

DDL锁有三种：

     1.排他DDL锁（Exclusive DDL lock）：这会防止其他会话得到它们自己的DDL锁或TM（DML）锁。这说明，在DDL操作期间你可以查询一个表，但是无法以任何方式修改这个表。

     2.共享DDL锁（Share DDL lock）：这些锁会保护所引用对象的结构，使之不会被其他会话修改，但是允许修改数据。

     3.可中断解析锁（Breakable parse locks）：这些锁允许一个对象（如共享池中缓存的一个查询计划）向另外某个对象注册其依赖性。如果在被依赖的对象上执行DDL，Oracle会查看已经对该对象注册了依赖性的对象列表，并使这些对象无效。因此，这些锁是“可中断的”，它们不能防止DDL出现。

排他DDL锁：如：Alter table t add new_column date

共享DDL锁：在创建存储的编译对象（如过程和视图）时，会对依赖的对象加这种共享DDL锁。例如，如果执行以下语句：

      Create view MyView
          as
      select *  
      from emp, dept
      where emp.deptno = dept.deptno;

案例：删除一张表，无响应

SQL> select count(*) from T_LOG;

COUNT(*)
----------
9097
SQL> truncate TABLE "ELON"."T_LOG";
SQL> drop table "ELON"."T_LOG";

SQL> select owner,table_name,status from dba_tables where table_name='T_LOG';

OWNER TABLE_NAME STATUS
------------------------------ ------------------------------ --------
ELON T_LOG VALID

SQL> conn ELON/ELON

SQL> select sid,serial#,paddr,username,osuser,machine,program from v$session where sid in( select SESSION_ID from dba_ddl_locks where name='T_LOG');

SID SERIAL# PADDR USERNAME OSUSER MACHINE PROGRAM
---------- ---------- ---------------- ------------------------------ ------------------------------ --------------- -------------------------
58 61519 C000000CEB26D418 SYS oracle app01 sqlplus@app01 (TNS V1-
V3)

2808 16967 C000000CEB31B6E8 JDSS Administrator WIN-LLI9R4268RH JDBC Thin Client
5121 4795 C000000CEAF1F888 SYS oracle app01 oracle@app01 (J013)

查询持有DDL排它锁的会话
SQL>select sid,serial#,s.sql_id,sql_text,program from v$session s,v$sql l where sid in(58,2808,5121) and s.sql_id=l.sql_id

SID SERIAL# SQL_ID SQL_TEXT PROGRAM
---------- ---------- ------------- -------------------------------------------------- -------------------------
2808 16967 fgzbtnugq630d update T_LOG a set (a.STATUS,a.ZGSTATUS)=( JDBC Thin Client
select STATUS,ZGSTATUS from T_LOG@ELON_XHDoT_WH b where b.txid=a.txid) where a.STATUS<>'1' and a.STATUS<>'2' and a.modify_time>sysdate-5 and ts='GCN'

5121 4795 1cv2jg0a6m0k8 begin DBMS_STATS.CLEANUP_STATS_JOB_PROC(:1,:2,:3,: oracle@app01 (J013)
4,:5); end;

 

SQL> alter system kill session '2808,16967';
alter system kill session '2808,16967'
*
ERROR at line 1:
ORA-00031: session marked for kill

SQL> select spid, osuser, s.program from v$session s,v$process p where s.paddr=p.addr and s.sid=2808;

SPID OSUSER PROGRAM
------------------------ ------------------------------ -------------------------
23928 Administrator JDBC Thin Client

app01@/data/diag/rdbms/prod/prod/trace$ps -ef | grep 23928
oracle 21349 19923 0 17:28:48 pts/0 0:00 grep 23928
oracle 23928 1 0 4? 10 ? 1:22 oraclecddb (LOCAL=NO)

app01@/data/diag/rdbms/prod/prod/trace$kill -9 23928
app01@/data/diag/rdbms/prod/prod/trace$ps -ef | grep 23928
oracle 21680 19923 0 17:31:45 pts/0 0:00 grep 23928