Restablecer la contraseña de un usuario expirado sin conocer la clave actual en Oracle 12c / 19c

Restablecer la contraseña de un usuario expirado sin conocer la clave actual en Oracle 12c / 19c

   julio 20, 2026       Luis Felipe

---

En la administración diaria de bases de datos Oracle, es habitual encontrarnos con un escenario crítico: una cuenta de usuario (ya sea de aplicación, de servicio o de integración) pasa a estado EXPIRED debido a las políticas del perfil de seguridad asignado.

El verdadero problema surge cuando nadie recuerda la contraseña original. Si la cambiamos por una clave completamente nueva, interrumpiremos de inmediato el servicio de las aplicaciones o sistemas dependientes hasta que sus cadenas de conexión sean actualizadas, algo que no siempre es posible realizar de manera inmediata en entornos de producción.

Para resolver esta contingencia sin alterar la contraseña real y ganar tiempo para gestionar el cambio formal, podemos "refrescar" el estado de la cuenta (recomponiendo su estatus a OPEN) reasignando su mismo hash almacenado en el diccionario de datos.

En versiones antiguas de Oracle (10g / 11g), solía extraerse la columna PASSWORD de la tabla sys.user$. Sin embargo, a partir de Oracle 12c, 19c y versiones superiores, el motor utiliza algoritmos de cifrado más avanzados (SHA-2). Por ello, el hash activo para la autenticación se almacena en la columna SPARE4.

A continuación, se detalla el procedimiento paso a paso para realizar esta acción de contingencia en cualquier usuario de la base de datos.

Si estás trabajando en entornos Oracle 12c, 19c o superiores bajo la arquitectura Multitenant, recuerda que antes de ejecutar este procedimiento debes asegurarte de estar conectado en el contenedor correcto:

• Si el usuario afectado es un Common User (ej. C##APP_USER), deberás ejecutarlo desde el CDB$ROOT.

• Si es un usuario local de una aplicación, debes moverte primero a su PDB correspondiente: ALTER SESSION SET CONTAINER = mi_pdb;

Antes de realizar cualquier ajuste, confirmamos el estado de la cuenta y el perfil asociado consultando la vista DBA_USERS:

SELECT username, account_status, profile, expiry_date FROM dba_users WHERE username = 'USUARIO';

Para evitar el error ORA-28007: the password cannot be reused, debemos verificar si el perfil asignado permite la reasignación de la misma clave. Consultamos DBA_PROFILES:

SELECT resource_name, limit FROM dba_profiles WHERE profile = (SELECT profile FROM dba_users WHERE username = 'USUARIO') AND resource_name IN ('PASSWORD_REUSE_TIME', 'PASSWORD_REUSE_MAX');

• Si ambos valores son UNLIMITED: Se puede avanzar al siguiente paso.
• Si tienen un límite numérico restrictivo: Se pueden modificar temporalmente los parámetros del perfil antes de ejecutar el cambio, para este caso en particular tenia asignado el perfil "default":

ALTER PROFILE DEFAULT LIMIT PASSWORD_REUSE_TIME UNLIMITED PASSWORD_REUSE_MAX UNLIMITED;

Nota: Si tu usuario tiene un perfil personalizado, reemplaza DEFAULT por el nombre de su perfil para no alterar las políticas globales de la base de datos.

Consultamos la tabla interna sys.user$ filtrando por el nombre del usuario afectado:

SELECT spare4 FROM sys.user$ WHERE name = 'USUARIO';

La consulta devolverá la cadena alfanumérica completa correspondiente al hash de autenticación: 

Es importante copiar la cadena devuelta por SPARE4 en su totalidad (incluyendo prefijos como S: o T:), ya que especifica el tipo de algoritmo de cifrado empleado por la versión de la base de datos.

Ejecutamos la sentencia ALTER USER utilizando la cláusula IDENTIFIED BY VALUES. Esta sintaxis le indica a Oracle que registre directamente la clave procesada sin aplicar una nueva función de hashing sobre la cadena:

ALTER USER USUARIO IDENTIFIED BY VALUES 'S:A8B9C0D1E2F3...[HASH_COMPLETO_AQUÍ]...';

Nota: Si la cuenta también se encontraba bloqueada (LOCKED), se puede incluir la cláusula ACCOUNT UNLOCK en la misma sentencia

Con esto, el motor revalida el hash, calcula la nueva fecha de vencimiento según el perfil y cambia el estado de la cuenta a OPEN.




Validamos nuevamente la vista DBA_USERS para verificar que el procedimiento se haya completado exitosamente:

SELECT username, account_status, expiry_date FROM dba_users WHERE username = 'USUARIO';

El estado cambiará a OPEN y la columna EXPIRY_DATE reflejará la nueva fecha de vencimiento.

Si en los pasos anteriores modificaste los parámetros de reutilización de contraseña, no olvides regresar el perfil a sus valores originales para mantener el estándar de seguridad de la organización:

ALTER PROFILE DEFAULT LIMIT PASSWORD_REUSE_TIME 365 PASSWORD_REUSE_MAX 3;

Este procedimiento constituye una herramienta de contingencia fundamental para el DBA al momento de resolver bloqueos de servicio urgentes en aplicaciones cuyas contraseñas no puedan modificarse de forma inmediata.

Sin embargo, no debe adoptarse como una solución permanente. La buena práctica de administración dicta planificar ventanas de mantenimiento periódicas para la rotación formal de credenciales y la asignación de perfiles con la directiva PASSWORD_LIFE_TIME UNLIMITED exclusivamente a cuentas de servicio de aplicación debidamente auditadas.

Saludos.

Read More

Cómo automatizar GRANTS en nuevas tablas y procedimientos usando Triggers DDL y DBMS_SCHEDULER

Cómo automatizar GRANTS en nuevas tablas y procedimientos usando Triggers DDL y DBMS_SCHEDULER

   junio 15, 2026       Luis Felipe

---

¿Cuántas veces te ha pasado? Alguien del equipo crea una nueva tabla o un procedimiento en el ambiente de desarrollo, pero los demás integrantes no pueden acceder a ella debido a que no tienen los privilegios necesarios y para completar el DBA está fuera de oficina o de vacaciones...

Asignar permisos manualmente es ineficiente y propenso a errores humanos. Hoy vamos a ver cómo automatizar por completo esta tarea mediante un Trigger DDL.

Sin embargo, si estás trabajando en Oracle 12c, hacerlo directamente dentro de un trigger te llevará de cabeza a un callejón sin salida (específicamente al temido ORA-04092). En este artículo te enseño cómo estructurar una solución asíncrona, limpia y segura utilizando Transacciones Autónomas y DBMS_SCHEDULER.

Cuando ejecutas un CREATE TABLE, Oracle realiza un COMMIT implícito inmediatamente antes y después del comando. Si intentas meter un GRANT (que es un comando DCL) directamente dentro de un Trigger DDL convencional, romperás el ciclo transaccional del motor, resultando en un error de bloqueo o un fallo de compilación.

Para solucionarlo en Oracle 12c, debemos aplicar estos dos conceptos clave:

1. Un Job asíncrono: Que se encargue de dar el permiso milisegundos después de que termine la creación del objeto.

2. Una Transacción Autónoma (PRAGMA AUTONOMOUS_TRANSACTION): Que le permita al trigger abrir una "mini-sesión" independiente para agendar el Job sin interferir con el CREATE principal.

Nota: Aunque en versiones antiguas se usaba DBMS_JOB, en Oracle 12c la recomendación oficial es usar el paquete moderno DBMS_SCHEDULER, ya que es mucho más robusto y se auto-gestiona de forma más eficiente.

Primero creamos un procedimiento auxiliar. Usamos SQL dinámico (EXECUTE IMMEDIATE) combinándolo con DBMS_ASSERT.ENQUOTE_NAME por seguridad frente a inyecciones de SQL.

Es fundamental incluir la cláusula AUTHID CURRENT_USER para que se ejecute bajo los privilegios del usuario actual en tiempo de ejecución.

CREATE OR REPLACE PROCEDURE sp_otorgar_grant (
    p_object_name IN VARCHAR2,
    p_object_type IN VARCHAR2
) AUTHID CURRENT_USER AS
    v_sql VARCHAR2(1000);
BEGIN
    -- Evaluamos el tipo de objeto para asignar el privilegio correcto
    IF p_object_type = 'TABLE' THEN
        v_sql := 'GRANT SELECT, INSERT, UPDATE, DELETE ON ' || dbms_assert.enquote_name(p_object_name) || ' TO ROL_TEST';
    ELSIF p_object_type = 'PROCEDURE' THEN
        v_sql := 'GRANT EXECUTE ON ' || dbms_assert.enquote_name(p_object_name) || ' TO ROL_TEST';
    END IF;
 
    -- Si el tipo de objeto aplica, ejecutamos el DCL
    IF v_sql IS NOT NULL THEN
        EXECUTE IMMEDIATE v_sql;
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        RAISE;
END;
/

Ahora creamos el Trigger a nivel de base de datos (AFTER CREATE ON DATABASE) para centralizar los permisos en un solo trigger. Gracias al PRAGMA AUTONOMOUS_TRANSACTION, este bloque puede ejecutar su propio COMMIT interno (necesario al crear un Job) sin afectar la transacción de la tabla que se está creando en ese instante.

CREATE OR REPLACE TRIGGER trg_auto_grant_objetos
AFTER CREATE ON SCHEMA
DECLARE
    -- Habilitamos la transacción independiente para evitar el ORA-04092
    PRAGMA AUTONOMOUS_TRANSACTION;
    v_obj_type  VARCHAR2(30);
    v_job_name  VARCHAR2(100);
    v_what      VARCHAR2(2000);
BEGIN
    v_obj_type := DICTIONARY_OBJ_TYPE;
    -- Filtramos únicamente los objetos que nos interesan
    IF v_obj_type IN ('TABLE', 'PROCEDURE') THEN
        -- Generamos un nombre único y dinámico para el Job
        v_job_name := DBMS_SCHEDULER.GENERATE_JOB_NAME('JOB_GRANT_');
        -- Definimos el bloque anónimo que ejecutará el Job en segundo plano
        v_what     := 'BEGIN sp_otorgar_grant(''' || DICTIONARY_OBJ_NAME || ''', ''' || v_obj_type || '''); END;';
        -- Lanzamos el Job de ejecución inmediata
        DBMS_SCHEDULER.CREATE_JOB (
            job_name        => v_job_name,
            job_type        => 'PLSQL_BLOCK',
            job_action      => v_what,
            start_date      => SYSTIMESTAMP + INTERVAL '3' SECOND,
            enabled         => TRUE,
            auto_drop       => TRUE -- El job se elimina automáticamente al finalizar
        );
        -- Confirmamos la creación del Job en su transacción independiente
        COMMIT;
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
END;
/

Nota: Es necesario que el usuario (esquema) donde fue creado el trigger necesita privilegios para crear jobs en la base de datos.

GRANT CREATE JOB TO USUARIO_TRIGGER;

 

Una peculiaridad de delegarle la tarea a DBMS_SCHEDULER es que, si algo falla (por ejemplo, el rol no existe o el objeto tiene un nombre extraño), el error no saltará en tu script de despliegue. Ocurrirá de manera silenciosa en segundo plano.

Para auditar y comprobar que los permisos se están asignando con éxito, puedes interrogar periódicamente a la vista del diccionario de datos:

SELECT log_date, job_name, status, error#
FROM USER_SCHEDULER_JOB_RUN_DETAILS
WHERE job_name LIKE 'JOB_GRANT_%'
ORDER BY log_date DESC;

Si en la columna STATUS visualizas SUCCEEDED, felicidades: tus objetos en Oracle 12c ya se auto-gestionan solos.

Importante: Si vas a realizar un despliegue masivo en producción (un script que crea cientos de tablas de golpe), la generación masiva de Jobs individuales podría estresar momentáneamente el Scheduler de Oracle. En esos escenarios específicos, la buena práctica dicta deshabilitar temporalmente el trigger antes de la actualización y habilitarlo al finalizar:

-- Deshabilitar el trigger
ALTER TRIGGER trg_auto_grant_objetos DISABLE;
-- Habilitar el trigger
ALTER TRIGGER trg_auto_grant_objetos ENABLE;

En definitiva, automatizar la asignación de permisos mediante Transacciones Autónomas y el poder asíncrono de DBMS_SCHEDULER transforma una tarea manual, tediosa y propensa a fallos en un proceso invisible que eleva la robustez de tu infraestructura. Al implementar esta solución —asegurándote siempre de que tu usuario cuente con el privilegio directo CREATE JOB y configurando un ligero retraso de un par de segundos en el inicio del Job para sincronizarse con el COMMIT principal—, no solo eliminarás de raíz los molestos tickets de soporte por olvido de GRANTS, sino que llevarás tus despliegues en Oracle al siguiente nivel de madurez técnica y profesional.

Saludos.

Read More

Resolviendo las limitaciones del Auto Optimizer Stats Collection en Oracle

Resolviendo las limitaciones del Auto Optimizer Stats Collection en Oracle

   marzo 20, 2026       Luis Felipe

---

En el mundo de la administración de bases de datos, las estadísticas son la ruta de acceso que el optimizador (CBO) elegirá para ejecutar una sentencia SQL. Sin ellas, hasta la consulta más simple puede convertirse en una pesadilla de rendimiento. Oracle nos ofrece por defecto el Auto Optimizer Stats Collection, un job diseñado gestionar la recolección de estadísticas de forma integral y desatendida. Sin embargo, en entornos de Data Warehouse con volúmenes masivos de datos esto suele ser insuficiente.

Recientemente, en una de las bases de datos que administro, me enfrenté a un escenario crítico: el mantenimiento nativo de Oracle no lograba concluir dentro de la ventana programada de 4 horas. Esto derivaba en la presencia de estadísticas obsoletas (STALE) y el bloqueo de estadísticas en objetos específicos, provocando una degradación progresiva en el rendimiento global de la base de datos.

• El proceso se interrumpía dejando gran parte de los objetos sin procesar.

• Las estadísticas quedaban en un estado inconsistente, impidiendo actualizaciones manuales y disparando errores como el ORA-20005.

• Sin datos frescos, el optimizador elegía rutas de acceso ineficientes, aumentando drásticamente la latencia global del sistema.

Para romper este ciclo, decidimos tomar el control total mediante una automatización personalizada. Nuestra lógica aplica reglas de ingeniería de datos claras:

• El procedimiento identifica si el esquema contiene tablas particionadas o normales para aplicar el método de recolección correcto.

• Priorizamos la última partición vigente, asegurando que el optimizador (CBO) siempre tenga datos frescos de la carga reciente sin desperdiciar recursos en datos históricos estáticos.

• Implementamos una tabla de control para registrar cada inicio, fin y duración, permitiendo una auditoría que el job nativo no ofrece de forma tan detallada.

Importante: Antes de implementar esta solución es necesario desactivar la recolección automática de estadísticas de Oracle. 

Pero antes verificamos el estado actual de la tarea de recolección de estadísticas.

select client_name,status from dba_autotask_client where client_name='auto optimizer stats collection';

Procedemos a desactivar la tarea de recolección automática de Oracle.

BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE(
    client_name => 'auto optimizer stats collection',
    operation   => NULL,
    window_name => NULL
  );
END;
/

Verificamos nuevamente el estado actual de la tarea de recolección de estadísticas.

He compartido la solución completa (DDL de bitácora, procedimiento PL/SQL y configuración del Job) en mi repositorio de GitHub para que la comunidad pueda adaptarlo:

🔗Oracle-Stats-Automation

Puedes consultar el estado de tus estadísticas y el rendimiento del job mediante la tabla de bitácora:

select * from sys.gbd_bitacora_estat where grupo=5 order by 1,2 asc;

Esta solución fue desarrollada y validada con éxito en un entorno Oracle Database 12c, demostrando una estabilidad total tanto en la gestión de objetos particionados como en la integración con el Oracle Scheduler. Aunque el enfoque es estándar, se recomienda realizar pruebas previas en entornos de desarrollo antes de pasar a producción.

Read More

Resolviendo el evento de espera: Streams AQ: enqueue blocked on low memory

Resolviendo el evento de espera: Streams AQ: enqueue blocked on low memory

   febrero 16, 2026       Luis Felipe

---

Era uno de esos días en los que me encontraba realizando tareas de respaldo de información de las particiones de tablas de una de las muchas bases de datos que administro.
Noté algo que me llamó la atención: ¿Cómo podían exportarse más de 1,735 particiones de otra base de datos en horas, mientras que las 762 particiones de esta base de datos demoraban días? Sí, leyeron bien: 2 a 3 días para completar la exportación.

Claramente esto no era normal, había algo que estaba afectando el proceso de exportación (EXPDP). Me puse manos a la obra para descubrir cuál era el problema. Inicialmente pensé que era un tema de tamaño de las particiones, por lo que revisé la cantidad de registros y el peso total de cada una, comparándolas con otra base de datos. Sorprendentemente, estas particiones eran más pequeñas, por lo que descarté esa hipótesis.

Generé un reporte AWR para analizar el rendimiento de la base de datos mientras se ejecutaba el proceso de exportación. Grande fue mi sorpresa al encontrar este tipo de espera:

Queueing
(Encolamiento: tiempo que una sesión pasa esperando recursos antes de poder ejecutarse)

Al revisar el top de eventos, identifiqué un evento peculiar que nunca antes había visto: Streams AQ: enqueue blocked on low memory.

Buscando información sobre este evento, encontré la nota de Oracle Support: EXPDP And IMPDP Slow Performance In 11gR2 and 12cR1 And Waits On Streams AQ: Enqueue Blocked On Low Memory (Doc ID 1596645.1)

El documento describía exactamente los síntomas que estaba experimentando:

• Oracle Data Pump Export (expdp) y Import (impdp) funcionan muy lentamente en versiones como 11gR2 y 12cR1

Solución:

Modificar el parámetro STREAMS_POOL_SIZE si no contaba con un valor asignado y luego reiniciar la base de datos para que el cambio surta efecto.

La pregunta siguiente era: ¿Cuánto asignarle? ¿8 MB? ¿16 MB?

En mi búsqueda encontré una consulta dinámica que calcula automáticamente un valor seguro, tomando el valor máximo entre los parámetros streams_pool_size y __streams_pool_size y sumándole 64 MB:

select 'alter system set streams_pool_size='||(max(to_number(trim(c.ksppstvl)))+67108864)||' SCOPE=SPFILE;' from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c where a.indx = b.indx and a.indx = c.indx and lower(a.ksppinm) in ('__streams_pool_size','streams_pool_size');

Con esto obtuve el valor recomendado para asignar al parámetro.

1. Conectarse como SYSDBA

sqlplus / as sysdba

2. Modificar el parámetro 

SQL> alter system set streams_pool_size=384m scope=both;

3. Reiniciar la base de datos

SQL> shutdown immediate;

SQL> startup;

4. Verificar el cambio

SQL> show parameter "streams_pool_size";

Luego del cambio:

• La espera Streams AQ: enqueue blocked on low memory desapareció.

• Las exportaciones con Data Pump progresaron con normalidad.

• El rendimiento general mejoró notablemente.

Si alguna vez experimentan exportaciones lentas con Data Pump y detectan el evento de espera: Streams AQ: enqueue blocked on low memory aumentar el parámetro STREAMS_POOL_SIZE siguiendo el procedimiento descrito es la solución más efectiva.

Saludos,

Luis Felipe.

Read More