Explain DBA_JOBS in oracle with examples

DBA_JOBS is a data dictionary view in Oracle that provides information about all the jobs scheduled in the database. These jobs are managed by the Oracle Job Scheduler, which allows you to automate and schedule various database tasks, such as running PL/SQL procedures or executing batch processes.

Key Columns in DBA_JOBS

• JOB: The unique identifier of the job.
• LOG_USER: The user who created the job.
• PRIV_USER: The user under whose privileges the job will run.
• SCHEMA_USER: The schema where the job is executed.
• LAST_DATE: The last date and time when the job was executed.
• LAST_SEC: The last second when the job was executed.
• THIS_DATE: The date and time when the job started executing (current run).
• THIS_SEC: The second when the job started executing (current run).
• NEXT_DATE: The next scheduled date and time for the job to run.
• NEXT_SEC: The next scheduled second for the job to run.
• TOTAL_TIME: The total elapsed time for the job's execution.
• BROKEN: Indicates whether the job is broken (Y/N).
• INTERVAL: The interval between executions of the job.
• FAILURES: The number of failures since the last successful execution.
• WHAT: The PL/SQL code block or procedure that is executed by the job.
• CURRENT_SESSION_LABEL: The session label of the current job execution.
• CLEARANCE_HI: High water mark for clearance (for job queue system).
• CLEARANCE_LO: Low water mark for clearance (for job queue system).
• NLS_ENV: NLS environment parameters for the job.
• MISC_ENV: Miscellaneous environment parameters for the job.

Examples of Using DBA_JOBS

1. View All Jobs

To view all scheduled jobs in the database, you can query the DBA_JOBS view:

SELECT JOB, LOG_USER, SCHEMA_USER, LAST_DATE, NEXT_DATE, INTERVAL, WHAT, BROKEN
FROM DBA_JOBS;

2. Create a Job

Create a job to run a PL/SQL block every day at midnight:

BEGIN
  DBMS_JOB.SUBMIT(
    job        => :job_number, 
    what       => 'BEGIN my_procedure; END;', 
    next_date  => SYSDATE, 
    interval   => 'TRUNC(SYSDATE+1) + 1/86400'  -- next midnight
  );
  COMMIT;
END;
/

3. Alter a Job

Change the interval of a job (JOB_ID=1) to run every 2 hours:

BEGIN
  DBMS_JOB.INTERVAL(
    job       => 1,
    interval  => 'SYSDATE + 2/24'
  );
  COMMIT;
END;
/

Intervals
--------------------------------------------------------------
Every day at 12:00 midnight 'TRUNC(SYSDATE + 1)' 
Every day at 8:00 a.m. 'TRUNC(SYSDATE + 1) + 8/24' 
Every Tuesday at 12:00 noon 'NEXT_DAY(TRUNC(SYSDATE ), "TUESDAY" ) + 12/24' 
First day of the month at midnight 'TRUNC(LAST_DAY(SYSDATE ) + 1)' 
Last day of the quarter at 11:00 p.m. 'TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), 'Q' ) - 1/24' 
Every Monday, Wednesday, and Friday at 9:00 a.m. 'NEXT_DAY(SYSDATE, "FRIDAY") ) ) + 9/24' 
Execute daily 'SYSDATE + 1' 
Execute hourly 'SYSDATE + 1/24' 
Execute every 10 minutes 'SYSDATE + 10/1440' 
Execute every 30 seconds 'SYSDATE + 30/86400' 
Execute every 7 days 'SYSDATE + 7' 
---------------------------------------------------------------

4. Remove a Job

Remove a job with the ID 1:

BEGIN
  DBMS_JOB.REMOVE(job => 1);
  COMMIT;
END;
/

5. Broken Jobs

View jobs that are marked as broken:

SELECT JOB, LOG_USER, SCHEMA_USER, WHAT, BROKEN
FROM DBA_JOBS
WHERE BROKEN = 'Y';

6. Fix a Broken Job

Mark a job as not broken (fix it):

BEGIN
  DBMS_JOB.BROKEN(job => 1, broken => FALSE);
  COMMIT;
END;
/

7. Job Execution History

To see when a job last ran and when it is next scheduled to run:

SELECT JOB, LAST_DATE, LAST_SEC, NEXT_DATE, NEXT_SEC
FROM DBA_JOBS
WHERE JOB = 1;

Summary

DBA_JOBS is a useful view for managing and monitoring scheduled jobs in Oracle. It provides detailed information about each job, including its execution schedule, status, and the PL/SQL code it runs. By leveraging this view and associated PL/SQL packages like DBMS_JOB, you can efficiently automate repetitive tasks in your Oracle database.