Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Oracle Database Transaction History

Posted on 2002-07-15
Medium Priority
Last Modified: 2008-03-03
Does oracle have any built in security feature that keeps track of who added/modified/deledted from the DB and where they did it?

Question by:superdesio
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 35

Accepted Solution

Mark Geerlings earned 200 total points
ID: 7155324

But, that probably isn't the answer you were hoping for.  There are ways to capture this information with an Oracle database but you need to do some work.

One option to capture part of this information is the "auditing" option that Oracle supports.  You can turn auditing on but that only captures part of what you are looking for.  That captures the username, timestamp, table name and type of transaction (insert/update/delete) but not the actual data values or number of records affected.  Auditing also captures lots of additional information that you may not be interested in, but you need to manage, since it can fill up disk in a hurry.

Another option is to write one or more triggers for each table in the database.  They can capture any data you like when a transaction occurs: username, timestamp, table name, type of transaction, "before" values, "after" values, etc. and record this in audit or log tables you set up, or in ASCII log files (if you use utl_file).

Author Comment

ID: 7155352
actully the oracale auditing sounds like what i am looking for. Do you know of a good resource I can read more information on this?  Also does it capture what record in the table was updated/modified/added?


Assisted Solution

psvel earned 200 total points
ID: 7155367
As mentioned by markgeer, Oracle's default auditing feature does not captures the data images in a table and it just logs the transaction details and operations which modifies the metadata.

If you need to audit the data values in a table you need to write a trigger on each table , which can be automated . I have created audit package which contains 3 tables and set of procedures which accompolish the data auditing. If you need it let me know.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Expert Comment

ID: 7156602
Check this out:

This is for oracle 8.1.5

You can find documentation for all versions of Oracle if you do a search on www.google.com or otn.oracle.com.  

This documentation is also included in the documentation that comes with your software.  


Expert Comment

ID: 7160596
logminer, are you in archive log mode?

logminer can let you peek into the arch logs as far back as you have em and you can see who did what.

8i lets you see dml
9i has included support to see ddl as well

be warned, you might be wading through a tonne of data but it's all there!
LVL 35

Expert Comment

by:Mark Geerlings
ID: 7163329
Yes, LogMiner has the DML statements that would need to be executed to repeat (and/or undo) any insert, update or delete statement.  But it is not fast or troublefree, at least in Oracle8i.

Expert Comment

ID: 9392212
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
split between markgeer and psvel
Please leave any comments here within the next seven days.


EE Cleanup Volunteer

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example, show how to take different types of Oracle backups using RMAN.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question