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?
LVL 2
brgdotnetcontractorAsked:
Who is Participating?
 
Mark GeerlingsConnect With a Mentor Database AdministratorCommented:
Maybe.

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).
2
 
Abhimanyu SuriConnect With a Mentor Sr Database 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)
          text,
       sql_opname,
       current_obj#,
       machine,
       program,
       (SELECT username
          FROM dba_users u
         WHERE u.user_id = h.user_id)
          uname
  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.

Thanks,
Suri
0
 
Geert GConnect With a Mentor Oracle 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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.