今天在调度运行日志中出现了大量ORA-00054,经过排查，全部与TEMP_DAZK_ZSJL_YXX 临时表有关系，测试truncate也提示同样错误，delete全表却没有问题。
由于是正式库没有DBA权限，因此查询V$LOCKED_OBJECT表为空。
于是只能调整思路：
SELECT * FROM DBA_OBJECTS WHERE object_name = 'TEMP_DAZK_ZSJL_YXX'
可以根据其中LAST_DDL_TIME找到上次执行时间，根据此时间排查日志，发现如下SQL执行后没有结果
2023-09-27 20:41:07.738 [pool-23-thread-2] DEBUG c.w.d.e.business.check.dao.DazkDao.insertGzData 137- ==> Preparing: INSERT INTO TEMP_DAZK_ZSJL_YXX(PCH,YLJGDM,GRJBXXBSH,ZSJL,ZQSJL) SELECT A.YWRQ, A.YLJGDM, A.GRJBXXBSH, A.SL AS ZSJL,NVL(B.SL,0) AS ZQSJL FROM ( SELECT '20230101-20231231'AS YWRQ, A.YLJGDM, A.GRJBXXBSH, COUNT(1) AS SL FROM DA_GR_JBXX A WHERE A.XGBZ<>'3' AND (months_between(to_date('2023-12-31 23:59:59','yyyy-MM-dd hh24:mi:ss'),A.CSRQ)/12)>6 AND A.TBRQ >= TO_DATE('2023-01-01 00:00:00','yyyy-MM-dd hh24:mi:ss') AND A.TBRQ <= TO_DATE('2023-12-31 23:59:59','yyyy-MM-dd hh24:mi:ss') AND JDRQ <= to_date('2023-12-31 23:59:59','yyyy-MM-dd HH24:mi:ss') GROUP BY A.YLJGDM, A.GRJBXXBSH ) A LEFT JOIN (SELECT '20230101-20231231'AS YWRQ, A.YLJGDM, A.GRJBXXBSH, COUNT(1) AS SL FROM DA_GR_JBXX A WHERE A.XGBZ<>'3' AND (months_between(to_date('2023-12-31 23:59:59','yyyy-MM-dd hh24:mi:ss'),A.CSRQ)/12)>6 AND A.TBRQ >= TO_DATE('2023-01-01 00:00:00','yyyy-MM-dd hh24:mi:ss') AND A.TBRQ <= TO_DATE('2023-12-31 23:59:59','yyyy-MM-dd hh24:mi:ss') AND JDRQ <= to_date('2023-12-31 23:59:59','yyyy-MM-dd HH24:mi:ss') AND A.CSLBDM = custommapping(A.CSLBDM,'/', '1/2/3/4/5//9') GROUP BY A.YLJGDM, A.GRJBXXBSH) B ON A.YWRQ=B.YWRQ AND A.YLJGDM=B.YLJGDM AND A.GRJBXXBSH=B.GRJBXXBSH
2023-09-27 20:41:07.820 [pool-23-thread-2] DEBUG c.w.d.e.business.check.dao.DazkDao.insertGzData 137- ==> Parameters:
排查会话id
SELECT * FROM V$SESSION WHERE prev_exec_start <To_date('2023-9-27 20:43:08','yyyy-MM-dd HH24:mi:ss') and prev_exec_start > To_date('2023-9-27 20:40:08','yyyy-MM-dd HH24:mi:ss')
根据数据库、时间以及执行机器名称反复确认后，杀掉此进程，一切正常：
alter system kill session '2422,40267' IMMEDIATE;
网友评论