Oracle Job Scheduler Commands for Task Automation

6 min read

ADVERTISEMENT

The Oracle Job Scheduler is a powerful tool that allows you to automate database tasks, helping you save time and ensure that repetitive or scheduled operations are performed consistently. Here’s a more detailed breakdown of how it works:

1. Schedules

A schedule in Oracle Job Scheduler defines when a job should run. It includes details such as:

  • Start Date: The date and time when the job should first execute.
  • Repeat Interval: The frequency at which the job should repeat (e.g., daily, weekly, monthly).
  • End Date: The date and time when the job should stop running (optional).

For example, you can set a job to run every day at 8:00 AM or every first day of the month.

2. Programs

A program is a logical unit that defines what action a job will perform. It specifies:

  • The type of operation to be performed (such as executing a stored procedure, calling a script, etc.).
  • The name of the stored procedure, script, or package that will be executed when the job runs.
  • The arguments for the program, if needed.

Programs can be created using PL/SQL or external scripts. For example, a program could be created to run a stored procedure like BILLING_PROC for generating bills.

3. Jobs

A job is the actual entity that links a schedule and a program together. It tells Oracle which program to execute according to a specific schedule. Jobs can also be manually triggered or stopped if needed. You can:

  • Create a job that links to a specific schedule and program.
  • Enable or disable a job as per the requirement.

4. Key Benefits of Using Job Scheduler

  • Automation: It allows tasks to run automatically based on the defined schedules, reducing manual intervention.
  • Consistency: Ensures that recurring tasks are executed on time without relying on human action.
  • Resource Management: You can set up jobs to run during off-peak hours, ensuring they don’t interfere with regular database operations.
  • Flexibility: You can run simple tasks like backups or complex jobs involving multiple PL/SQL procedures or scripts.

Example Use Cases:

  • Daily Billing Jobs: Automatically run billing procedures at 8 AM every day.
  • Database Cleanup: Set up jobs to archive old data or clean up logs at regular intervals.
  • Backup Jobs: Automate backups that run during off-peak hours to ensure the system remains available during peak hours.

In summary, the Oracle Job Scheduler provides a flexible, scalable solution to automate repetitive database tasks, manage workloads, and ensure critical operations are executed without manual intervention.

1. Create a Schedule

BEGIN
   DBMS_SCHEDULER.CREATE_SCHEDULE (
      Schedule_name    => 'DAILYREPORTGENERATION',
      Start_date       => SYSTIMESTAMP,
      Repeat_interval  => 'FREQ=DAILY;BYHOUR=11;BYMINUTE=30',
      Comments         => 'DAILY REPORT GENERATION'
   );
END;

2. Create a Program

BEGIN
   DBMS_SCHEDULER.CREATE_PROGRAM (
      program_name        => 'DAILYREPORTGENERATION',
      program_type        => 'STORED_PROCEDURE',
      program_action      => 'DAILYJOB.REPORTGENPROC',
      number_of_arguments => 0,
      enabled             => TRUE,
      comments            => 'DAILY REPORT GENERATION'
   );
END;

3. Create a Job

BEGIN
   DBMS_SCHEDULER.CREATE_JOB (
      job_name        => 'DAILYREPORTGEN_RUN',
      program_name    => 'DAILYREPORTGENERATION',
      schedule_name   => 'DAILYREPORTGEN_SCHED',
      enabled         => FALSE,
      comments        => 'Daily report generation job'
   );
END;

4. Create Scheduler Job (Direct)

BEGIN
   DBMS_SCHEDULER.CREATE_JOB (
      job_name          => '"MY_SCHEMA"."MV_REF_REPORT_DATA"',
      job_type          => 'PLSQL_BLOCK',
      job_action        => 'dbms_refresh.refresh(''"MY_SCHEMA"."STC_NEXT_REPORT_MV_DATA"'');',
      number_of_arguments => 0,
      start_date        => NULL,
      repeat_interval   => 'FREQ=DAILY;BYHOUR=8;BYMINUTE=00;BYSECOND=00',
      end_date          => NULL,
      enabled           => FALSE,
      auto_drop         => FALSE,
      comments          => 'Converted report jobs'
   );
   DBMS_SCHEDULER.enable(
      name => '"MY_SCHEMA"."MV_REF_REPORT_DATA"'
   );
END;

5. View Schedule Details

SET COLSEP ' | '
SET LINESIZE 120
SET PAGESIZE 50
SET FEEDBACK ON
SET HEADING ON

COLUMN schedule_name FORMAT A30
COLUMN schedule_type FORMAT A20
COLUMN start_date FORMAT A25
COLUMN repeat_interval FORMAT A40

SELECT schedule_name, schedule_type, start_date, repeat_interval
FROM dba_scheduler_schedules;

6. Enable a Job

EXECUTE DBMS_SCHEDULER.ENABLE('SCOTT.MONTHLYREPORT');

7. Disable a Job

EXECUTE DBMS_SCHEDULER.DISABLE('SCOTT.MONTHLYREPORT');

8. Stop a Running Job

EXECUTE DBMS_SCHEDULER.STOP_JOB('SCOTT.MONTHLYREPORT');

9. Drop a Running Job

EXECUTE DBMS_SCHEDULER.DROP_JOB('SCOTT.MONTHLYREPORT');

10. Run a Job Immediately

EXECUTE DBMS_SCHEDULER.RUN_JOB('SCOTT.MONTHLYREPORT');

11. Drop a Schedule

BEGIN
   DBMS_SCHEDULER.DROP_SCHEDULE(
      schedule_name => 'DAILYREPORTGEN_SCHED',
      force => TRUE
   );
END;

12. Drop a Scheduler Job

DBMS_SCHEDULER.DROP_JOB (job_name => 'SCOTT.MONTHLYREPORT');

13. Create Scheduler Job with Shell Script (Oracle 12c onwards)

BEGIN
   dbms_credential.create_credential (
      CREDENTIAL_NAME => 'MY_OS_USER',
      USERNAME => 'oracle',
      PASSWORD => 'oracle@123',
      ENABLED => true
   );
END;

EXEC DBMS_SCHEDULER.create_job (
   job_name => 'my_script_task',
   job_type => 'external_script',
   job_action => '/export/home/oracle/test.sh',
   enabled => true,
   START_DATE => sysdate,
   REPEAT_INTERVAL => 'FREQ=MINUTELY;byminute=1',
   auto_drop => false,
   credential_name => 'MY_OS_USER'
);

14. Monitor Scheduler Jobs

SET COLSEP ' | '
SET LINESIZE 120
SET PAGESIZE 50
SET FEEDBACK ON
SET HEADING ON
COLUMN job_name FORMAT A30
COLUMN session_id FORMAT A10
COLUMN running_instance FORMAT A10
COLUMN elapsed_time FORMAT A15
SELECT job_name, session_id, running_instance, elapsed_time
FROM dba_scheduler_running_jobs;
SELECT * FROM DBA_SCHEDULER_JOB_RUN_DETAILS;
SELECT * FROM DBA_SCHEDULER_JOB_LOG;

15. Get DDL of a Scheduler Job

SET LONG 10000
SELECT dbms_metadata.get_ddl('PROCEDURE', 'DUP_ACC', 'SCOTT') FROM dual;

16. Copy Scheduler Job from One User to Another

EXEC DBMS_SCHEDULER.copy_job('SCOTT.MY_JOB_2','W3BUDDY.MY_JOB_2');

17. Get Log Information of Scheduler Jobs

SET PAGESIZE 299
SET LINESIZE 299
COL job_name FOR A24
COL log_date FOR A40
COL operation FOR A19
COL additional_info FOR A79
SELECT job_name, log_date, status, OPERATION, ADDITIONAL_INFO
FROM dba_scheduler_job_log
ORDER BY log_date DESC;

18. History of All Scheduler Job Runs

SET PAGESIZE 299
SET LINESIZE 299
COL job_name FOR A24
COL actual_start_date FOR A56
COL run_duration FOR A34
SELECT job_name, status, actual_start_date, run_duration
FROM DBA_SCHEDULER_JOB_RUN_DETAILS
ORDER BY ACTUAL_START_DATE DESC;

19. Managing Scheduler Credentials

BEGIN
   dbms_credential.create_credential (
      CREDENTIAL_NAME => 'ORACLEOSUSER',
      USERNAME => 'oracle',
      PASSWORD => 'oracle@123',
      ENABLED => true
   );
END;
EXEC DBMS_SCHEDULER.drop_credential('ORACLEOSUSER');
SELECT owner, CREDENTIAL_NAME, USERNAME, ENABLED
FROM DBA_CREDENTIALS;

EXEC DBMS_SCHEDULER.SET_ATTRIBUTE(
   name => 'ORACLEOSUSER',
   attribute => 'password',
   value => 'oracle'
);

20. View and Manage Auto Task Jobs in Database

SELECT client_name, status, attributes, service_name
FROM dba_autotask_client;

BEGIN
   DBMS_AUTO_TASK_ADMIN.disable (
      client_name => 'auto space advisor',
      operation => NULL,
      window_name => NULL
   );
END;

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

BEGIN
   DBMS_AUTO_TASK_ADMIN.disable (
      client_name => 'auto optimizer stats collection',
      operation => NULL,
      window_name => NULL
   );
END;

For more details on Oracle Job Scheduler commands, visit the official documentation: DBMS_SCHEDULER Overview.

ADVERTISEMENT

You might like

Leave a Reply

Your email address will not be published. Required fields are marked *