Tool to monitor database data

Question

I would like to tool that when record data changed inside all tables. so later i would get a report like

table user

added record - 3,user3..

deleted record - 2,user2..

Answer 1

This solution does not require triggers but requires setup and causes a performance hit if you enable it for all tables.

Auditing has been built into Oracle for many releases. There is an article here which goes into some detail. Basically, you turn it on, tune it for what level of detail you want and the output is available as a dba view or as XML.

Check the system parameter AUDIT TRAIL, it sounds like you want the fine grained auditing available from 9i onwards so you can access the SQL_BIND and SQL_TEXT.

There is an SCN number so you can flashback to the state of the data when the change was made.

Keep in mind that there is a performance hit for all this. You must have the hardware and resources available if this is a production system.

Answer 2

You can use Oracle Logminer for this. This is a powerful tool to parse the redo logs.

Check out

• http://download.oracle.com/docs/cd/B10500_01/server.920/a96521/logminer.htm

Quoting an example from there:

Scenario 1: Using LogMiner to Track Changes Made by a Specific User

This example shows how to see all changes made to the database in a specific time range by a single user: joedevo. Connect to the database and then take the following steps:

Create the LogMiner dictionary file.

To use LogMiner to analyze joedevo's data, you must either create a LogMiner dictionary file before any table definition changes are made to tables that joedevo uses or use the online catalog at LogMiner startup. See Extract a LogMiner Dictionary for examples of creating LogMiner dictionaries. This example uses a LogMiner dictionary that has been extracted to the redo log files.

Add redo log files.

Assume that joedevo has made some changes to the database. You can now specify the names of the redo log files that you want to analyze, as follows:

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
       LOGFILENAME => 'log1orc1.ora', -
       OPTIONS => DBMS_LOGMNR.NEW);

If desired, add additional redo log files, as follows:

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
       LOGFILENAME => 'log2orc1.ora', -
       OPTIONS => DBMS_LOGMNR.ADDFILE);

Start LogMiner and limit the search to the specified time range:

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR( -
      DICTFILENAME => 'orcldict.ora', -
      STARTTIME => TO_DATE('01-Jan-1998 08:30:00','DD-MON-YYYY

HH:MI:SS'), - ENDTIME => TO_DATE('01-Jan-1998 08:45:00', 'DD-MON-YYYY HH:MI:SS'));

Query the V$LOGMNR_CONTENTS view.

At this point, the V$LOGMNR_CONTENTS view is available for queries. You decide to find all of the changes made by user joedevo to the salary table. Execute the following SELECT statement:

SQL> SELECT SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS 
       WHERE USERNAME = 'joedevo' AND SEG_NAME = 'salary';

For both the SQL_REDO and SQL_UNDO columns, two rows are returned (the format of the data display will be different on your screen). You discover that joedevo requested two operations: he deleted his old salary and then inserted a new, higher salary. You now have the data necessary to undo this operation.

SQL_REDO                              SQL_UNDO
--------                              --------
delete from SALARY                    insert into SALARY(NAME,

EMPNO, SAL) where EMPNO = 12345 values ('JOEDEVO', 12345, 500) and NAME='JOEDEVO' and SAL=500;

insert into SALARY(NAME, EMPNO, SAL)  delete from SALARY
values('JOEDEVO',12345, 2500)         where EMPNO = 12345
                                      and NAME = 'JOEDEVO'
2 rows selected                       and SAL = 2500;

End the LogMiner session.

Use the DBMS_LOGMNR.END_LOGMNR procedure to finish the LogMiner session properly:

SQL> DBMS_LOGMNR.END_LOGMNR( );

Answer 3

You can utilize DML triggers to do this exact task. This will be an AFTER trigger and you can insert the audited data in a separate audit table.

EDIT: is there a reason why you want to audit ALL tables in the database? Usually there are just a handful of tables where you need to audit what has changed in them.