Which Tables Are Actually Changing in Your Oracle Database?

When a database contains hundreds or even thousands of tables, how do you quickly find out which ones are actively changing?

Some tables may receive new data every day. Others may be updated occasionally, while many may remain unchanged for weeks or months. Checking each table separately is clearly not practical.

Fortunately, Oracle already tracks the approximate number of rows inserted, updated, and deleted in its tables. The challenge is that this information shows the current state—it does not give us a convenient history of table activity.

The idea behind this solution is simple: capture Oracle’s table-modification information regularly and save it in a separate history table.

For each table in the selected schema, we record:

• Table owner

• Table name

• Snapshot date and time

• Approximate number of inserted rows

• Approximate number of updated rows

• Approximate number of deleted rows

The process runs automatically once a day. After collecting several snapshots, we can easily see:

• Which tables are changing

• Which tables have no recorded changes

• Which tables have the highest modification activity

• How table activity changes between different snapshot dates

This gives us a simple overview of table activity across the entire schema—without checking tables individually and without creating triggers on application tables.

One important detail: Oracle reports approximate modification counters since optimizer statistics were last gathered. Therefore, these values are useful for monitoring and comparison, but they should not be treated as an exact audit history of every committed transaction.

The following example implements this approach using a snapshot table, a PL/SQL procedure, and an Oracle Scheduler job that captures the information automatically every day.

Important Limitation

This method provides table-level change monitoring only. It shows the approximate number of rows inserted, updated, or deleted in each table.

However, it does not identify the specific rows that were added, changed, or removed. It also does not provide row IDs, primary-key values, changed columns, old and new values, or information about the user who performed the operation.

For row-level identification, a different solution—such as auditing, triggers, LogMiner, or GoldenGate—is required.