Cacher is the code snippet organizer for pro developers

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name             => 'gather_schema',
   job_type             => 'PLSQL_BLOCK',
   job_action           => 'BEGIN dbms_stats.gather_schema_stats(ownname=>''DBA_FID'', estimate_percent=> 10, method_opt=> ''for all columns size auto'',degree=> 1); END;',
   start_date           =>  SYSTIMESTAMP,
   repeat_interval      => 'FREQ=DAILY;byhour=03;byminute=0;', 
   end_date             =>  NULL,
   enabled              =>  TRUE,
   comments             => 'Gather schema DBA_FID tous les jours a 3h');
END;
/

# Attention, il s'agit de 2 simple quote et non une double quote autour de DBA_FID

CREATE OR REPLACE PROCEDURE SYS.REBUILD_TABLE_INDEX
AS
SQL_STR VARCHAR2(1000);
BEGIN

    FOR CUROSR_INDEX IN (SELECT OWNER,INDEX_NAME from dba_indexes
        WHERE OWNER='DBA_FID'
        ORDER BY INDEX_NAME ) LOOP

        BEGIN
        SQL_STR:='ALTER INDEX '||CUROSR_INDEX.OWNER||'.'||CUROSR_INDEX.INDEX_NAME ||' REBUILD NOLOGGING';        
        EXECUTE IMMEDIATE SQL_STR;
        DBMS_OUTPUT.PUT_LINE('SUCCESS : '|| CUROSR_INDEX.OWNER||'.'||CUROSR_INDEX.INDEX_NAME);
        EXCEPTION WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('ERROR : '||CUROSR_INDEX.OWNER||'.'||CUROSR_INDEX.INDEX_NAME||':'||SQLERRM);
        END;
 
END LOOP;
END;
/

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name             => 'REBUILD_INDEX',
   job_type             => 'STORED_PROCEDURE',
   job_action           => 'REBUILD_TABLE_INDEX',
   start_date           =>  SYSTIMESTAMP,
   repeat_interval      => 'FREQ=DAILY;byday=MON;byhour=04;byminute=0;', 
   end_date             =>  NULL,
   enabled              =>  TRUE,
   comments             => 'Rebuild index DBA_FID tous les lundi a 4h');
END;
/

////////////////////////////
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name             => 'REBUILD_INDEX',
   job_type             => 'STORED_PROCEDURE',
   job_action           => 'REBUILD_TABLE_INDEX',
   start_date           =>  NULL,
   repeat_interval      => 'FREQ=MONTHLY;INTERVAL=6;byday=1MON;byhour=04;byminute=0;', 
   end_date             =>  NULL,
   enabled              =>  TRUE,
   comments             => 'Rebuild index DBA_FID tous les 6 mois le 1er lundi du mois a 4h');
END;
/
////////////////////////////

BEGIN
  dbms_scheduler.drop_job(job_name => 'REBUILD_INDEX');
END;
/


executer le job manuellement :
execute dbms_scheduler.run_job('REBUILD_INDEX');

SELECT OPERATION, JOB_NAME, STATUS FROM DBA_OPTSTAT_OPERATIONS ORDER BY START_TIME;

SELECT job_name, log_date, status, actual_start_date, run_duration, cpu_used FROM dba_scheduler_job_run_details where job_name like '%GATHER%';

set lines 200
col job_name format a30
col REPEAT_INTERVAL format a50
col LAST_START_DATE format a50
col NEXT_START_DATE format a50
select job_name, REPEAT_INTERVAL, LAST_START_DATE,NEXT_START_DATE, state from all_scheduler_jobs;

execute dbms_scheduler.enable('SYS.GATHER_SCHEMA');
execute dbms_scheduler.disable('SYS.GATHER_SCHEMA');