Scheduler job oracle db

BEGIN
  SYS.DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => 'schema.JOB'
      ,start_date      => TO_TIMESTAMP_TZ('2017/06/07 15:00:00.000000 +07:00','yyyy/mm/dd hh24:mi:ss.ff tzr')
      ,repeat_interval => 'FREQ=MINUTELY;INTERVAL=10'
      ,end_date        => NULL
      ,job_class       => 'DEFAULT_JOB_CLASS'
      ,job_type        => 'STORED_PROCEDURE'
      ,job_action      => 'shema.proc_name'
      ,comments        => 'ITSM xxxx'
    );
    END;
    /

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;

Automatic Statistics and maintenance

For checking status of auto task operations like gathering stats

SELECT client_name, status FROM dba_autotask_operation;

CLIENT_NAME                                                      STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection                                  ENABLED
auto space advisor                                               ENABLED
sql tuning advisor                                               ENABLED


For schedules
SELECT * FROM dba_autotask_schedule;

WINDOW_NAME          START_TIME    DURATION  
-------------------- ------------- -----------
MONDAY_WINDOW        08/02/2010 22:00:00,900000000 EUROPE/ATHENS 0 4:0:0.0
MONDAY_WINDOW        15/02/2010 22:00:00,900000000 EUROPE/ATHENS 0 4:0:0.0
MONDAY_WINDOW        22/02/2010 22:00:00,900000000 EUROPE/ATHENS 0 4:0:0.0
...
SUNDAY_WINDOW        21/02/2010 06:00:00,900000000 EUROPE/ATHENS 0 20:0:0.0
SUNDAY_WINDOW        28/02/2010 06:00:00,900000000 EUROPE/ATHENS 0 20:0:0.0

You can disable any of the tasks by using the DBMS_AUTO_TASK_ADMIN package. If you want to disable all the automated tasks, issue the following command::
Oracle PL/SQL


EXEC dbms_auto_task_admin.disable;
If you want to enable all the automated tasks, issue the following command:
Oracle PL/SQL


EXEC dbms_auto_task_admin.enable;
You can also disable (or enable) a specific task. For example, you can issue the following command to disable “gather_stats_prog” task:
Oracle PL/SQL

BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/


SELECT * FROM DBA_AUTOTASK_JOB_HISTORY

for job history

SELECT client_name, status FROM dba_autotask_operation;

CLIENT_NAME                                                      STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection                                  DISABLED
auto space advisor                                               ENABLED
sql tuning advisor                                               ENABLED

EXEC dbms_auto_task_admin.enable( 'auto optimizer stats collection',
NULL, NULL );

SELECT client_name, status FROM dba_autotask_operation;

CLIENT_NAME                                                      STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection                                  ENABLED
auto space advisor                                               ENABLED
sql tuning advisor                                               ENABLED


You can also use the “Automated Maintenance Tasks” screen of Enterprise Manager to manage these automated tasks.For disabling tasks

Begin
dbms_stats.gather_schema_stats ('SYS');
end;
/
begin
dbms_stats.gather_dictionary_stats;
dbms_stats.gather_fixed_objects_stats;
end;
/


SELECT 'exec dbms_stats.gather_table_stats( ownname => '''||OWNER||''', tabname => '''||TABLE_NAME||''', degree => 6) ;'
  FROM dba_tables
 WHERE owner not in ('ANONYMOUS','APPQOSSYS','CTXSYS','DBSNMP','DIP','DMSYS','EXFSYS','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OUTLN','SCOTT','SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','SQLTXADMIN','SQLTXPLAIN','SYS','SYSMAN','SYSTEM','TOAD','WMSYS','XDB','XS$NULL')
ORDER BY 1;



'CAARS_DM_USER','DM_USER','DW_USER','ETL','HARTS_DM_USER','HDD_DM_USER','HMA_CONTROL','HMA_CRM_USER','HMA_DM_INCENTIVES','HMA_DM_MARKETING','HMA_DM_SALES','HMA_DM_SERVICE','HMA_DM_SSBI',','HMA_LOOKUP','HMA_ODS','HMA_PROD','HMA_STAGE','HMA_STG_MV_USER','INCENT_DM_USER','LMRS_DM_USER','MKTG_DM_USER','QIS_DM_USER','SALES_DM_USER','SCORE_DM_USER','SED_DM_USER','SSBI_DM_USER','VOC_DM_USER','WIS_DM_USER'


SELECT 'exec dbms_stats.gather_schema_stats( ownname => '''||USERNAME||''') ;' FROM dba_users
 WHERE USERNAME  in ('CAARS_DM_USER','DM_USER','DW_USER','ETL','HARTS_DM_USER','HDD_DM_USER','HMA_CONTROL','HMA_CRM_USER','HMA_DM_INCENTIVES','HMA_DM_MARKETING','HMA_DM_SALES','HMA_DM_SERVICE','HMA_DM_SSBI','HMA_LOOKUP','HMA_ODS','HMA_PROD','HMA_STAGE','HMA_STG_MV_USER','INCENT_DM_USER','LMRS_DM_USER','MKTG_DM_USER','QIS_DM_USER','SALES_DM_USER','SCORE_DM_USER','SED_DM_USER','SSBI_DM_USER','VOC_DM_USER','WIS_DM_USER'
)
ORDER BY 1;


exec dbms_stats.gather_schema_stats( ownname => 'OPACES') ;