Question :
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.
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.