Determining which table was just 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.

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
 

Audit INSERT TABLE by user;
Audit UPDATE VIEW by USER;
Audit UPDATE TABLE BY user;
 

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.