Script - Monitorización de conexiones basada en triggers

Script de creación de tabla y triggers para el registro de los logons y logoff en la base de datos, permitiendo la monitorización de las conexiones contra la base de datos.

(bajo esquema SYS)

create table
   stats$user_log
(
   user_id           varchar2(100),
   session_id           number,
   host              varchar2(1000),
   logon_day                 date,
   logon_time        varchar2(100),
   logoff_day                date,
   logoff_time       varchar2(10)
) tablespace USERS
;
 

create or replace trigger
   logon_audit_trigger
AFTER LOGON ON DATABASE
BEGIN
insert into stats$user_log values(
   user,
   sys_context('USERENV','SESSIONID'),
   sys_context('USERENV','HOST'),
   sysdate,
   to_char(sysdate, 'hh24:mi:ss'),
   null,
   null
);
END;
/

create or replace trigger
   logoff_audit_trigger
before LOGOff ON DATABASE
BEGIN
insert into stats$user_log values(
   user,
   sys_context('USERENV','SESSIONID'),
   sys_context('USERENV','HOST'),
   null,
   null,
   sysdate,
   to_char(sysdate, 'hh24:mi:ss')
);
END;
/

Consulta para analizar las conexiones contra la base de datos entre dos fechas:

Ejemplo de llamada:

@consulta_audit_conex.sql '01/01/2010 00:00:00' '23/08/2010 23:59:59'

COLUMN HOST FORMAT A45
COLUMN USER_ID FORMAT A18
SELECT COUNT(1), USER_ID , DECODE(LOGON_DAY,NULL,'DESCONEXIONES','CONEXIONES') , host,
MIN(LOGOFF_DAY),MIN(LOGON_DAY),MAX(LOGOFF_DAY),MAX(LOGON_DAY)
 FROM SYS.STATS$USER_LOG
where
(logon_day is null or logon_day between '&&1' and '&&2' ) and
(logoff_day is null or logoff_day between '&&1' and '&&2' )
GROUP BY USER_ID , DECODE(LOGON_DAY,NULL,'DESCONEXIONES','CONEXIONES'), host
ORDER BY 2
/