Tool to monitor database data

monitoringoracletools

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

Best Answer

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.

Related Solutions

Mysql – How identify tables with millions of entries

Since the tables you want to check are MyISAM, you can use just one query. For any given table or tables using MyISAM, here is that query:

SET @SecondsAgo = 60;
SELECT
    table_schema,table_name,
    data_length MYDSize,index_length MYISize,
    table_rows,update_time
FROM information_schema.tables
WHERE engine='MyISAM'
    AND table_schema NOT IN ('information_schema','performance_schema','mysql')
    AND update_time > (NOW() - INTERVAL @SecondsAgo SECOND)
ORDER BY update_time DESC;

For each MyISAM table, you will get the following:

table name and database
.MYD size
.MYI size
number of rows in the table
last time the table was updated

You can set @SecondsAgo to choose how granular to scan for changes.

If many tables or the same set of tables appear from this query, you will see which tables are experiencing the most frequent writes and the most growth.

I will leave it to your to determine the weirdness from the results.

How to find the last error codes and messages of failed statements executed by a user

You can create an AFTER SERVERERROR trigger and log the relevant information to a table.

Here is an example: http://www.adp-gmbh.ch/ora/sql/trigger/servererror.html

Best Answer

Related Solutions

Mysql – How identify tables with millions of entries

How to find the last error codes and messages of failed statements executed by a user

Related Question