Operating System - HP-UX
1748205 Members
4542 Online
108759 Solutions
New Discussion юеВ

Re: How to monitor data manipulation in Oracle 9i

 
Manuel G
Frequent Advisor

How to monitor data manipulation in Oracle 9i

Hi all:

We want to monitor who and when had changed some data of our database.

Anyone knows any dba tables or views that could help us?

We use to work with several Oracle clients and want to know who had manipulated data stored in a central database.

Thanks in advanced.
6 REPLIES 6
Steve Steel
Honored Contributor

Re: How to monitor data manipulation in Oracle 9i

Hi

look Through

http://web.singnet.com.sg/~petermag/oracle.html

http://www.orafaq.com/faq.htm

A question here will help you more

Steve Steel
If you want truly to understand something, try to change it. (Kurt Lewin)
Yogeeraj_1
Honored Contributor

Re: How to monitor data manipulation in Oracle 9i

hi,

you may wish to have a look at Workspace Manager which enables applications to version database rows, and isolate collections of row versions using workspaces. It requires no changes to application SQL (DML), and is easily managed from Oracle Enterprise Manager. Workspace Manager provides a rich concurrency and security model for long duration transactions. It has a complete set of workspace semantics and efficient storage of versioned data.

A summary of other benefits that it gives:
- See changes made in a workspace in the context of the entire database and as of any point in time or milestone.

- Allow multiple versions of the data to exist in the same table instead of copying and synchronizing multiple copies of the table.

- Free developers from writing custom code and DBAs from adding application specific metadata to track versions of table data.

- Manage access to row versions across all applications

hth
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
harry d brown jr
Honored Contributor

Re: How to monitor data manipulation in Oracle 9i


That's why application developers should incorporate change control logs. Something we always did when I was a developer for a Banking Software firm. Of course another reason why users should NEVER have direct SQL update/insert capibilities at an SQL prompt and why they shouldn't have shell prompt access!

live free or die
harry
Live Free or Die
Brian Crabtree
Honored Contributor

Re: How to monitor data manipulation in Oracle 9i

You have a few options. One is to activate auditing, which might catch some of what you are doing. The other would be to create a trigger that would activate on insert, updates, and deletes against a table, and would log specific information about the transaction (ie: time, username, rowid or row identifiers). This would probably be the easiest, however it would also add a large cost to your transactions.

Brian
Indira Aramandla
Honored Contributor

Re: How to monitor data manipulation in Oracle 9i

To monitor the DML you can lists the table that hold important data. Then create triggers for these tables for INSERT, UPDATE, DELETE, VIEW/SELECT) transactions, to populate a audit table with the following information (username, terminalname, date, time-stamp and type of transaction).

This will add overhead for your transaction processing, but if you feel it is necessary to monitor then it had to be done.

On the other hand if there are only few tables then you can write scripts to monitor the user sessions and e-mail to you when the table data is changed.
Never give up, Keep Trying
R. Allan Hicks
Trusted Contributor

Re: How to monitor data manipulation in Oracle 9i

Log Miner processes archive logfiles to show you who did what to whom and when it was done. This assumes that you are in archive log mode. Log miner is a part of (or an option of) Oracle Enterprise Manager.
"Only he who attempts the absurd is capable of achieving the impossible