一些会话语句失败，可能与其所依赖的对象有关。比如对一个正在执行truncate的表，库缓存中会锁住该表，同时再执行涉及这个表的存储过程的编译compile，必定无法成功。

检查依赖对象涉及的会话：

点击(此处)折叠或打开

SQL> SELECT * FROM V$ACCESS
WHERE (OWNER, OBJECT) IN
(SELECT REFERENCED_OWNER, REFERENCED_NAME FROM DBA_DEPENDENCIES
WHERE OWNER = 'FUJIANREP' AND NAME = 'P_GENERATE_REPDATA');

SID OWNER OBJECT TYPE
---------- ------------------------------ ------------------------------ ------------
54 FUJIANREP CAT_BUYER SYNONYM
54 FUJIANREP CAT_CATEGORY SYNONYM
54 FUJIANREP CAT_DOSEAGE_FORM SYNONYM
54 FUJIANREP CAT_DRUG SYNONYM
54 FUJIANREP CAT_ENTERPRISE SYNONYM
54 FUJIANREP CAT_METRIC SYNONYM

根据找出的SID，找出对应的会话

点击(此处)折叠或打开

SQL> SELECT SID, SERIAL#, USERNAME, PROGRAM, TERMINAL
FROM V$SESSION WHERE SID = 54;

SID SERIAL# USERNAME PROGRAM TERMINAL
---------- ---------- ------------------------------ ------------ ----------
54 26216 FUJIANREP PlSqlDev.exe LIBY

找出SQL语句

点击(此处)折叠或打开

SQL> SELECT SQL_TEXT FROM V$SQL
WHERE ADDRESS IN (SELECT SQL_ADDRESS FROM V$SESSION WHERE SID = 54);

根据得出的信息，分析原因。

ITPub博客

分析依赖对象对当前失败会话的影响

最新文章