Oracle - ¿Cómo sé qué consulta y qué usuario la ha hecho?

Hola:
Necesitaría saber qué usuarios están conectados a mi base de datos (Oracle 9i) y a qué tablas está accediendo en un determinado momento. ¿Guarda algún tipo de Log? ¿Guarda algún histórico? ¿Se puede hacer a través de alguna consulta SQL?
¿Y cómo lo puedo hacer desde el Entrerprise Manager?
Gracias

Activa tu auditoría en Oracle.

Pasos:

1) al init.ora agrega un parámetro llamado "Audit_trail =TRUE"
cierra y abre la base de datos.
(Si estas usando un spfile solo abrela con el PFILE=PATH\init.ora) y recrea el spifile).
2) realiza la siguiente sentencia, con algun usuario con privilegios de SYSDBA:

SQL> Audit session;

Listo tienes la auditoria de conexiones a la base de datos, incluso te audita las conexiones fallidas y existosas, asi como las desconexiones.

Todos los datos se almacenana en la tabla AUD$ y consultas la informacion con las vistas de Rendimiento de oracle "DBA" ejemplo DBA_AUDIT_SESSION

Te mando un Query para que lo mandes ejecutar una vez que tu auditoria esta activada:
COL OS_USERNAME FOR A15
COL TERMINAL FOR A10
COL STATUS FOR a12
COL CONECTADO FOR A20
COL DESCONECCION FOR A20
set lines 200

select os_username,username,terminal,decode(
returncode, '0','connectado','1005','sinpassword','1017','fallido',returncode) STATUS,
TO_CHAR(timestamp,'DD-MM-YYYY HH24:MI:SS') CONECTADO,
TO_CHAR(LOGOFF_TIME,'DD-MM-YYYY HH24:MI:SS') DESCONECCION
FROM DBA_AUDIT_SESSION
/

suerte!

Una vez activada tu auditoria si quieres revisar lo que han hecho los usuarios a los objetos puedes hacer una auditoria a los objetos.

SELECT 'AUDIT SELECT, INSERT, DELETE, UPDATE ON usuario_dueño_de_objetos.'||OBJECT_NAME|| ' BY ACCESS;'
FROM DBA_OBJECTS
WHERE OBJECT_TYPE = 'TABLE'
AND OWNER = 'usuario_dueño_de_objetos'
/

y con el siguinete querie puedes visualizar que realizaron los usuarios en los objetos:

SELECT OS_USERNAME,USERNAME,TERMINAL,OWNER,OBJ_NAME,ACTION_NAME,DECODE
(RETURNCODE,'0','SUCCESS',RETURNCODE) ACCION,
TO_CHAR(TIMESTAMP,'DD-MM-YYYY HH24:MI:SS') TIEMPO
FROM DBA_AUDIT_OBJECT
/

Advertencia, cuida que la tabla AUD$ de sys no crezca demaciado, puede llegar al limite de storage tu disco si tienes como autoextend tu datafile de system.

Afectaría tu pereformance por las escrituras de Auditoria

¿Cómo puedo hacer para que se desactive cuando estamos haciendo las cargas? Lo digo porque tenemos cargas que tardan mucho tiempo y si además tiene que ir agregando registros a la tabla de auditorías se podría ir mucho de hora.

Gracias.

Mensaje 1 de 2
Simple:
Existen 2 formas.
1)

Si tu Versión de base de datos la inicializas con el init.ora:
La forma mas rápida de desactivar Toda la auditoría es editando tu init de la base de datos pongas el parámetro "AUDIT_TRAIL=NONE", Cierres tu base de datos y la vuelvas a abrir.

Si tu Versión de base de datos inicializa con el SPFILE
Dianámicamente con un usuario con privilegios de SYSDBA alteras el systema poniendo el parámetro "AUDIT_TRAIL=NONE".

¿Como verificas si la auditoria no esta disponible en la base de datos?
Con el siguiente query:

col VALUE for a20
col name for a20

SQL> select name,value
from v$parameter
where name = 'audit_trail'
/
NAME VALUE
____________________ ____________________
audit_trail NONE

mensaje 2 de 2

2)

Otra forma de desactivarla es crear un Script que indique que no deseas auditar los objetos:

Ejemplo:

Para no auditar las conexiones a la base de datos:

SQL> noaudit session;

y para no auditar las sentencia a los objetos:

SELECT 'NOAUDIT ALL ON owner.'||OBJECT_NAME|| ' BY ACCESS;'
FROM DBA_OBJECTS
WHERE OBJECT_TYPE IN ('TABLE','VIEW','SEQUENCE','PROCEDURE,','PACKAGE','FUNCTION','SNAPSHOTS')
AND OWNER = 'owner';

Esta forma es la mas tardada, puesto por que cuando tengas que auditar nuevamente las sentencias tienes que volver a ejecutar el script, pero no tienes que dar de baja la instancia.

Como puedes visualizar, ambas formas de desactivar la auditoria tienen ventajas y desventajas, pero esto es lo que puedes hacer para no perjudicar tu performance en la ejecución batch del diseño del sistema que estes implementando.

Cuando vuelvas a activar tu aditoría, Solo recuerda que tienes que verificar el incremento de tu tabla sys.aud$ que no crezca mas de lo que requieres por que en esta tabla es donde se almacena toda la información.

Suerte..!

Jorge - DBA