Oracle dbms_scheduler, enable, disable and drop Jobs Step by Step

 1. Executing a stored procedure:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'PROCESS_DATA_JOB',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN process_data; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; INTERVAL=1', -- Run daily
    enabled         => TRUE
  );
END;
/

2. Executing Anonymous PL/SQL Block:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'ANONYMOUS_BLOCK_JOB',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN /* Your PL/SQL logic here */ NULL; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=HOURLY; INTERVAL=2', -- Run every 2 hours
    enabled         => TRUE
  );
END;
/

3. Running SQL Statements:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'SQL_STATEMENT_JOB',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN
              EXECUTE IMMEDIATE ''DELETE FROM employees WHERE hire_date <TRUNC(SYSDATE) - 365'';
               END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=WEEKLY; INTERVAL=1', -- Run weekly
    enabled         => TRUE
  );
END;
/

4. Running OS Commands:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'OS_COMMAND_JOB',
    job_type        => 'EXECUTABLE',
    job_action      => '/usr/bin/my_script.sh', -- Replace with your actual script path
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; INTERVAL=1', -- Run daily
    enabled         => TRUE
  );
END;
/

-- Create the job

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'MY_JOB',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN /* Your PL/SQL logic here */ NULL; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; INTERVAL=1', -- Run daily
    enabled         => TRUE
  );
END;
/

-- Disable the job

BEGIN
  DBMS_SCHEDULER.DISABLE('MY_JOB');
END;
/

-- Enable the job again

BEGIN

  DBMS_SCHEDULER.ENABLE('MY_JOB');

END;

/

SELECT job_name, enabled, state

FROM user_scheduler_jobs; -- Use DBA_SCHEDULER_JOBS or ALL_SCHEDULER_JOBS for all jobs in the database

1. Executing a stored procedure:

BEGIN

  DBMS_SCHEDULER.CREATE_JOB (

    job_name        => 'PROCESS_DATA_JOB',

    job_type        => 'PLSQL_BLOCK',

    job_action      => 'BEGIN process_data; END;',

    start_date      => SYSTIMESTAMP,

    repeat_interval => 'FREQ=DAILY; INTERVAL=1', -- Run daily

    enabled         => TRUE

  );

END;

/

2. Executing Anonymous PL/SQL Block:

BEGIN

  DBMS_SCHEDULER.CREATE_JOB (

    job_name        => 'ANONYMOUS_BLOCK_JOB',

    job_type        => 'PLSQL_BLOCK',

    job_action      => 'BEGIN /* Your PL/SQL logic here */ NULL; END;',

    start_date      => SYSTIMESTAMP,

    repeat_interval => 'FREQ=HOURLY; INTERVAL=2', -- Run every 2 hours

    enabled         => TRUE

  );

END;

/

3. Running SQL Statements:

BEGIN

  DBMS_SCHEDULER.CREATE_JOB (

    job_name        => 'SQL_STATEMENT_JOB',

    job_type        => 'PLSQL_BLOCK',

    job_action      => 'BEGIN

                         EXECUTE IMMEDIATE ''DELETE FROM employees WHERE hire_date < TRUNC(SYSDATE) - 365'';

                       END;',

    start_date      => SYSTIMESTAMP,

    repeat_interval => 'FREQ=WEEKLY; INTERVAL=1', -- Run weekly

    enabled         => TRUE

  );

END;

/

4. Running OS Commands:

BEGIN

  DBMS_SCHEDULER.CREATE_JOB (

    job_name        => 'OS_COMMAND_JOB',

    job_type        => 'EXECUTABLE',

    job_action      => '/usr/bin/my_script.sh', -- Replace with your actual script path

    start_date      => SYSTIMESTAMP,

    repeat_interval => 'FREQ=DAILY; INTERVAL=1', -- Run daily

    enabled         => TRUE

  );

END;

/

-- Create the job

BEGIN

  DBMS_SCHEDULER.CREATE_JOB (

    job_name        => 'MY_JOB',

    job_type        => 'PLSQL_BLOCK',

    job_action      => 'BEGIN /* Your PL/SQL logic here */ NULL; END;',

    start_date      => SYSTIMESTAMP,

    repeat_interval => 'FREQ=DAILY; INTERVAL=1', -- Run daily

    enabled         => TRUE

  );

END;

/

5. Run job secondly 

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'SQL_STATEMENT_JOB',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN
              EXECUTE IMMEDIATE ''DELETE FROM employees WHERE hire_date <TRUNC(SYSDATE) - 365'';
               END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=SECONDLY; INTERVAL=1', -- Run weekly
    enabled         => TRUE
  );
END;
/

6. Run job minutely 

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'SQL_STATEMENT_JOB',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN
              EXECUTE IMMEDIATE ''DELETE FROM employees WHERE hire_date <TRUNC(SYSDATE) - 365'';
               END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=MINUTELY ; INTERVAL=1', -- Run weekly
    enabled         => TRUE
  );
END;
/

-- Disable the job

BEGIN

  DBMS_SCHEDULER.DISABLE('&job_name');

END;

/

-- Enable the job again

BEGIN

  DBMS_SCHEDULER.ENABLE('&job_name');

END;

/

--- Drop a Job 

BEGIN

  DBMS_SCHEDULER.DROP_JOB(

    job_name => '&job_name',

    force    => FALSE,

    defer    => FALSE

  );

END;

/

SELECT job_name, enabled, state

FROM dba_scheduler_jobs where job_name='&job_name'; -- Use DBA_SCHEDULER_JOBS or ALL_SCHEDULER_JOBS for all jobs in the database

select * from all_scheduler_running_jobs;

select job,what,broken from dba_jobs;

select job_name,owner,enabled from dba_scheduler_jobs;

disable job:-

execute dbms_scheduler.disable('owner.job');

enable job:-

execute dbms_scheduler.enable('owner.job');

drop job:-

execute dbms_scheduler.drop_job('owner.job');