Automatización de tareas en Oracle

Documento escrito por: Oussama Osman

Puedes ver todos los documentos de este autor aquí.

---

Conceptos previos:

• DDL (Data Definition Language): sentencias SQL que se utilizan para definir la estructura de la base de datos, como la creación y modificación de tablas, índices, vistas y otras estructuras de datos. Algunos ejemplos de sentencias DDL son CREATE TABLE, ALTER TABLE, DROP TABLE, etc.
• DML (Data Manipulation Language): sentencias SQL que son usadas para manipular los datos en la base de datos, como la inserción, actualización, eliminación y selección de datos. Algunos ejemplos de sentencias DML pueden ser INSERT, UPDATE, DELETE y SELECT.

La automatización de tareas es muy útil a la hora de ahorrar tiempo y de evitar repetir de forma innecesaria ciertas acciones.

Planificación de tareas

En Oracle existe el planificador de tareas o Oracle Jobs, el cual es un componente que permite programar y automatizar tareas regulares o repetitivas.

Gracias a este planificador de tareas se puede especificar la frecuencia, momento y condiciones para ejecutar tareas, como la limpieza de datos, generación de informes, optimización de índices y la realización de copias de seguridad.

Con este componente, no solo se ahorra tiempo o esfuerzo, si no que al eliminar la necesidad de realizar de forma manual tareas administrativas asegura la ejecución de estas, que pueden llegar a ser críticas para el rendimiento y la integridad de la base de datos.

Arquitectura y Concepto

La arquitectura del planificador de tareas de Oracle consta de lo siguiente:

• job queue processes: Proceso encargado de realizar las tareas.
• Jnnn: (n representa un número) Procesos hijos asociados a una tarea.

Aparte, el planificador de tareas permite ejecutar procedimientos y bloques PL/SQL y programas almacenados Java, externos o locales, como scripts o ejecutables.

Esta ejecución viene dada por uno de estos tres factores:

• Tiempo: fecha y hora específica.
• Evento/s: cuando un evento o condiciones se cumple, lo que permite esperar a la finalización de otros procesos antes de la ejecución del trabajo.
• Dependencia : dependiendo del éxito o fracaso de un trabajo se puede ejecutar cierta tarea.

El encargado de esto es un paquete llamado dbms_scheduler, que proporciona una serie de funciones programables y procedimientos.

Funcionamiento

El planificador de tareas consta de estos elementos:

• Job (trabajo): indica que tiene que hacer (program) y cuando se debe hacer (schedule).
• Window (ventana): le deja Oracle la posibilidad de decidir cuando ejecutar la tarea, es decir, un período de tiempo durante el cual se pueden ejecutar las tareas programadas.
• Job class (clase del trabajo): permite asociar tareas con un grupo del Resource Manager.

Creación de un job

Los siguientes son los pasos básicos para planificar una tarea en Oracle:

• Crear una tarea: Primero, debes crear una tarea que represente la acción que desea programar en la base de datos. Esta tarea puede ser un procedimiento PL/SQL, un script externo o cualquier otra acción programable.

• Crear un programa: Una vez que se ha creado la tarea, debes crear un programa que especifique la tarea o tareas y especificar de que tipo de tarea o tareas se trata. Puede ser un horario fijo o una programación basada en eventos. Es necesario habilitar el programa, para ello usaremos esta consulta:

  exec dbms_scheduler.enable('nombre_programa');

  Podemos verificar que se ha creado dicho programa lanzando la siguiente consulta:

  SELECT * FROM dba_scheduler_programs
  WHERE program_name='nombre_programa';

• Asignar una ventana: Aunque esto es opcional, puedes asignar una ventana a la tarea para especificar un período de tiempo durante el cual la tarea puede ser ejecutada.

• Asignar privilegios: Asegúrate de que el usuario que tiene la tarea asignada tenga los privilegios y permisos necesarios para ejecutar la tarea.

• Programar la tarea: Programa la tarea en el planificador de tareas y especifica la frecuencia y el momento en que se debe ejecutar. Podemos verificar que se ha creado correctamente lanzando la consulta siguiente:

  SELECT * FROM dba_scheduler_schedules
  WHERE schedule_name='nombre_tarea';

• Crear un trabajo (job): Una vez programada la tarea, puedes crear un trabajo que contenga la tarea o agrupe varias tareas programadas y controlarlas y monitorearlas juntas. Es necesario habilitar el trabajo, para ello usamos esta consulta:

  exec dbms_scheduler.enable('nombre_trabajo');

  Podemos verificar la creación del trabajo con la siguiente consulta:

  SELECT * FROM dba_scheduler_jobs
  WHERE job_name='nombre_trabajo';

Estos son los pasos básicos para programar una tarea en el planificador de tareas de Oracle. Sin embargo, es importante tener en cuenta que el proceso exacto puede variar según la versión de Oracle y la complejidad de la tarea que se está programando.

EJEMPLOS DE SINTAXIS:

• Creación de un programa:

  BEGIN
    DBMS_SCHEDULER.CREATE_PROGRAM(
      program_name => 'nombre_programa',
      program_action => 'nombre_tarea',
      program_type => 'tipo_tarea'
    );
  END;
• Creación de un schedule:

  BEGIN
    DBMS_SCHEDULER.CREATE_SCHEDULE(
      schedule_name => 'nombre_tarea_programada',
      start_date => 'fecha_inicio',
      repeat_interval => 'frecuencia_repeticion',
      end_date => 'fecha_fin',
      comments => 'comentarios adicionales'
    );
  END;
• Creación de un job o trabajo:

  BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
      job_name => 'nombre_job',
      program_name => 'nombre_programa',
      schedule_name => 'nombre_tarea_programada'
    );
  END;

Información y operaciones sobre trabajos

Si queremos saber más acerca de un trabajo creado, podemos lanzar las siguientes consultas dependiendo de lo que queremos:

• Ejecuciones del trabajo:

  SELECT * FROM user_schedule_job_log
  WHERE job_name='nombre_trabajo';
• Próxima ejecución del trabajo:

  SELECT job_name, net_run_date
  FROM dba_scheduler_jobs
  WHERE job_name='nombre_trabajo'
  También podemos realizar las siguientes operaciones según nuestra necesidad:
• Deshabilitar un trabajo:

  exec dbms_scheduler.disable('nombre_trabajo');
• Borrar un trabajo:

  exec dbms_scheduler.drop_job('nombre_trabajo');
• Borrar un programa:

  exec dbms_scheduler.drop_program('nombre_programa');
• Borrar el planificador:

  exec dbms_scheduler.drop_schedule('nombre_planificador');

Scripts desde el SO

También podemos usar el SO (Sistema Operativo) para administrar las base de datos o las distintas base de datos que tengamos ya que el propio SO trae un planificador, como podría ser cron. Esto permitirá ciertas acciones sin necesidad de una conexión explicita a la base de datos.

Creación de scripts

La creación de scripts en sí es igual a como se haría un script de forma habitual:

• Usaremos bash, es decir, un archivo acabado en .sh.
• Añadimos contenido al script que será lo que se ejecute.
• El archivo tendrá permisos para ejecutarse y así se interpretará como un script:

  chmod +x nombre_script.sh
• Ejecutamos el script.

Solo hay ciertas peculiaridad a la hora de realizar estos scripts con vistas a realizar tareas administrativas en la base de datos:

• Se deben especificar las variables de entorno: estas pueden ser el nombre de la base de datos o especificar que no se nos pida confirmación al realizar cierta tarea. Un ejemplo sería el siguiente:

  # especificar el nombre de la bd
  export ORACLE_SID=zb
  # especificar que no queremos confirmación
  export ORAENV_ASK=NO
  # aplicar los cambios realizados
  .oraenv
• EOF para los comandos a ejecutar en sql: EOF significa "End of File" y se utiliza para indicar el final de un bloque de texto que se está enviando como entrada a un comando. Un ejemplo sería el siguiente:

  sqlplus -s '/ as sysdba' <<eof
  startup
  exit
  eof
  En este bloque de código, lo que se hace es iniciar la base de datos (startup) y salir de ella, esto se le pasa al programa (sqlplus) gracias a la redirección (<<) del EOF.

Un ejemplo completo de parada e inicio de una base de datos con un script sería este:

  # especificar el nombre de la bd
  export ORACLE_SID=zb
  # especificar que no queremos confirmación
  export ORAENV_ASK=NO
  # aplicar los cambios realizados
  .oraenv
  # ejecutar los comandos en sql
  sqlplus -s '/ as sysdba' <<eof
  shutdown immediate
  startup
  exit
  eof

Disparadores

Un trigger o disparador es un bloque PL/SQL que se activa de manera automática cuando se cumple una condición específica. Se suelen usar para implementar restricciones, evitar operaciones erróneas, reglas administrativas complejas, generar valores por defecto, auditar actualizaciones, mandar alertas y gestionar replicas de tablas entre otras cosas.

Dentro de los triggers hay distintos tipos:

• Triggers de tablas: que están asociados a una tabla concreta. Se suelen activar cuando se manipula (DML) una tabla.
• Triggers de sustitución: que están asociados a una vista concreta. Se suelen activar cuando se manipula (DML) una vista.
• Triggers de sistema: Se suelen activar con eventos del sistema o instrucciones DDL.

Hay que tener en cuenta que para crear triggers se necesita el permiso de CREATE TRIGGER, salvo que también queramos crearlo en otros usuarios, que sería CREATE ANY TRIGGER.

Por último, debes tener en cuenta que si un trigger falla se debe volver al último commit (ROLLBACK)

Creación de triggers

Para crear un trigger se usa la siguiente sintaxis:

Imagen de la Documentación oficial de Oracle

En el dml_event_clause:

Imagen de la Documentación oficial de Oracle

Y en el referencing_clause:

Imagen de la Documentación oficial de Oracle

Debes tener en cuenta las siguientes cosas:

• El before se ejecuta antes de la instrucción DML y el after se lanza una vez realizada dicha instrucción.
• old/new: se usan junto a FOR EACH ROW ya que tiene efecto a nivel de fila (row). El old tiene un valor nulo si la instrucción DML es un INSERT y el new tiene valor nulo si se trata de un DELETE.
• EL OR sirve para enlazar eventos que harán saltar el trigger.

Cuidado:

• El INSTEAD OF solo es válido para vistas, es decir, triggers de sustitución.
• No se pueden hacer COMMIT, ROLLBACK o SAVEPOINT ni usar estos en ningún procedimiento usado por el trigger.
• No se pueden usar instrucciones DDL.

Esta es la sintaxis general, pero se adapta para cada tipo de trigger:

• Triggers de tablas:

  • No se pueden consultar ni alterar las mismas tablas que se modifican en la misma sesión.
  • No cambiar valores clave (claves primarias, ajenas o únicas) de tablas a actualizar o consultar en la misma sesión.

• Triggers de sustitución:

  • Están asociados unicamente a vistas.
  • Siempre para cada fila (FOR EACH ROW)

• Triggers de sistema:

  • Eventos del sistema o una DDL.

  • Se necesita un permiso para poder ejecutarlos: ADMINISTER DATABASE TRIGGER

  • Estos son algunos de los eventos del sistema que se pueden usar en este trigger:

    Evento	Momento
    STARTUP	AFTER
    SHUTDOWN	BEFORE
    LOGON	AFTER
    LOGOFF	BEFORE
    SERVERERROR	AFTER
    CREATE	ambas
    DROP	ambas
    ALTER	ambas
    TRUNCATE	ambas
    GRANT	ambas
    REVOKE	ambas
    DLL	ambas

EJEMPLOS:

• Triggers de tablas: Vamos a auditar el cambio de curso de alumnos en un instituto y meter el nombre del alumno, su id y su anterior curso y el nuevo en una tabla que hemos creado llamada alumnos_cambiados:

  CREATE OR REPLACE TRIGGER audit_alumno_cambiado
  BEFORE UPDATE OF curso
  ON alumnos
  FOR EACH ROW
  BEGIN
    INSERT INTO alumnos_cambiados
      (alumno_id, nombre_alumno, curso_anterior, curso_actual)
    VALUES(
      :old.alumno_id,
      :old.nombre_alumno,
      :old.curso,
      :new.curso);
  END;
• Triggers de sustitución: Tenemos una vista asociada a la tabla alumnos, y queremos poder borrar directamente desde la vista:

  CREATE OR REPLACE TRIGGER borrado_alumno
  INSTEAD OF DELETE ON vista_alumnos
  FOR EACH ROW
  BEGIN
   DELETE FROM alumnos WHERE alumno_id=:old.almuno_id
  END;
• Triggers de sistema: Queremos guardar el usuario y la hora en una tabla que hemos creado llamada cierre_sesion cada vez que un usuario cierra sesión en la base de datos:

  CREATE TRIGGER audit_cierre_sesion
  BEFORE LOGOFF ON DATABASE
  BEGIN
    INSERT INTO cierre_sesion VALUES (USER, SYSDATE)
  END;

Consideraciones

• Activar trigger:

  ALTER TRIGGER nombre_trigger ENABLE;
• Deshabilitar trigger:

  ALTER TRIGGER nombre_trigger DISABLE;
• Compilar trigger:

  ALTER TRIGGER nombre_trigger COMPILE;
• Borra trigger:

  DROP TRIGGER nombre_trigger;
• Vista de información de los triggers:

  SELECT * FROM user_triggers;