![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIn5GcuQmYwUDN2EmZxgDN4MWO0kDN1EjN3IDMllTZ2UjN1MDNfdWbp9CXt92Yu4GZjlGbh5SZslmZxl3Lc9CX6MHc0RHaiojIsJye.png)
一、摘要
當遇到編譯Package無法通過時,需要進行查詢那個session在同時編譯或者使用該package,可通過如下方式查詢
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIn5GcuQmYwUDN2EmZxgDN4MWO0kDN1EjN3IDMllTZ2UjN1MDNfdWbp9CXt92Yu4GZjlGbh5SZslmZxl3Lc9CX6MHc0RHaiojIsJye.png)
二、通過V$ACCESS視圖
通過表v$access就能夠檢視到使用到的表,存儲過程以及cursor
查找使用者正在通路的對象 一旦發現某些使用者或者系統中的查詢存在問題, 查詢 V$ACCESS 可以為您指出有潛在問題的對
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIn5GcuQmYwUDN2EmZxgDN4MWO0kDN1EjN3IDMllTZ2UjN1MDNfdWbp9CXt92Yu4GZjlGbh5SZslmZxl3Lc9CX6MHc0RHaiojIsJye.png)
三、通過DBA_DDL_LOCKS
可以通過以下視圖進行查詢
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIn5GcuQmYwUDN2EmZxgDN4MWO0kDN1EjN3IDMllTZ2UjN1MDNfdWbp9CXt92Yu4GZjlGbh5SZslmZxl3Lc9CX6MHc0RHaiojIsJye.png)
四、通過一段複雜SQL
SELECT DECODE (lob.kglobtyp,
0, 'NEXT OBJECT',
1, 'INDEX',
2, 'TABLE',
3, 'CLUSTER',
4, 'VIEW',
5, 'SYNONYM',
6, 'SEQUENCE',
7, 'PROCEDURE',
8, 'FUNCTION',
9, 'PACKAGE',
11, 'PACKAGE BODY',
12, 'TRIGGER',
13, 'TYPE',
14, 'TYPE BODY',
19, 'TABLE PARTITION',
20, 'INDEX PARTITION',
21, 'LOB',
22, 'LIBRARY',
23, 'DIRECTORY',
24, 'QUEUE',
28, 'JAVA SOURCE',
29, 'JAVA CLASS',
30, 'JAVA RESOURCE',
32, 'INDEXTYPE',
33, 'OPERATOR',
34, 'TABLE SUBPARTITION',
35, 'INDEX SUBPARTITION',
40, 'LOB PARTITION',
41, 'LOB SUBPARTITION',
42, 'MATERIALIZED VIEW',
43, 'DIMENSION',
44, 'CONTEXT',
46, 'RULE SET',
47, 'RESOURCE PLAN',
48, 'CONSUMER GROUP',
51, 'SUBSCRIPTION',
52, 'LOCATION',
55, 'XML SCHEMA',
56, 'JAVA DATA',
57, 'SECURITY PROFILE',
59, 'RULE',
62, 'EVALUATION CONTEXT',
'UNDEFINED')
object_type,
lob.kglnaobj object_name,
pn.kglpnmod lock_mode_held,
pn.kglpnreq lock_mode_requested,
ses.sid,
ses.serial#,
ses.username
FROM x$kglpn pn,
v$session ses,
x$kglob lob,
v$session_wait vsw
WHERE pn.kglpnuse = ses.saddr
and pn.kglpnhdl = lob.kglhdadr
and lob.kglhdadr = vsw.p1raw
ORDER BY lock_mode_held DESC
Thanks and Regards
2015-05-06 Created By BaoXinjian
<a href="http://www.wiz.cn/i/3d838de3">來自為知筆記(Wiz)</a>
<b>ERP技術讨論群: 288307890</b>
<b>技術交流,技術讨論,歡迎加入</b>
<b>Technology Blog Created By Oracle ERP - 鮑建立</b>