Create and Schedule a Job to Run Automatically in Oracle | SQL PLSQL
In Oracle Database, scheduled jobs, also known as database jobs, can be managed using the Oracle Scheduler. The Oracle Scheduler is a built-in tool that allows you to automate various database tasks, such as running PL/SQL procedures, executing shell scripts, or invoking external programs. Here's an overview of scheduled jobs in Oracle:
Key Concepts:
1. *Job:*
A job is a unit of work that performs a specific task, such as running a stored procedure or invoking an external program.
2. *Job Class:*
A job class is a grouping of jobs based on resource requirements or priority. It allows you to manage and allocate resources more efficiently.
3. *Window:*
A window is a specific time period during which a job can run. Windows can be used to schedule jobs during specific hours, days, or other time frames.
Creating and Managing Jobs:
4. *Create a Job:*
You can create a job using the `DBMS_SCHEDULER.CREATE_JOB` procedure or using the SQL `CREATE JOB` statement.
```sql
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name = 'your_job_name',
job_type = 'PLSQL_BLOCK',
job_action = 'BEGIN your_procedure; END;',
start_date = SYSTIMESTAMP,
repeat_interval = 'FREQ=DAILY; BYHOUR=12',
enabled = TRUE
);
END;
```
This example creates a job that runs a PL/SQL block daily at 12:00 PM.
5. *Modify a Job:*
You can modify the properties of an existing job using the `DBMS_SCHEDULER.SET_ATTRIBUTE` procedure.
```sql
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE (
name = 'your_job_name',
attribute = 'repeat_interval',
value = 'FREQ=HOURLY; INTERVAL=2'
);
END;
```
This example modifies the repeat interval of the job to run every two hours.
6. *Drop a Job:*
Use the `DBMS_SCHEDULER.DROP_JOB` procedure to remove a scheduled job.
```sql
BEGIN
DBMS_SCHEDULER.DROP_JOB('your_job_name');
END;
```
Job Types:
7. *PL/SQL Block Job:*
Executes a PL/SQL anonymous block or a stored procedure.
```sql
CREATE JOB your_job_name
RUNS your_procedure;
```
8. *Program Job:*
Executes a predefined program object.
```sql
CREATE JOB your_job_name
PROGRAM 'your_program_name';
```
Monitoring Jobs:
9. *Viewing Job Information:*
Use the `DBA_SCHEDULER_JOBS` view or the `USER_SCHEDULER_JOBS` view to query information about scheduled jobs.
```sql
SELECT job_name, job_type, start_date, state
FROM DBA_SCHEDULER_JOBS
WHERE owner = 'your_user';
```
10. *Viewing Job Run History:*
Use the `DBA_SCHEDULER_JOB_RUN_DETAILS` view or the `USER_SCHEDULER_JOB_RUN_DETAILS` view to view the run history of jobs.
```sql
SELECT job_name, run_duration, status
FROM DBA_SCHEDULER_JOB_RUN_DETAILS
WHERE owner = 'your_user';
```
Examples:
11. *Create a Job that Executes a PL/SQL Procedure:*
```sql
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name = 'daily_report_job',
job_type = 'PLSQL_BLOCK',
job_action = 'BEGIN generate_daily_report; END;',
start_date = SYSTIMESTAMP,
repeat_interval = 'FREQ=DAILY; BYHOUR=2',
enabled = TRUE
);
END;
```
12. *Create a Job that Executes an External Script:*
```sql
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name = 'external_script_job',
job_type = 'EXECUTABLE',
job_action = '/path/to/your/script.sh',
start_date = SYSTIMESTAMP,
repeat_interval = 'FREQ=MINUTELY; INTERVAL=30',
enabled = TRUE
);
END;
```
Scheduled jobs in Oracle provide a powerful mechanism for automating routine tasks, improving efficiency, and reducing manual intervention. The Oracle Scheduler offers a wide range of options for scheduling, monitoring, and managing jobs to meet various automation needs.