一些会话语句失败,可能与其所依赖的对象有关。比如对一个正在执行truncate的表,库缓存中会锁住该表,同时再执行涉及这个表的存储过程的编译compile,必定无法成功。
检查依赖对象涉及的会话:
根据找出的SID,找出对应的会话
找出SQL语句
根据得出的信息,分析原因。
检查依赖对象涉及的会话:
点击(此处)折叠或打开
-
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
---------- ------------------------------ ------------------------------ ------------
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
点击(此处)折叠或打开
- SQL> SELECT SID, SERIAL#, USERNAME, PROGRAM, TERMINAL
- FROM V$SESSION WHERE SID = 54;
-
SID SERIAL# USERNAME PROGRAM TERMINAL
---------- ---------- ------------------------------ ------------ ----------
54 26216 FUJIANREP PlSqlDev.exe LIBY
---------- ---------- ------------------------------ ------------ ----------
54 26216 FUJIANREP PlSqlDev.exe LIBY
点击(此处)折叠或打开
-
SQL> SELECT SQL_TEXT FROM V$SQL
- WHERE ADDRESS IN (SELECT SQL_ADDRESS FROM V$SESSION WHERE SID = 54);
根据得出的信息,分析原因。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22621861/viewspace-1544633/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22621861/viewspace-1544633/