Q

Checking table updates: who, when

This Content Component encountered an error

How can I check which table is updated/inserted by which machine/user at what time?

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

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close