stats collection job using dbms_scheduler

set servoutoutput on

BEGIN
     DBMS_SCHEDULER.CREATE_JOB (
          job_name => 'RAJ_STATS_REFRESH'
          ,job_type => 'PLSQL_BLOCK'
          ,job_action => 'Begin dbms_stats.gather_schema_stats(ownname => ''RAJ'', cascade => true); end;'
          ,start_date => '30-JAN-19 10.00.00PM US/Pacific'
          ,repeat_interval => 'FREQ=DAILY; INTERVAL=1'
          ,enabled => TRUE
          ,comments => 'Refreshes the RAJ Schema stats every night at 10 PM'
          );
END;
/


col JOB_NAME form a30
col STATE form a10
col SOURCE form a5

SELECT JOB_NAME,STATE,LAST_START_DATE,LAST_RUN_DURATION, NEXT_RUN_DATE FROM DBA_SCHEDULER_JOBS  WHERE JOB_NAME = 'RAJ';

select JOB_NAME, FAILURE_COUNT, LAST_START_DATE, LAST_RUN_DURATION from dba_scheduler_jobs WHERE JOB_NAME = 'RAJ';

SELECT JOB_NAME FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'RAJ';

--select * from dba_scheduler_job_run_details where job_name = 'RAJ';

col status form a10
col ACTUAL_START_DATE form a40
col RUN_DURATION form a15
select JOB_NAME, STATUS,  ERROR#, ACTUAL_START_DATE, RUN_DURATION from dba_scheduler_job_run_details where job_name ='RAJ';

SELECT JOB_NAME,STATE,LAST_START_DATE,LAST_RUN_DURATION, NEXT_RUN_DATE FROM DBA_SCHEDULER_JOBS  WHERE JOB_NAME = 'RAJ';

JOB_NAME                       STATE      LAST_START_DATE                                                             LAST_RUN_DURATION
------------------------------ ---------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
NEXT_RUN_DATE
---------------------------------------------------------------------------
RAJ        RUNNING    03-FEB-19 12.55.00.156811 PM US/PACIFIC
03-FEB-19 12.55.00.100000 PM US/PACIFIC

--- Rollback:


> conn / as sysdba
Connected.
12:21:46 SYS@RAJ> BEGIN
12:21:51   2  DBMS_SCHEDULER.DROP_JOB( JOB_NAME => 'RAJ');
12:21:56   3   END;
12:21:59   4  /

PL/SQL procedure successfully completed.