Creating scheduler jobs in oracle database

Take for example you are trying to run database stats every weekly on the whole database. 

You can do it either by a shell script and running it in Crontab

or

You can use DBMS_SCHEDULER package to create a scheduler job

Here i show how to use dbms_scheduler and setup stats job.

First create a procedure in the schema you are trying to run the job from

 CREATE OR REPLACE procedure SYS.Manual_full_db_stats as  
 BEGIN  
     SYS.DBMS_STATS.GATHER_DATABASE_STATS ( Granularity => 'DEFAULT' ,OPTIONS => 'GATHER' ,Gather_Sys => FALSE ,Estimate_Percent => 10 ,Method_Opt => 'FOR ALL COLUMNS SIZE 1' ,Degree => 6 ,CASCADE => TRUE ,No_Invalidate => FALSE);  
 END;  
 /  

Once the procedure is created you can create a scheduler job.. If you want to use plsql block instead of a procedure just modify the below code and create the job

    ,job_type    => 'PLSQL_BLOCK'  
    ,job_action   => 'BEGIN   
  SYS.MANUAL_FULL_DB_STATS;  
  COMMIT;   
 END;   
 '  

Main code

 BEGIN  
  SYS.DBMS_SCHEDULER.CREATE_JOB  
   (  
     job_name    => 'SYS.MANUAL_STATS_JOB'  
    ,start_date   => TO_TIMESTAMP_TZ('2016/08/20 14:00:00.000000 America/Los_Angeles','yyyy/mm/dd hh24:mi:ss.ff tzr')  
    ,repeat_interval => 'FREQ=WEEKLY;INTERVAL=1'  
    ,end_date    => NULL  
    ,job_class    => 'DEFAULT_JOB_CLASS'  
    ,job_type    => 'STORED_PROCEDURE'  
    ,job_action   => 'SYS.MANUAL_FULL_DB_STATS'  
    ,comments    => NULL  
   );  
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE  
   ( name   => 'SYS.MANUAL_STATS_JOB'  
    ,attribute => 'RESTARTABLE'  
    ,value   => FALSE);  
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE  
   ( name   => 'SYS.MANUAL_STATS_JOB'  
    ,attribute => 'LOGGING_LEVEL'  
    ,value   => SYS.DBMS_SCHEDULER.LOGGING_OFF);  
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL  
   ( name   => 'SYS.MANUAL_STATS_JOB'  
    ,attribute => 'MAX_FAILURES');  
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL  
   ( name   => 'SYS.MANUAL_STATS_JOB'  
    ,attribute => 'MAX_RUNS');  
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE  
   ( name   => 'SYS.MANUAL_STATS_JOB'  
    ,attribute => 'STOP_ON_WINDOW_CLOSE'  
    ,value   => FALSE);  
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE  
   ( name   => 'SYS.MANUAL_STATS_JOB'  
    ,attribute => 'JOB_PRIORITY'  
    ,value   => 3);  
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL  
   ( name   => 'SYS.MANUAL_STATS_JOB'  
    ,attribute => 'SCHEDULE_LIMIT');  
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE  
   ( name   => 'SYS.MANUAL_STATS_JOB'  
    ,attribute => 'AUTO_DROP'  
    ,value   => FALSE);  
  SYS.DBMS_SCHEDULER.ENABLE  
   (name         => 'SYS.MANUAL_STATS_JOB');  
 END;  
 /  

PS: Some time you may get error saying job not found or insufficient privileges

 ORA-20000: Insufficient privileges to analyze an object in Schema

 For this you need to give the grants to the user who is running it..

 grant ANALYZE ANY to username;
 grant SELECT ANY TABLE to username;