Hi friends… One of our 12.2 db we got below errors from alertlog:

ORA-00060: deadlock resolved; details in file /u01/app/oracle/diag/rdbms/db_name/DB_NAME/trace/DB_NAME_j001_10041.trc
ORA-12012: error on auto execute of job “SYS”.”ORA$AT_SA_SPC_SY_56337″
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at “SYS.DBMS_SPACE”, line 2741
ORA-06512: at “SYS.DBMS_HEAT_MAP_INTERNAL”, line 716
ORA-06512: at “SYS.DBMS_HEAT_MAP_INTERNAL”, line 1164
ORA-06512: at “SYS.DBMS_HEAT_MAP”, line 228
ORA-06512: at “SYS.DBMS_SPACE”, line 2747

In Deadlock trace file, PROCESS STATE section shows action name: ORA$AT_SA_SPC_SY_56337

Process global information:

service name: SYS$USERS
client details:
O/S info: user: oracle, term: UNKNOWN, ospid: 544
machine: abc.xxx.xxx program: oracle@abc.xxx.xxx(J001)
application name: DBMS_SCHEDULER, hash value=44787
action name: ORA$AT_SA_SPC_SY_56337, hash value=61673 <<<action name:ORA$AT_SA_SPC_SY_56337

This issue is identified as below bug:

Bug 24687075 – SPACE ADVISOR TASKS/JOBS HITTING DEADLOCKS WITH GATHER DB STATS JOBS
Fixed in version :20.1

So what is solution?

Solution 1 is; apply the Generic patch available for your database version, its already available for 12.2 and 18c.

You can also disable the space advisory job as workaround:

Solution 2 is; run below command as sysdba
SQL> EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(‘AUTO SPACE ADVISOR’,NULL, NULL);