Automatizar el refresco de vistas materializadas en Oracle Database

por

Mantener las vistas materializadas actualizadas es crucial para garantizar datos precisos en tus reportes y análisis. En esta pequeño «tutorial» aprenderás cómo automatizar el refresco de vistas materializadas en Oracle utilizando PL/SQL y DBMS_SCHEDULER. Esta automatización asegura que tus datos estén siempre al día sin intervención manual, optimizando el rendimiento de tu base de datos. Vamos al lío!

¿Por qué automatizar el refresco de vistas materializadas?

Las vistas materializadas son una herramienta poderosa para mejorar el rendimiento de consultas en bases de datos Oracle. Sin embargo, si no se actualizan regularmente, pueden contener datos obsoletos. Automatizar este proceso asegura que tus datos estén siempre al día sin intervención manual, y eso como DBA te mejora la vida una barbaridad.

Guía paso a paso

1. Crear el procedimiento para refrescar las vistas materializadas

El primer paso es crear un procedimiento almacenado en PL/SQL que recorra todas las vistas materializadas del esquema y las refresque de forma individual:

CREATE OR REPLACE PROCEDURE refresh_all_mviews AS
BEGIN
    FOR mview IN (
        SELECT mview_name FROM user_mviews
    ) LOOP
        BEGIN
            DBMS_OUTPUT.PUT_LINE('Intentando refrescar: ' || mview.mview_name);
            DBMS_MVIEW.REFRESH(mview.mview_name, 'COMPLETE');
            DBMS_OUTPUT.PUT_LINE('Refrescada exitosamente: ' || mview.mview_name);
        EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('Error al refrescar ' || mview.mview_name || ': ' || SQLERRM);
        END;
    END LOOP;
END;
/

Este procedimiento utiliza un bucle para iterar sobre todas las vistas materializadas del esquema y las actualiza utilizando el método COMPLETE.

2. Programar la ejecución automática con DBMS_SCHEDULER

El siguiente paso es configurar un JOB que ejecute el procedimiento creado en un horario definido, por ejemplo, todos los días a las 10 PM.

BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
        job_name        => 'REFRESH_ALL_MVIEWS_JOB',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'BEGIN refresh_all_mviews; END;',
        start_date      => TRUNC(SYSDATE) + 1 + (22/24), -- Próxima ejecución a las 10 PM
        repeat_interval => 'FREQ=DAILY; BYHOUR=22; BYMINUTE=0; BYSECOND=0',
        enabled         => TRUE,
        comments        => 'Trabajo para refrescar todas las vistas materializadas cada noche a las 10 PM'
    );
END;
/

Con esto, el procedimiento se ejecutará automáticamente todos los días a la hora especificada.

3. Validar el Trabajo Programado

Es importante verificar que el trabajo se haya creado correctamente y que esté programado para ejecutarse. Con esta query veremos toda la información del JOB en cuestión:

SELECT job_name, next_run_date, state
FROM user_scheduler_jobs
WHERE job_name = 'REFRESH_ALL_MVIEWS_JOB';

Para supervisar el rendimiento y posibles errores, puedes consultar el historial de ejecuciones del trabajo:

SELECT job_name, status, run_duration, additional_info
FROM user_scheduler_job_run_details
WHERE job_name = 'REFRESH_ALL_MVIEWS_JOB';

4. Ejecución Manual del Procedimiento

Si necesitas refrescar las vistas inmediatamente sin esperar al horario programado, puedes ejecutar el procedimiento de forma manual de la siguiente forma:

BEGIN
    refresh_all_mviews;
END;
/

5. Auditar la actualización de las vistas materializadas

Finalmente, puedes consultar la última fecha de actualización de cada vista materializada, el modo y el método de refresco con este comando:

SELECT mview_name,
       last_refresh_date,
       refresh_mode,
       refresh_method
FROM user_mviews
ORDER BY last_refresh_date DESC;

Beneficios de la Automatización

  • Eficiencia: Ahorra tiempo eliminando la necesidad de actualizaciones manuales.
  • Precisión: Garantiza que los datos estén siempre actualizados.
  • Escalabilidad: Ideal para esquemas con muchas vistas materializadas.

Conclusión

Automatizar el refresco de vistas materializadas es una práctica esencial para cualquier base de datos Oracle con grandes volúmenes de datos. Recordad que en el blog podéis aprender un montón sobre Oracle Database y sus errores. Espero que esta guía os sirva para implementar un sistema eficiente y fiable que mantenga vuestros datos siempre actualizados.

Deja un comentario