Query to find the job responsible for gather stats in Oracle 11g

Query to find the job responsible for stats in Oracle 11g


set linesize 200

set pagesize 50

col window_group format a20

col consumer_group format a30

col client_name format a35

col db_unique_name format a15

select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "RUNTIME", db_unique_name, 

       status, client_name, window_group, consumer_group 

from dba_autotask_client, v$database;

-------------------------------------------------------------------------------------------------------------

The job name for (Oracle's default) stats gathering job in 11g? 

select * from DBA_AUTOTASK_OPERATION;

-------------------------------------------------------------------------------------------------------------

Where can I see the time this job runs? how can I change it?

select * from DBA_AUTOTASK_SCHEDULE;
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE( 'WEEKNIGHT_WINDOW', 'repeat_interval',
'freq=daily;byday=MON, TUE, WED, THU, FRI;byhour=0;byminute=0;bysecond=0');

--------------------------------------------------------------------------------------------------------------

What is the job name for (Oracle's default) stats gathering job in 11g?

select job_name from dba_scheduler_jobs
where job_name like '%STAT%'
order by 1;


JOB_NAME
------------------------------
BSLN_MAINTAIN_STATS_JOB
MGMT_STATS_CONFIG_JOB

--------------------------------------------------------------------------------------------------------------

Query to check the status of the BSLN_MAINTAIN_STATS_JOB:

select log_date,status from dba_scheduler_job_run_details  where job_name=’BSLN_MAINTAIN_STATS_JOB’
order by log_date desc;

LOG_DATE                                                                    STATUS
———————————————————————— ————
08-JUL-12 01.30.01.224309 PM                                         FAILED
09-JUL-12 01.30.00.855555 PM                                         FAILED
10-JUL-12 01.30.00.761203 PM                                         FAILED
11-JU:L-12 01.30.00.583605 PM                                        FAILED
12-DEC-12 01.30.00.450731 PM                                        FAILED

 to Fix it Run

SQL> @?/rdbms/admin/catnsnmp.sql 
SQL> @?/rdbms/admin/catsnmp.sql


-------------------------------------------------------------------------------------------------------------