Auditoria sobre conexiones de usuarios

Mediante la vista propia de Oracle dba_audit_trail (acceder con system o usuario con permiso de lectura en todas las vistas del sistema) se puede conocer la información sobre las conexiones realizadas a la base de datos. Muy útil para realizar auditorías e investigaciones.

Esta consulta extrae las conexiones que realizó el usuario 'nombre_usuario' en la fecha indicada.

select a.os_username, a.username, a.terminal,

a.timestamp, a.logoff_time, a.comment_text
from dba_audit_trail a
where a.username = 'nombre_usuario'
and trunc(a.timestamp) = '20080124';

Retorna el usuario del sistema operativo con que se conectó, el usuario de Oracle, la máquina utilizada para la conexión, fecha y hora en que se conectó, fecha y hora en que se desconectó, e infrormación sobre la conexión.

Por ejemlpo si se sabe que un determinado usuario realizó una determinada operación en el sistema y se conoce la fecha y hora de la misma, mediante esta vista se puede complementar la información de la máquina con que se conectó, usuario utilizado, etc.

Con la siguiente consulta se obtienen estadísticas del uso de la base de datos por cada usuario:

select a.username,

count(*) cantidad_de_conexiones,
max(a.timestamp) fecha_utima_conexion,
count(distinct a.os_username) cantidad_de_usuarios,
count(distinct a.terminal) cantidad_de_maquinas,
count(distinct trunc(a.timestamp)) cantidad_de_dias
from dba_audit_trail a
where a.timestamp >= '20090101'
group by a.username;

Calcular primer y último día del mes

Para calcular el primer día del mes respecto a una fecha dada, o del día, en Oracle se obtiene truncando la fecha a nivel del mes. Y para calcular el último día del mes se dispone de la función last_day que entrega el último día del mes correspondiente a la fecha que se pasa por parámetros.

select trunc(sysdate, 'mm') primer_dia_del_mes,
trunc(last_day(sysdate)) ultimo_dia_del_mes
from dual;

Para el caso del último día se trunca el resultado para quitarle las horas.

Consulta aquí como calcular el primer y último día laborable del mes.

Escribir un nùmero con letras en Oracle

Aquí se muestra una interesante manera de cómo escribir un número con letras sin desarrollar una función para esto. Si bien no es una solución “tradicional” es bueno para ver se puede hacer en una sentencia SELECT.

Como ejemplo para un número 1234 retorna "un mil doscientes treinta y cuatro".

Buscar tablas que contengan un campo determinado

La siguinete consulta puede ser utilizada para encontrar las tablas que contienen determinado campo al que se le conoce parcial o totalmente su nombre.

select owner,
table_name,
column_name Nombre,
substr(data_type decode(char_col_decl_length, null,
decode(data_precision, null, null,
'('data_precision
decode(data_scale,0,')',','data_scale')')),
'('char_col_decl_length')'),1,20) Tipo
from all_tab_columns
where column_name like upper( 'nombre_campo_o_parte') '%'
order by owner, table_name, column_name;

Esta consulta es util por ejemplo para realizar conversiones de datos, saber tablas relacionadas cuando no existen claves foráneas, o encontrar el nombre de una tabla cuando se conoce solamente un campo de la misma.

Buscar tablas por parte de su nombre

Cuando no se conoce exactamente el nombre de una tabla Oracle, con la siguiente consulta se la puede encontrar.

select owner, table_name
from all_tables
where table_name like upper('parte_de_nombre_tabla' '%')
order by owner, table_name;

Consultar los índices de una tabla

Con la siguiente consulta se obtienen los índices de una tabla Oracle, incluye las columnas de los mismos.

select table_owner,
index_name,
column_position pos,
substr(column_name, 1, 30) column_name
from all_ind_columns
where table_name = upper('nombre_de_tabla')
order by table_owner, index_name, pos;

En caso de tener varios esquemas, incluir el nombre de esquema en la condición: and table_owner = 'nombre_esquema'

Consultar los campos de una tabla

Con la siguiente consulta se obtienen los campos y sus caracteríasticas de una tabla o vista de Oracle, el resultado es igual al comando DESC.


select owner, column_id Id, column_name Nombre,
decode(nullable, 'N','NOT NULL') Nulo,
substr(data_type decode(char_col_decl_length, null,
decode(data_precision, null, null,
'(' data_precisiondecode(data_scale,0,')',','
data_scale')')), '('char_col_decl_length')'),1,20) Tipo
from all_tab_columns
where table_name = upper('nombre_de_tabla')
order by owner, column_id;

Cambiar 'nombre_de_tabla' por el nombre de la tabla a consultar. Si en la base de datos se manejan varios esquemas se puede agregar al where el dueño de la tabla: "and owner = 'nombre_esquema'".