Tracking DML activities on particular table

Question

How to track all DML activity on a particular table in oracle database. I need to monitor it.

Answer 1

Use Oracle auditing or fine grained auditing. The very, very most basic configuration:

1. set the audit_trail initialization parameter and restart the database:

    SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
   
    System altered.
   
    SQL> SHUTDOWN
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> STARTUP
    ORACLE instance started.
   
    Total System Global Area  289406976 bytes
    Fixed Size                  1248600 bytes
    Variable Size              71303848 bytes
    Database Buffers          213909504 bytes
    Redo Buffers                2945024 bytes
    Database mounted.
    Database opened.
    SQL>
   

2. configure auditing on your table:

   audit insert, update, delete on [owner].[table_name] by access;
   

3. Use the DBA_AUDIT_TRAIL view to see the audit records.

Both of those are rather large topics to cover in detail here, but you can start researching/reading on practical examples here for Oracle 11g:

• https://docs.oracle.com/cd/E11882_01/server.112/e10575/tdpsg_auditing.htm#TDPSG50000
• https://oracle-base.com/articles/10g/auditing-10gr2