How to LOG all SQL statements by user to create a AUDITING

[isonlyme]

Hi Guys!

I want to store all transactions made by users (by transactions I mean SQl statements) to store it in a different table. I mean If a user ran an SQL statement to update delete or anything i want to store it for tracking.

I know monitoring programs show in realtime what the user is running but they dont store it.

I know this is doable in some way but i dont know wich tables to query and so on or if exist an application for this kind of thing?

Any advise will be really appreciated

Thanks in advanced!!!

 

[SantaMufasa]

Wilfredo,

Do you want to store the actual SQL DML statements that users run, or do you want to audit record changes showing WHO, did WHAT, to WHOM, and WHEN? In other words, do you want to store SQL commands or do you want to store OLD and NEW values for fields along with the date/time and user who made the change?

Mufasa
(aka Dave of Sandy, Utah, USA @ 22:56 (06Oct04) UTC (aka "GMT" and "Zulu"), 15:56 (06Oct04) Mountain Time)

 

[isonlyme]

HI

no just the SQL commands with the user and maybe if posible the date thast it...

Thanks in advance

 

[SantaMufasa]

Wilfredo,

Unfortunatly, Oracle is not going to give you much help. About the only thing you asked for that you can query and see are the SQL commands. First, find out how many SQL statements are currently processing, then second, if there are not too many, you can query them:

1) Select count(*) from v$sqlarea;
2) set linesize 132
   col sql_text format a132
   select sql_text from v$sqlarea where rownum <= <nn>;

Replace <nn> with some small number so that the query does not run away with your screen.

Sorry I didn't have better news for you.

Mufasa
(aka Dave of Sandy, Utah, USA @ 10:29 (11Oct04) UTC (aka "GMT" and "Zulu"), 03:29 (11Oct04) Mountain Time)

 

[khobar]

Hi

May be you can try to use logminer to find out what are the sql statements issued on a particular table in a particular saved redolog file but may not be able to tell which user issued the sql statement.


khobar