关于DDL 锁的一些实验和疑问，欢迎大家讨论
scott@NEI> select count(*) from big;
COUNT(*)
----------
849303
scott@NEI> select sid from v$mystat where rownum<2;
SID
----------
159
scott@NEI> alter table big drop column owner;
Table altered. ========》执行这个的时候会hang住约10秒
此时开启另一个session
ys@NEI> r
1* select * from dba_ddl_locks where session_id=159
SESSION_ID OWNER NAME TYPE MODE_HELD MODE_REQU
---------- -------------------- -------------------- -------------------- --------- ---------
159 SYS DICTIONARY_OBJ_OWNER Table/Procedure/Type Null None
159 SYS DBMS_SYS_SQL Body Null None
159 SYS DBMS_STANDARD Table/Procedure/Type Null None
159 XDB XD+s5hmkr05krgMFeMBR 25 Share None
YQnQ==
159 XDB DBMS_XDBZ0 Body Null None
159 SYS XML_SCHEMA_NAME_PRES Table/Procedure/Type Null None
ENT
159 SYS PLITBLM Table/Procedure/Type Null None
159 SYS XML_SCHEMA_NAME_PRES Body Null None
ENT
159 SYS DICTIONARY_OBJ_TYPE Table/Procedure/Type Null None
159 XDB XD+s5hmkh15krgMFeMBR 25 Share None
YQnQ==
159 SYS DATABASE 18 Null None
159 SYS DBMS_SQL Body Null None
159 SCOTT SCOTT 18 Null None
159 SYS DBMS_OUTPUT Table/Procedure/Type Null None
159 SYS DBMS_APPLICATION_INF Table/Procedure/Type Null None
O
159 XDB XDh1jUheYAR5PgNAgAIL 25 Share None
JCxg==
159 SYS AW_DROP_PROC Table/Procedure/Type Null None
159 XDB XDbD/PLZ01TcHgNAgAII 25 Share None
egtw==
159 XDB XDB$EXTNAME2INTNAME Table/Procedure/Type Null None
SESSION_ID OWNER NAME TYPE MODE_HELD MODE_REQU
---------- -------------------- -------------------- -------------------- --------- ---------
159 SYS DBMS_SQL Table/Procedure/Type Null None
159 XDB DBMS_XDBZ0 Table/Procedure/Type Null None
159 SYS DBMS_APPLICATION_INF Body Null None
O
159 SYS DICTIONARY_OBJ_NAME Table/Procedure/Type Null None
23 rows selected.
没有任何关于这个DDL的信息
另一个session
select * from v$lock where sid=159
425C447C 425C4494 159 TM 134145 0 5 0 7 0
426028C8 426029E4 159 TX 655370 39916 6 0 7 0
可以看出获得一个共享排他锁S/Row-X (SSX)和排他的X锁这个时候我觉得应该在操作数据字典表也就是tab$
sys@NEI> select owner,object_name,status from all_objects where object_id=134145;
OWNER OBJECT_NAME STATUS
-------------------- ------------------------------ -------
SCOTT BIG VALID
我的问题就是为什么在dba_ddl_locks里面查不到锁的信息~我昨天做的实验中偶尔是可以查到一个share模式的锁
但今天怎么也查不到了。请大侠们一起来看看