09yc39d25z29y Query Causing Core Dumps And ORA-7445 On Target Database (Doc ID 2449667.1)

Last updated on JUNE 20, 2023

Applies to:

Symptoms

Query 09yc39d25z29y is causing core dumps and ORA-7445 on target database:

with latest_run as
(
SELECT all_runs.CON_ID,
all_runs.OWNER,
all_runs.JOB_NAME,
all_runs.STATUS
FROM CDB_SCHEDULER_JOB_RUN_DETAILS all_runs,
(SELECT CON_ID,
OWNER,
JOB_NAME,
MAX(ACTUAL_START_DATE) AS START_DATE
FROM CDB_SCHEDULER_JOB_RUN_DETAILS
GROUP BY CON_ID,OWNER,JOB_NAME) latest_runs
WHERE latest_runs.CON_ID =all_runs.CON_ID
AND latest_runs.OWNER=all_runs.OWNER
AND latest_runs.JOB_NAME=all_runs.JOB_NAME
AND all_runs.ACTUAL_START_DATE= latest_runs.START_DATE
)
SELECT pdb_name, SUM(broken), SUM(failed)
FROM (SELECT c.name pdb_name,
DECODE(broken, 'N', 0, 1) broken,
DECODE(NVL(failures,0), 0, 0, 1) failed
FROM cdb_jobs j, v$containers c
WHERE j.con_id = TO_NUMBER(c.con_id)
UNION ALL
SELECT c.name pdb_name,
DECODE(failed_details.STATE, 'BROKEN', 1, 0) broken,
DECODE(failed_details.STATUS , 'FAILED', DECODE(failed_details.STATE,'BROKEN',0,'DISABLED',0,1), 0) failed
FROM v$containers c,
(SELECT all_jobs.CON_ID,
all_jobs.OWNER,
all_jobs.JOB_NAME,
latest_run.STATUS,
all_jobs.STATE
FROM CDB_SCHEDULER_JOBS all_jobs,latest_run
WHERE latest_run.CON_ID=all_jobs.CON_ID
AND latest_run.OWNER=all_jobs.OWNER
AND latest_run.JOB_NAME=all_jobs.JOB_NAME
) failed_details
WHERE failed_details.con_id = TO_NUMBER(c.con_id)
)
GROUP BY pdb_name

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.