ADVERTISEMENT

Oracle DBMS_SCHEDULER Commands

Oracle DBMS_SCHEDULER enables automation of routine tasks like report generation, cleanup, and backups. Below is a concise guide to creating, managing, and monitoring Scheduler jobs.

Create Scheduler Objects

-- Create a Schedule
BEGIN
  DBMS_SCHEDULER.CREATE_SCHEDULE(
    schedule_name     => 'DAILYREPORTGEN_SCHED',
    start_date        => SYSTIMESTAMP,
    repeat_interval   => 'FREQ=DAILY;BYHOUR=11;BYMINUTE=30',
    comments          => 'Daily report generation'
  );
END;

-- 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
  );
END;

-- Create a Job
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name       => 'DAILYREPORTGEN_RUN',
    program_name   => 'DAILYREPORTGENERATION',
    schedule_name  => 'DAILYREPORTGEN_SCHED',
    enabled        => FALSE
  );
END;

-- Create 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"'');',
    repeat_interval => 'FREQ=DAILY;BYHOUR=8;BYMINUTE=00',
    enabled         => FALSE,
    auto_drop       => FALSE
  );
  DBMS_SCHEDULER.enable(name => 'MY_SCHEMA.MV_REF_REPORT_DATA');
END;

Manage Scheduler Jobs

EXEC DBMS_SCHEDULER.ENABLE('SCOTT.MONTHLYREPORT');      -- Enable a job
EXEC DBMS_SCHEDULER.DISABLE('SCOTT.MONTHLYREPORT');     -- Disable a job
EXEC DBMS_SCHEDULER.STOP_JOB('SCOTT.MONTHLYREPORT');    -- Stop a job
EXEC DBMS_SCHEDULER.RUN_JOB('SCOTT.MONTHLYREPORT');     -- Run job immediately
EXEC DBMS_SCHEDULER.DROP_JOB('SCOTT.MONTHLYREPORT');    -- Drop a job

View Schedules and Jobs

-- View schedule details
SELECT schedule_name, schedule_type, start_date, repeat_interval
FROM dba_scheduler_schedules;

-- Monitor running jobs
SELECT job_name, session_id, running_instance, elapsed_time
FROM dba_scheduler_running_jobs;

-- Job logs
SELECT * FROM DBA_SCHEDULER_JOB_LOG;
SELECT * FROM DBA_SCHEDULER_JOB_RUN_DETAILS;

Miscellaneous

-- Get DDL of a procedure
SELECT dbms_metadata.get_ddl('PROCEDURE', 'DUP_ACC', 'SCOTT') FROM dual;

-- Copy job from one user to another
EXEC DBMS_SCHEDULER.COPY_JOB('SCOTT.MY_JOB_2','W3BUDDY.MY_JOB_2');

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

External Script Job (with Credential)

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

-- Create external script job
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'my_script_task',
    job_type        => 'external_script',
    job_action      => '/export/home/oracle/test.sh',
    credential_name => 'MY_OS_USER',
    start_date      => SYSDATE,
    repeat_interval => 'FREQ=MINUTELY;BYMINUTE=1',
    enabled         => TRUE,
    auto_drop       => FALSE
  );
END;

Log History & Audit

-- Job execution logs
SELECT job_name, log_date, status, operation, additional_info
FROM dba_scheduler_job_log
ORDER BY log_date DESC;

-- Job run history
SELECT job_name, status, actual_start_date, run_duration
FROM DBA_SCHEDULER_JOB_RUN_DETAILS
ORDER BY actual_start_date DESC;

Auto Task Jobs

-- View auto tasks
SELECT client_name, status, attributes, service_name
FROM dba_autotask_client;

-- Disable auto task
BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto space advisor');
END;

Manage Credentials

-- Create credential
BEGIN
  dbms_credential.create_credential (
    CREDENTIAL_NAME => 'ORACLEOSUSER',
    USERNAME => 'oracle',
    PASSWORD => 'oracle@123',
    ENABLED => TRUE
  );
END;

-- Drop credential
EXEC DBMS_SCHEDULER.DROP_CREDENTIAL('ORACLEOSUSER');

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

ADVERTISEMENT