Objetivos
- Conocer que es un JOB de Base de Datos.
- Familiarizarnos con el Paquete: DBMS_SCHEDULER.
- Conocer sus usos y las ventajas que Ofrece.
- Ver ejemplos prácticos.
Los JOBS
Para ejecutar ciertos procesos de negocio de manera eficiente, las Bases de Datos necesitan realizar tareas automáticas capaces de analizar y procesar grandes volúmenes de información. Estas tareas suelen ser esenciales para garantizar el correcto funcionamiento de las operaciones en el mundo real.
Un ejemplo común son procesos como:
- el cambio de estatus de las facturas pagadas en el mes,
- la generación mensual de estados de cuenta de tarjetas de crédito,
- o el cálculo de dividendos en cuentas de ahorro de un banco.
Todas estas actividades se implementan habitualmente mediante JOBs de Base de Datos.
Oracle, como es natural, provee herramientas que permiten crear y programar JOBs para que se ejecuten automáticamente en una fecha y hora determinadas, facilitando así la automatización de procesos críticos del negocio.
Paquete DBMS_SCHEDULER
El paquete DBMS_SCHEDULER funciona como un Programador de Tareas (Job Scheduler) en Oracle. Introducido en la versión 10g, fue diseñado para reemplazar gradualmente al antiguo DBMS_JOB, aunque este último aún sigue disponible en versiones actuales.
Características principales
- Job History: mantiene un registro en forma de auditoría de todos los Jobs ejecutados.
- Sintaxis simple y potente: facilita la programación de tareas complejas.
- Ejecución flexible: permite ejecutar tanto bloques PL/SQL como programas del sistema operativo.
- Gestión de recursos: administra diferentes clases de Jobs.
- Modelo de seguridad: basado en privilegios específicos para Jobs.
- Nombres y comentarios: facilita la identificación y documentación de cada Job.
- Intervalos naturales: permite expresar la planificación de forma intuitiva.
Procedimiento CREATE_JOB
Se utiliza para crear un Job. Este procedimiento cuenta con varias sobrecargas, por lo que los parámetros pueden variar según el caso.
Privilegios necesarios
- El usuario debe tener el privilegio CREATE JOB para crear un Job en su propio esquema.
- Con el privilegio CREATE ANY JOB, puede crear Jobs en cualquier esquema.
- Por defecto, los Jobs se crean inhabilitados; es necesario habilitarlos explícitamente para que se ejecuten.
Parámetros:
| Parámetro | Descripción |
| job_name | Especifica el nombre del JOB. Si el JOB a ser creado residirá en otro esquema, debe calificarse con el nombre del esquema. |
| job_type | Este atributo especifica el tipo de JOB. Los valores admitidos son:‘PLSQL_BLOCK’, ‘STORED_PROCEDURE’, ‘EXECUTABLE’ y ‘CHAIN’. |
| job_action | Este atributo especifica la acción del JOB. |
| number_of_arguments | Este atributo especifica el número de argumentos que espera el JOB. El rango es 0-255, siendo 0 el valor predeterminado. |
| program_name | El nombre del programa asociado al JOB. Si el programa es del tipo EXECUTABLE, el propietario del JOB debe tener el privilegio de sistema CREATE EXTERNAL JOB antes de que se pueda habilitar o ejecutar el JOB. |
| start_date | Este atributo especifica la fecha en la que se desea ejecutar el JOB por primera vez. Si start_date y repeat_interval se dejan NULL, el JOB se programa para ejecutarse tan pronto que sea habilitado. |
| event_condition | Se trata de una expresión condicional basada en las columnas de la tabla de cola de origen de eventos. |
| queue_spec | Este argumento especifica la cola en la que los sucesos que inician este JOB en particular se encadenarán (la cola de origen). |
| repeat_interval | Este atributo especifica la frecuencia con la que el JOB debe repetirse. Puede especificar el intervalo de repetición utilizando expresiones de calendario o de PL/SQL.La expresión especificada se evalúa para determinar la próxima vez que se ejecutara el JOB. Si no se especifica el repeat_interval, el JOB se ejecutará sólo una vez en la fecha de inicio especificada. |
| schedule_name | El nombre del programa, ventana o grupo de ventanas asociado con este JOB. |
| end_date | Este atributo especifica la fecha en la cual el JOB expirará y ya no se ejecutará más. Cuando se alcanza end_date, el JOB se desactiva. El ESTADO del JOB cambia a COMPLETED y el indicador enabled se establece en FALSE.Si no se especifica ningún valor para end_date, el JOB siempre se repetirá, a menos que se configure max_runs o max_failures, en cuyo caso el JOB se detendrá cuando se alcance cualquiera de los dos valores.El valor de end_date debe ser mayor/posterior al valor de start_date. Si no es así, se genera un error cuando se habilita el JOB. |
| job_priority | Este atributo designa la prioridad de un JOB en relación con otros de la misma clase. Si dos JOBs en la misma clase están programados para iniciarse al mismo tiempo, el que tiene la prioridad más alta toma precedencia. Los valores aceptables son de 1 a 5, donde 1 es la prioridad más alta. El valor predeterminado es 3. |
| comments | Este atributo especifica un comentario sobre el JOB. De forma predeterminada, este atributo es NULL. |
| enabled | Este atributo especifica si el JOB se ha creado habilitado o no. Los posibles valores son TRUE o FALSE. De forma predeterminada, este atributo se establece en FALSE y, por lo tanto, el JOB se crea como inhabilitado. |
| auto_drop | Este indicador, si es TRUE, hace que el JOB se elimine automáticamente después de haber finalizado o se haya inhabilitado. |
Procedimiento SET_ATTRIBUTE
El procedimiento SET_ATTRIBUTE se utiliza para modificar los atributos de un Job ya existente.
- Está sobrecargado para aceptar distintos tipos de datos:
- VARCHAR2
- TIMESTAMP WITH TIMEZONE
- BOOLEAN
- PLS_INTEGER
- INTERVAL DAY TO SECOND
Comportamiento al modificar un Job
- Si el Job está habilitado, el Scheduler primero lo inhabilita, aplica el cambio y luego lo rehabilita automáticamente.
- Si el Job está inhabilitado, mantiene ese estado después de aplicar la modificación.
Este procedimiento es muy útil para ajustar parámetros como la fecha de inicio, el intervalo de ejecución, o incluso comentarios y configuraciones de seguridad, sin necesidad de recrear el Job desde cero.
Parámetros:
| Parámetro | Descripción |
| name | Es el nombre del JOB. |
| attribute | Es el nombre del atributo. |
| value | Establece el nuevo valor para el atributo. El mismo no puede ser NULL. Para asignar un valor NULL, utilice el procedimiento SET_ATTRIBUTE_NULL. |
| value2 | La mayoría de los atributos tienen sólo un valor asociado con ellos, pero algunos pueden tener dos. El argumento value2 es para este segundo valor opcional. |
Procedimiento ENABLE
El procedimiento ENABLE se utiliza para habilitar un Job, ya que por defecto los Jobs se crean inhabilitados.
Controles previos
- Antes de habilitar un Job, el Scheduler realiza una serie de validaciones de consistencia y configuración.
- Si alguna comprobación falla, el Job no se habilita y se lanza un error.
- Si se intenta habilitar un Job que ya está habilitado, no se produce error alguno y el estado permanece sin cambios.
Este procedimiento es fundamental porque asegura que el Job esté correctamente definido antes de entrar en ejecución. Normalmente se usa después de crear el Job con CREATE_JOB o tras modificarlo con SET_ATTRIBUTE.
Parámetros:
| Parámetro | Descripción |
| name | Es el nombre del JOB. |
Procedimiento DISABLE
El procedimiento DISABLE se utiliza para inhabilitar un Job previamente creado.
Comportamiento
- Al inhabilitar un Job, todos sus metadatos se conservan (definición, atributos, historial, etc.).
- El estado del Job en la cola de ejecución cambia a DISABLED, lo que significa que no podrá ejecutarse hasta ser habilitado nuevamente con el procedimiento
ENABLE.
Este procedimiento resulta útil cuando se necesita pausar temporalmente la ejecución de un Job sin perder su configuración ni tener que recrearlo.
Parámetros:
| Parámetro | Descripción |
| name | Es el nombre del JOB. |
| force | Para indicar si se quiere ignorar las dependencias.Si force es FALSE y el JOB está en ejecución, se devuelve un error.Si force es TRUE, el JOB se inhabilita, pero permite que finalice la instancia en ejecución. |
Procedimiento RUN_JOB
El procedimiento RUN_JOB se utiliza para ejecutar un Job inmediatamente, sin necesidad de esperar al intervalo o fecha programada.
Comportamiento
- El Job no necesita estar habilitado previamente.
- Si el Job está inhabilitado, el Scheduler realiza controles de validez:
- Si los controles son exitosos, el Job se habilita automáticamente, se ejecuta y luego permanece en estado habilitado.
- Si los controles fallan, se lanza un error y el Job no se ejecuta.
- Si el Job ya está habilitado, simplemente se ejecuta de inmediato.
Este procedimiento es muy útil para pruebas rápidas o para ejecutar Jobs en situaciones excepcionales, sin alterar su programación original.
Parámetros:
| Parámetro | Descripción |
| job_name | Es el nombre del JOB. |
| use_current_session | Especifica si la ejecución del JOB debe producirse en la misma sesión en la que se invocó el procedimiento. |
Procedimiento STOP_JOB
El procedimiento STOP_JOB se utiliza para detener todos los Jobs que se encuentran en ejecución en ese momento.
Comportamiento según el tipo de Job
- Jobs repetitivos (SCHEDULED):
- Si se detienen, su estado pasa a SCHEDULED si tienen una próxima ejecución programada.
- Si no hay próxima ejecución, su estado pasa a COMPLETED.
- Jobs de una sola ejecución (ONE-TIME):
- Al detenerse, su estado cambia a STOPPED, ya que no tienen más corridas pendientes.
Parámetros:
| Parámetro | Descripción |
| job_name | Es el nombre del JOB. |
| force | Si forcé es FALSE, se intenta detener el JOB de manera armónica utilizando un mecanismo de interrupción.Si forcé es TRUE, el JOB es detenido inmediatamente. |
Procedimiento DROP_JOB
El procedimiento DROP_JOB se utiliza para eliminar un Job de forma definitiva.
Comportamiento
- Al eliminar un Job:
- Se borran todos sus metadatos (definición, atributos, historial, comentarios, etc.).
- El Job desaparece de la cola de ejecución y ya no puede ser utilizado ni recuperado.
- Es una acción irreversible: si se necesita el mismo Job nuevamente, debe ser recreado desde cero con
CREATE_JOB.
Parámetros:
| Parámetro | Descripción |
| job_name | Es el nombre del JOB. |
| force | Si la force es FALSE y una instancia del JOB se está ejecutando, la llamada genera un error.Si force es TRUE, se intenta detener la instancia del JOB en ejecución y luego se elimina el JOB. |
Ejemplos
A continuación creamos la tabla wrong_salary para insertar a los empleados con un salario fuera del rango permitido para su empleo de acyerdi a los valores min_salary y max_salary de la tabla jobs.
CREATE TABLE hr.wrong_salary
(id NUMBER
CONSTRAINT pk_id_wrong_sal PRIMARY KEY,
employee_id NUMBER(6)
CONSTRAINT fk_emp_id
REFERENCES hr.employees (employee_id),
salary NUMBER(8,2),
amount_outrage NUMBER(10,4));Secuencia que usaremos para insertar el Primary Key de la tabla: wrong_salary
CREATE SEQUENCE hr.seq_id_wrong_sal
INCREMENT BY 1
START WITH 1
NOCYCLE;Creamos el procedimiento proc_chk_sal_range que se encargara de hacer todo el proceso: se seleccionan a los empleados con un salario invalido y posteriormente lo inserta en la tabla wrong_salary.
CREATE OR REPLACE PROCEDURE hr.proc_chk_sal_range IS
CURSOR cur_chk_emp_sal IS
SELECT
e.employee_id,
e.salary,
j.min_salary,
j.max_salary
FROM hr.employees e, jobs j
WHERE e.job_id = j.job_id
AND e.salary NOT BETWEEN j.min_salary AND j.max_salary;
v_amount NUMBER;
BEGIN
FOR i IN cur_chk_emp_sal LOOP
IF i.salary < i.min_salary THEN
v_amount := i.min_salary;
ELSE
v_amount := i.max_salary;
END IF;
INSERT INTO hr.wrong_salary(id, employee_id, salary, amount_outrage)
VALUES(hr.seq_id_wrong_sal.NEXTVAL,i.employee_id, i.salary, i.salary-v_amount);
END LOOP;
COMMIT;
END proc_chk_sal_range;Con la siguiente consulta podemos ver todos los empleados, sus salarios y el rango valido de su empleo.
SELECT
e.employee_id,
e.salary,
j.min_salary,
j.max_salary
FROM hr.employees e, jobs j
WHERE e.job_id = j.job_id;
Los UPDATES a continuación actualizan los salarios de algunos empleados para así ponerles un salario fuera del rango permitido
UPDATE hr.employees e
SET e.salary = (
SELECT j.min_salary - 500
FROM jobs j
WHERE j.job_id = e.job_id
)
WHERE e.employee_id IN (113,108,145,206,183);UPDATE hr.employees e
SET e.salary = (
SELECT j.max_salary + 310
FROM jobs j
WHERE j.job_id = e.job_id
)
WHERE e.employee_id IN (121,135,131,182,118);Ahora creamos el JOB job_chk_emp_sal para que se ejecute cada mes a partir de la fecha: 11/07/2026. Notar que el parámetro auto_drop lo establecimos en TRUE y que también establecimos una fecha limite en 11/08/2028.
Procedemos a habilitar el job con el uso de procedimiento ENABLE.
BEGIN
DBMS_SCHEDULER.ENABLE
(name => 'hr.job_chk_emp_sal');
END;
A continuación usamos el procedimiento RUN_JOB para ejecutarlo imediatamente.
BEGIN
DBMS_SCHEDULER.RUN_JOB
('hr.job_chk_emp_sal'
,TRUE);
END;Luego de la ejecución del job procedemos a consultar la tabla wrong_salary.
SELECT *
FROM wrong_salary;
Ahora cambiamos algunos atributos del job usando SET_ATTRIBUTE y SET_ATTRIBUTE_NULL .
BEGIN
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
(
name => 'hr.job_chk_emp_sal'
,attribute => 'MAX_FAILURES'
,value => 3
);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
(
name => 'hr.job_chk_emp_sal'
,attribute => 'JOB_PRIORITY'
,value => 2
);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
(
name => 'hr.job_chk_emp_sal'
,attribute => 'AUTO_DROP'
,value => FALSE
);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
(
name => 'hr.job_chk_emp_sal'
,attribute => 'END_DATE'
);
END;
Finalmente eliminamos el job de la base de datos.
BEGIN
SYS.DBMS_SCHEDULER.DROP_JOB
(job_name => 'hr.job_chk_emp_sal');
END;Conclusión
El paquete DBMS_SCHEDULER representa una herramienta poderosa y flexible para la automatización de tareas en Oracle. Gracias a sus procedimientos —como CREATE_JOB, SET_ATTRIBUTE, ENABLE, DISABLE, RUN_JOB, STOP_JOB y DROP_JOB— los administradores pueden crear, gestionar y controlar Jobs de manera eficiente, asegurando que los procesos críticos del negocio se ejecuten en el momento adecuado y bajo condiciones seguras.
En definitiva, DBMS_SCHEDULER no solo reemplaza al antiguo DBMS_JOB, sino que amplía sus capacidades con un modelo más robusto de seguridad, auditoría y gestión de recursos, convirtiéndose en un componente esencial para la administración moderna de bases de datos Oracle.