Know if an entry is being updated in Oracle?

Question

Is it possible to see if an existing table / record is updated from an Oracle database?

Answer 1

In terms of monitoring (not intended to look for previous changes), you have several options, including triggers, threads, and a column with a default value for sysdate. A trigger allows you to execute a bit of programming logic (stored directly in a trigger or in an external database object) whenever a record changes (insert, update, delete). Streams can be used to track changes by monitoring redo logs. One of the easiest is to add a date column with a default value for sysdate.

Answer 2

Do you speak within or outside a transaction?

In our program, we can use things like SQL% ROWCOUNT to see if 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> 

SQL% FOUND ( SQL% NOTFOUND).

ORA_ROWSCN, , .

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>

ORA_ROWSCN . , ROWDEPENCIES.

. , . . Enterprise Edition, Fine Grained Auditing: , FGA .

Answer 3

, , last_ddl_time user_objects.

Without the use of triggers or materialized logs (which would be a complete hack), I don’t know how to see when any particular row in the table has been updated.