Windows – Logging and saving user activity

logsoracleoracle-11g-r2windows

Database:Oracle 11gR2
OS: Windows 2008 Server R2

I'm not trained DBA, just have to run things on an Oracle database temporarily, which is used to perform OLAP processes.

I need to automatize user activity logging (about 30 users) and saving this data (as a text file) if possible.

What I need to log:

SQL_TEXT,
PARSING_SCHEMA_NAME,
FIRST_LOAD_TIME,
DISK_READS,
ROWS_PROCESSED,
ELAPSED_TIME, 
SERVICE,
MODULE,
IP_ADDRESS

It would be great to automatically save this logs on an hourly basis.

I've made a research about RMAN, but it seems complicated for me at this stage.

Thanks.

Best Answer

Oracle has pretty extensive auditing capabilities. This page is part of the Oracle 2 Day + security guide. It will run you through some of the capabilities, plus has a tutorial on setting up an audit trail.

Each hour you could query DBA_AUDIT_TRAIL and write the results out to a text file.

Related Question