1. 故障的确定：
ORA-00600 Error的通用处理
2. 故障的分析
1）根据已经确认的问题是library cache pin，加上若能在trace文件中找出SID，可用以下语句确认
点击(此处)折叠或打开
-
SELECT EVENT,P1TEXT,P1RAW,P2TEXT,P2RAW,STATE
- FROM V$SESSION_WAIT WHERE SID=75;
查询结果的EVENT就是library cache pin
2）确认Job调用的存储过程状态
点击(此处)折叠或打开
-
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS
-
FROM DBA_OBJECTS
-
WHERE OWNER = :1
- AND OBJECT_NAME = :2;
可先关闭该Job
点击(此处)折叠或打开
-
EXEC DBMS_JOB.BROKEN(63,TRUE);
-
COMMIT;
-
-
#杀掉Job进程
-
SELECT SPID FROM V$PROCESS WHERE ADDR IN (
-
SELECT PADDR FROM V$SESSION WHERE SID = 75);
-
- HOST kill -9 <进程ID>;
点击(此处)折叠或打开
-
SELECT * FROM V$ACESS
-
WHERE (OWNER, OBJECT) IN
-
(SELECT REFERENCED_OWNER, REFERENCED_NAME FROM DBA_DEPENDENCIES
- WHERE OWNER = :1 AND NAME = :2);
点击(此处)折叠或打开
-
SELECT SID, SERIAL#, USERNAME, PROGRAM, TERMINAL
- FROM V$SESSION WHERE SID = :1;
点击(此处)折叠或打开
-
SELECT SQL_TEXT FROM V$SQL
- WHERE ADDRESS IN (SELECT SQL_ADDRESS FROM V$SESSION WHERE SID = :1);
点击(此处)折叠或打开
-
SELECT EVENT, P1TEXT, P1, P2TEXT, P2, P3TEXT, P3, SECONDS_IN_WAIT
- FROM V$SESSION_WAIT WHERE SID = :1;
3. 故障解决
1)杀死以上查到的进程
点击(此处)折叠或打开
-
#杀掉Job进程
-
SELECT SPID FROM V$PROCESS WHERE ADDR IN (
-
SELECT PADDR FROM V$SESSION WHERE SID = :1);
-
- HOST kill -9 <进程ID>;
点击(此处)折叠或打开
- ALTER PROCEDURE <> COMPILE
3) 重新恢复Job
点击(此处)折叠或打开
-
EXEC DBMS_JOB.BROKEN(63， FALSE);
- COMMIT;