Oracle中类似ALL_TAB_MODIFICATIONS的未使用列检测方案咨询

Great question—this is such a relatable pain point with legacy databases, especially when stale columns risk breaking report accuracy. Let’s dive into your options:

Does Oracle Have a Built-in View Like ALL_TAB_MODIFICATIONS for Columns? #

Short answer: No. Oracle’s ALL_TAB_MODIFICATIONS tracks table-level DML statistics (inserts/updates/deletes counts), but there’s no out-of-the-box view that directly flags columns where:

• All new inserts set the column to NULL
• The column was never updated to a non-NULL value

That said, there are several reliable workarounds tailored to your needs.

Recommended Solutions #

1. Fine-Grained Auditing (FGA) for Column-Level Writes #

Oracle’s Fine-Grained Auditing lets you track exactly when columns are modified to non-NULL values. This is one of the most accurate methods if you can enable it:

• First, create an audit policy for your table (replace your_schema.your_table and target_column with your details):

  BEGIN
    DBMS_FGA.ADD_POLICY(
      object_schema   => 'your_schema',
      object_name     => 'your_table',
      policy_name     => 'audit_non_null_column_changes',
      audit_condition => 'target_column IS NOT NULL',
      audit_column    => 'target_column',
      statement_types => 'INSERT,UPDATE'
    );
  END;
  /
  

• Query the audit trail to see if the column was ever set to non-NULL:

  SELECT DISTINCT object_name, column_name, timestamp
  FROM DBA_FGA_AUDIT_TRAIL
  WHERE object_schema = 'your_schema'
    AND object_name = 'your_table'
    AND column_name = 'target_column';
  

• Note: This only captures activity after the policy is created. If you need historical data, this won’t help—but it’s perfect for ongoing tracking. Also, test performance on busy tables, as auditing adds overhead.

2. Analyze Historical SQL with V$SQL and AWR #

You can check if columns are ever referenced in INSERT/UPDATE statements (to set non-NULL values) or SELECT statements (to catch report usage):

• Check recent SQL in the shared pool:

  SELECT DISTINCT sql_text
  FROM V$SQL
  WHERE sql_text LIKE '%INSERT INTO your_table%'
     OR sql_text LIKE '%UPDATE your_table%';
  

  Look for mentions of your target column in the column lists or SET clauses.

• For longer historical data, use the Automatic Workload Repository (AWR):

  SELECT DISTINCT sql_text
  FROM DBA_HIST_SQLTEXT
  WHERE sql_text LIKE '%your_table%'
    AND (sql_text LIKE '%INSERT%' OR sql_text LIKE '%UPDATE%');
  

• Limitations: SQL in V$SQL ages out over time, so you might miss older queries. AWR retains data based on your retention policy (default is 8 days).

3. Leverage Column Statistics #

If your table has up-to-date statistics, you can check if the column has ever had non-NULL values:

• Query DBA_TAB_COL_STATISTICS:

  SELECT column_name, num_nulls, last_analyzed
  FROM DBA_TAB_COL_STATISTICS
  WHERE owner = 'your_schema'
    AND table_name = 'your_table'
    AND column_name = 'target_column';
  

  If num_nulls equals the total number of rows in the table (from DBA_TABLES.num_rows) across multiple analysis periods, it’s a strong indicator no non-NULL values were ever inserted/updated.
• Caveat: This only works if statistics are collected regularly. Also, it can’t distinguish between "never set to non-NULL" and "set to non-NULL then updated back to NULL".

4. Custom Triggers for Ongoing Tracking #

If you need full visibility into future column changes, create a trigger to log non-NULL updates/inserts:

• First, create a log table:

  CREATE TABLE column_change_log (
    table_name VARCHAR2(128),
    column_name VARCHAR2(128),
    change_type VARCHAR2(10),
    change_timestamp TIMESTAMP DEFAULT SYSTIMESTAMP
  );
  

• Then add a trigger to your target table:

  CREATE OR REPLACE TRIGGER track_non_null_changes
  AFTER INSERT OR UPDATE ON your_schema.your_table
  FOR EACH ROW
  BEGIN
    -- Check INSERT for non-NULL value
    IF INSERTING AND :NEW.target_column IS NOT NULL THEN
      INSERT INTO column_change_log (table_name, column_name, change_type)
      VALUES ('your_table', 'target_column', 'INSERT');
    END IF;
    -- Check UPDATE for non-NULL value (if old was NULL or changed)
    IF UPDATING AND :NEW.target_column IS NOT NULL THEN
      INSERT INTO column_change_log (table_name, column_name, change_type)
      VALUES ('your_table', 'target_column', 'UPDATE');
    END IF;
  END;
  /
  

• Note: Triggers add overhead to DML operations, so test carefully on high-throughput tables.

Final Tips for Your Use Case #

Since your main concern is faulty reports using stale columns:

• Combine write-tracking (audit/triggers) with read-tracking (check V$SQL/AWR for SELECTs on the column) to prioritize columns that are unused in writes but still used in reports.
• Always validate findings manually before deprecating columns—no automated method is 100% perfect, especially with legacy systems.

内容的提问来源于stack exchange，提问作者Tomas Grosup