To check the job status in 10g:
select * from dba_scheduler_jobs where job_name = 'GATHER_STATS_JOB';
select * from dba_scheduler_job_run_details where job_name = 'GATHER_STATS_JOB';
select * from dba_scheduler_job_log where job_name = 'GATHER_STATS_JOB';
To Check the scheduler for this job:
select OWNER,JOB_NAME,STATE,SCHEDULE_NAME from dba_scheduler_jobs where job_name='GATHER_STATS_JOB';
SELECT * FROM DBA_SCHEDULER_WINGROUP_MEMBERS;If the state is not "Scheduled":
EXEC DBMS_SCHEDULER.ENABLE ('GATHER_STATS_JOB');
if the window is not enabled:
EXEC DBMS_SCHEDULER.ENABLE ('SYS.WINDOWNAME');
You can also create the job on different user and execute it:
BEGIN
dbms_scheduler.create_Job (job_name=>'Gather_Stats_Job',
job_type=>'PLSQL_BLOCK',
job_action=>'BEGIN Dbms_Stats.gather_database_stats(cascade=>true); end;',
start_date => sysdate,
repeat_interval => 'freq=daily',
enabled=>true);
end;
/
BEGIN
DBMS_SCHEDULER.RUN_JOB(
job_name => 'GATHER_STATS_JOB'
);
END;
/
To disable gather_stats_job in 10g:
BEGIN
DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
END;
/
To drop the job, login as creator and run:EXEC DBMS_SCHEDULER.DROP_JOB (JOB_NAME=>'GATHER_STATS_JOB', force => true);