Q
Problem solve Get help with specific problems with your technologies, process and projects.

Determining which table was just modified

I have an application that inserts new records into a table. How do I know which table has been modified?

So, you have an application which successfully updates your database, but you don't know what it does. There are a couple of things you -- or actually, your DBA can do. You should discuss the problem with the DBA in general, before suggesting you want to audit a user or a process.

First, your DBA may have an understanding of the application, or may be able to call on the "owner" of the application for the information you need. Second, DBAs tend not to like to enable auditing in a database because of the potential sizing and performance impacts.

You can monitor the activities in the database using an AUDIT functionality.

  • You can audit the database on connection, i.e, when a specific user logins, capture that activity.
  • You can audit on an object level - That is, on a specific table, view, sequence or stored procedure. You would use this option if you anted to know who was updating a specific table, or using a specific view in the database.
  • You can audit on a privilege level- The application which accesses the database has privileges, ie, it can connect, it can select data, etc. If you have the application's user name, you can audit the user by privilege.
  • You can audit specific statements. For instance, you indicate that the application is updating records. You could audit on "Update Table" statements or "Insert Table" statements. Note that if you do this, you may be exposing yourself to a lot of data. Depending on what you audit, and for how long you audit, you may get many gigabytes of data.
To enable auditing:

Auditing must be enabled in the init.ora file for the database. The AUDIT_TRAIL parameter must be set to DB or OS. AUDIT_TRAIL=NONE is default setting, no audit.

Records to the SYS.AUD$ table, which can fill up rapidly. If you enable auditing, you will want to periodically archive, then truncate the table.

To turn on audit for logins,

    Audit session
 
If you know how the application logs in (ie, what the application's user name is):
Audit INSERT TABLE by user;
Audit UPDATE VIEW by USER;
Audit UPDATE TABLE BY user;
 
Chapter 10, Database Security and Auditing, Oracle 9i DBA Handbook

If the application does not make a lot of transactions, you might be able to "catch" the SQL in the buffer. Any transaction in the database must be parsed. The statement is actually stored in the parsed form together with execution plan in the shared pool in the part named SQL AREA. There are queries to retrieve information from the SQL Area. You can check out SQL TIPS from Orace or other sites.


Dig Deeper on Oracle E-Business Suite

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.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close