Is their a way to see who changed a record in a table ?

Hello Experts,

I am working on an Oracle stored procedure that depends upon a table with about 10 records in it. The table has about 10 columns. Yesterday someone changed a column value and it is causing issues with sql not working. Is there a way for myself or the DBA to determine who the user was who changed the column value in the table?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark GeerlingsDatabase AdministratorCommented:

If the table includes a column with a name something like "updated_by" or "modified_by" *AND* if your application reliably populates this value, then it should be easy to see who changed it.

If the table does not include a column like that (or if someone modified it outside of the application, and did not update that column) then it becomes more difficult.

If your database has auditing turned on (this is off by default in Oracle) you can query the dba_audit_trail to see who updated this table yesterday.

If none of those options give you the information you are looking for, and if your database is in archivelog mode (this is not the default in Oracle, but all production Oracle databases should be in archivelog mode) you can use LogMiner to look at the archived redo log file that includes the time when the change was made.  That can give you the Oracle logon name and possibly a client computer host name and host logon user *IF* a client/server program was used to make the update.  If the update was done through an application server though, LogMiner may not be able to provide much useful information.

The last (and least likely possibility): your UNDO tablespace and/or your online redo may still have information about who did this.  But, that is only likely if your database has had very few changes since this record was changed.  

To be able to answer questions like this in the future, you may want to add audit columns to the table and application logic or a database trigger to populate them, or turn on auditing.  Before you turn on auditing, make sure that you have a plan to manage the volume of data that will create (most of which will likely not be very useful).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Abhimanyu SuriDatabase EngineerCommented:
To add to that, v$ACTIVE_SESSION_HISTORY ( DBA_HIST_ACTIVE_SESS_HISTORY), maybe able to give some insight as well upon Joining with v$sqlarea (or other SQL text and hist views, GV in case of RAC) .

Something like below

SELECT sample_time,sql_id,
       (SELECT sql_text
          FROM v$sqlarea s
         WHERE s.sql_id = h.sql_id)
       (SELECT username
          FROM dba_users u
         WHERE u.user_id = h.user_id)
  FROM v$active_session_history h
 WHERE session_type = 'FOREGROUND' AND sql_opname <> 'SELECT'

Open in new window

Further filtering can be applied to object or machine etc.

NOTE : Will work for Enterprise Edition.

Geert GOracle dbaCommented:
you can switch on auditing for just that table
this will help pinpoint in the future

this way you can have auditing just for the item you want
audit insert,update,delete on schema.table_name by session;

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.