What you are trying to do is called "auditing". You can implement auditing in one of two methods in Oracle. For the first method, you can create a trigger on the table in question. When the user tries to UPDATE or INSERT into that table, capture information about the user's session and insert it into your own auditing table. For the second method, you can use Oracle's native auditing facility. This is discussed in the Oracle9i Database Administrator's Guide, Chapter 26, Auditing Database Use (http://download-east.oracle.com/otndoc/oracle9i/901_doc/server.901/a90117/audit.htm#1108).
To use auditing in this way, you must first set your AUDIT_TRAIL initialization parameter to TRUE. Then bounce your database. This turns on the audit facility, but it doesn't capture any information you tell it to. To capture information on a specific table, use a command similar to:
AUDIT SELECT, INSERT, DELETE ON myschema.mytable BY ACCESS WHENEVER SUCCESSFUL;As this statement says, whenever someone performs an INSERT, DELETE or SELECT statement against MYTABLE, and that statement is successful, then that transaction gets logged.
These transactions are logged to the SYS.AUD$ table. You can use the DBA_AUDIT_TRAIL view to see the contents. There are many other DBA_AUDIT views as well.
Auditing can be a tricky proposition, but hopefully I've given you a start. The manual mentioned above is a must read for anyone looking at implementing auditing.
For More Information
- What do you think about this answer? E-mail the editors at editor@searchDatabase.com with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in February 2002