Know if a record is updated within Oracle?

Question

Is there a option to see if existing table/record from a Oracle database is updated?

Answer 1

You can see if a table definition has change by querying the last_ddl_time from the user_objects view.

Without using triggers or materialized logs (which would be a total hack) there is no way I know of to see when any particular row in a table has been updated.

Answer 2

From a monitoring perspective (not intended to find previous changes), you have several options including but not limited to triggers, streams, and a column with a default value of sysdate. A trigger will allow you to execute a bit of programming logic (stored directly in the trigger or in an external database object) whenever the record changes (insert, update, delete). Streams can be used to track changes by monitoring the redo logs. One of the easiest may be to add a date column with a default value of sysdate.

Answer 3

Are you talking about within a transaction or outside of it?

Within our program we can use things like SQL%ROWCOUNT to see whether our DML succeeded...

SQL> set serveroutput on size unlimited
SQL> begin
  2       update emp
  3       set job = 'SALESMAN', COMM=10
  4       where empno = 8083;
  5      dbms_output.put_line('Number of records updated = '||sql%rowcount);
  6  end;
  7  /
Number of records updated = 1

PL/SQL procedure successfully completed.

SQL> 

Alternatively we might test for SQL%FOUND (or SQL%NOTFOUND).

From outside the transaction we can monitor ORA_ROWSCN to see whether a record has changed.

SQL> select ora_rowscn from emp
  2  where empno = 8083
  3  /

ORA_ROWSCN
----------
  83828715

SQL> update emp
  2      set comm = 25
  3      where empno = 8083
  4  /

1 row updated.

SQL> commit
  2  /

Commit complete.

SQL> select ora_rowscn from emp
  2  where empno = 8083
  3  /

ORA_ROWSCN
----------
  83828780

SQL>

By default ORA_ROWSCN is set at the block level. If you want to track it at the lower level your need to create the table with the ROWDEPENCIES keyword.

These are ad hoc solutions. If you want to proactive monitoring then you need to implementing some form of logging. Using triggers to write log records is a common solution. If you have Enterprise Edition you should consider using Fine Grained Auditing: Dan Morgan's library has a useful demo of how to use FGA to track changes.