Como habilitar el traceo ( TRACE ) de una sessión en la base de datos a través de un TRIGGER

En ocasiones, podemos tener algunos problemas, para encontrar algún problema en común, sobre lo que esta haciendo una aplicación a la hora de ejecutarse en la base de datos.

En codiciones extraordinarias, podemos estar en una situación, en dónde ni siquiera tenemos los fuentes del aplicativo como para poder debugger la ó las formas con la herramienta de desarrollo.

Aquí les aporto, un trigger con algunas pequeñas modificaciones, propiedad de Frank Naude, que permite habilitar a la hora de iniciar una sesión en la base de datos el traceo de un usuario "X".

Para adaptarlo a su instalación debe cambiar "scott", por el dueño del esquema del aplicativo y "PC1" por el patrón de nombramiento de las estaciones de trabajo.

Nota:
Este trigger es válido siempre y cuando, la validación del usuario sea directa en la aplicación y utilice roles a nivel de la base de datos para accesar el aplicativo. Si la validación es a través de un usuario único en la base de datos y control de permisos a nivel aplicativo, no será funcional.

Probado en: Oracle 9i R1,R2 y Oracle 10g R1 y R2 ( Para instalaciónes RAC, debe adaptarse el trigger cambiando la vista por gv$session y agregando la columna inst_id para identificar el nodo al que esta conectado.

El archivo de traceo queda en $ORACLE_BASE/admin/nombre_instancia/udump, y el mismo es visible una vez que el usuario se desconecte de la base de datos.

Actualización: Hector Gabriel Ulloa Ligarius de http://www.oracleyyo.com nos hace una aportación importante, que omití previamente, el usuario debe tener privilegios de "SELECT" sobre la vista v_$session

# -----------------------------------------------------------------------
# Filename: logintrig.sql
# Purpose: Audit user logins and enable SQL tracing for selected users
# Date: 09-Jul-2005
# Modificado:Ronald Vargas Quesada
# Author: Frank Naude, Oracle FAQ
# -----------------------------------------------------------------------

CONNECT / AS SYSDBA

DROP TABLE user_trace_bitacora;

CREATE TABLE user_trace_bitacora (
login_time DATE,
username VARCHAR2(30),
machine VARCHAR2(30),
command VARCHAR2(128)
);

CREATE OR REPLACE TRIGGER user_login_trigger
AFTER LOGON ON scott.SCHEMA

DECLARE
v_username sys.v_$session.username%TYPE;
v_machine sys.v_$session.machine%TYPE;
v_command sys.v_$session.command%TYPE;

BEGIN
SELECT username, machine, command
INTO v_username, v_machine, v_command
FROM sys.v_$session
WHERE audsid = USERENV('SESSIONID')
AND audsid != 0 -- Verifica que la coneccion no se del usuario SYS
AND rownum = 1; -- Procesos en paralelo podrían tener el mismo AUDSID's

INSERT INTO sys.user_trace_bitacora
VALUES (SYSDATE, v_username, v_machine, v_command);

-- Inicia el traceo para el SQL para los usuarios desde la máquina PC1 ( patrón)
IF UPPER(v_machine) like '%PC1%' THEN

dbms_session.set_sql_trace(TRUE);
END IF;
END;
/
SHOW ERRORS