alert 日志中的死锁错误
DEADLOCK DETECTED Performing diagnostic dump and signaling ORA-00060
Complete deadlock information is located in the trace file of process (pid: 18, osid: 36892, DIA0)
trace 文件显示如下
-------------------------------------------------------------------------------
HUNG PROCESS DIAGNOSTIC DUMP BEGIN:
dump requested by process (pid: 18, osid: 36892, DIA0)
trace file: u01/app/oracle/diag/rdbms/test/trace/test_dia0_36892.trc
-------------------------------------------------------------------------------
----- Current SQL Statement for this session (sql_id=gxm18860ab67a) -----
update tab$ set ts#=:2,file#=:3,block#=:4,bobj#=decode(:5,0,null,:5),tab#=decode(:6,0,null,:6),intcols=:7,kernelcols=:8,clucols=decode(:9,0,null,:9),audit$=:10,flags=:11,pctfree$=:12,pctused$=:13,initrans=:14,maxtrans=:15,rowcnt=:16,blkcnt=:17,empcnt=:18,avgspc=:19,chncnt=:20,avgrln=:21,analyzetime=:22,samplesize=:23,cols=:24,property=:25,degree=decode(:26,1,null,:26),instances=decode(:27,1,null,:27),dataobj#=:28,avgspc_flb=:29,flbcnt=:30,trigflag=:31,spare1=:32,spare2=decode(:33,0,null,:33),spare4=:34,spare6=:35,acdrflags=decode(:36,0,null,:36),acdrtsobj#=decode(:37,0,null,:37),acdrdefaulttime=:38,acdrrowtsintcol#=:39 where obj#=:1
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
object line object
handle number name
0x14e8a90d0 716 package body SYS.DBMS_HEAT_MAP_INTERNAL.HEAT_MAP_TOPN_TABLESPACES
0x14e8a90d0 1164 package body SYS.DBMS_HEAT_MAP_INTERNAL.AUTO_ADVISOR_HEATMAP_JOB
0x16e992608 228 package body SYS.DBMS_HEAT_MAP.AUTO_ADVISOR_HEATMAP_JOB
0x14ea1ba00 2747 package body SYS.DBMS_SPACE.AUTO_SPACE_ADVISOR_JOB_PROC
*** 2018-12-01T23:11:34.952336-05:00
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst1
ksdhng_dumpcb_hung_ call ksedst1
proc_int
ksdhng_action_execu ksdhng_dumpcb_hung_
ksuintract_exec_pen ksdhng_action_execu
ksuitr
这是由于以下未公开的bug引起的
Bug 24687075 SPACE ADVISOR TASKS/JOBS HITTING DEADLOCKS WITH GATHER DB STATS JOBS
解决方案
失败的job可以从下一次运行的自动维护作业中恢复正常。因此，如果不经常发生，可以安全地忽略。或者在修复bug之前，请禁用space advisory job作为解决方法
connect as sysdba
select CLIENT_NAME,STATUS from DBA_AUTOTASK_CLIENT;
EXEC DBMS_AUTO_TASK_ADMIN.DISABLE('AUTO SPACE ADVISOR',NULL,NULL);
select CLIENT_NAME,STATUS from DBA_AUTOTASK_CLIENT;
