Where is auto gather stats job listed in DBA_SCHEDULER_JOBS view ?
DB version:19c
OS: Oracle Linux 7.9
In my pluggable DB, I can see that the auto gather stats job is enabled.
But, I don't see it being listed in dba_scheduler_jobs view (like PROGRAM_NAME column). Why is that ?
SQL> alter session set container = SALES_PROD;
Session altered.
col CLIENT_NAME format a35
col TASK_NAME format a25
col PROGRAM_ACTION format a45
set lines 200
select ac.client_name,ac.status,
at.task_name,
sp.program_action
from dba_autotask_client ac,
dba_autotask_task at,
dba_scheduler_programs sp
where ac.client_name='auto optimizer stats collection'
and ac.client_name=at.client_name
and upper(at.task_name)=upper(sp.program_name);
CLIENT_NAME STATUS TASK_NAME PROGRAM_ACTION
----------------------------------- -------- ------------------------- ---------------------------------------------
auto optimizer stats collection ENABLED gather_stats_prog dbms_stats.gather_database_stats_job_proc
SQL> select PROGRAM_TYPE, ENABLED from dba_scheduler_programs where PROGRAM_NAME = 'GATHER_STATS_PROG';
PROGRAM_TYPE ENABL
---------------- -----
STORED_PROCEDURE TRUE
col job_Name for a28
col program_Name for a30
col Job_Action for a14
col Job_Enabled for a8
select dsj.job_name as job_Name,
dsj.program_name as program_Name,
trim(substr(dsj.job_action, 1,10)) as Job_Action, -- Newlines cause unreadable output, hence trim+substr
dsj.enabled as Job_Enabled
from dba_scheduler_jobs dsj;
JOB_NAME PROGRAM_NAME JOB_ACTION JOB_ENAB
---------------------------- ------------------------------ -------------- --------
PURGE_LOG PURGE_LOG_PROG TRUE
FILE_WATCHER FILE_WATCHER_PROGRAM FALSE
PMO_DEFERRED_GIDX_MAINT_JOB PMO_DEFERRED_GIDX_MAINT TRUE
CLEANUP_NON_EXIST_OBJ declare TRUE
CLEANUP_ONLINE_IND_BUILD declare TRUE
CLEANUP_TAB_IOT_PMO declare TRUE
CLEANUP_TRANSIENT_TYPE declare TRUE
CLEANUP_TRANSIENT_PKG declare TRUE
CLEANUP_ONLINE_PMO declare TRUE
FILE_SIZE_UPD declare TRUE
ORA$AUTOTASK_CLEAN ORA$AGE_AUTOTASK_DATA TRUE
HM_CREATE_OFFLINE_DICTIONARY dbms_hm.cr FALSE
DRA_REEVALUATE_OPEN_FAILURES dbms_ir.re TRUE
ORA$PREPLUGIN_BACKUP_JOB ORA$PREPLUGIN_BACKUP_PRG FALSE
BSLN_MAINTAIN_STATS_JOB BSLN_MAINTAIN_STATS_PROG TRUE
FGR$AUTOPURGE_JOB sys.dbms_f FALSE
RSE$CLEAN_RECOVERABLE_SCRIPT sys.dbms_s TRUE
SM$CLEAN_AUTO_SPLIT_MERGE sys.dbms_s TRUE
LOAD_OPATCH_INVENTORY BEGIN dbms FALSE
XMLDB_NFS_CLEANUP_JOB xdb.dbms_x FALSE
DELETE_INMEMORY_JOB1 DELETE_INSTANCES_AUTO_PRG FALSE
DELETE_INSTANCES_AUTO_JOB1 DELETE_INSTANCES_AUTO_PRG TRUE
DELETE_AWR_JOB1 DELETE_OTHERS_PRG FALSE
DELETE_INSTANCES_AUTO_JOB2 DELETE_INSTANCES_AUTO_PRG FALSE
DELETE_HC_JOB1 DELETE_OTHERS_PRG FALSE
SUBMIT_MIGRATION_JOBS_JOB SUBMIT_MIGRATION_JOBS_PRG FALSE
MIGRATE_SOA_METADATA_JOB MIGRATE_SOA_METADATA_PRG FALSE
CONTROL_MIGRATION_MAIN_JOB CONTROL_MIGRATION_MAIN_PRG FALSE
WMS_PURGING_JOB begin SOA2 TRUE
DELETE_COMP_BASED_JOB1 DELETE_INSTANCES_AUTO_PRG FALSE
30 rows selected.