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