POPULATE TABLE DATA

 

-- CLEAN FINAL REPORT: One clean row per table, no duplicates, perfect for managers

-- Top 50 most active tables across the entire database

SELECT

    "Rank",

    "Schema",

    "Table Name",

    "Rows Inserted",

    "Rows Updated",

    "Rows Deleted",

    "Total DML",

    "Avg Inserts/Day",

    "DML Activity Since",

    "Stats Last Collected"

FROM (

    SELECT

        RANK() OVER (ORDER BY 

            dtm.inserts DESC, 

            dtm.updates DESC, 

            dtm.deletes DESC

        ) AS "Rank",

        dtm.table_owner                                              AS "Schema",

        dtm.table_name                                               AS "Table Name",

        TO_CHAR(dtm.inserts, 'FM999,999,999,990')                    AS "Rows Inserted",

        TO_CHAR(dtm.updates, 'FM999,999,999,990')                     AS "Rows Updated",

        TO_CHAR(dtm.deletes, 'FM999,999,999,990')                    AS "Rows Deleted",

        TO_CHAR(dtm.inserts + dtm.updates + dtm.deletes, 'FM999,999,999,990') AS "Total DML",

        -- Safe average: avoids crazy numbers when stats are fresh

        TO_CHAR(

            ROUND(dtm.inserts / GREATEST(SYSDATE - dtm.timestamp + 1, 1)),

            'FM999,999,990'

        )                                                             AS "Avg Inserts/Day",

        TO_CHAR(dtm.timestamp, 'DD-MON-YYYY HH24:MI')                AS "DML Activity Since",

        TO_CHAR(MAX(st.last_analyzed), 'DD-MON-YYYY HH24:MI')        AS "Stats Last Collected"

    FROM dba_tab_modifications dtm

    LEFT JOIN dba_tab_statistics st

           ON st.owner       = dtm.table_owner

          AND st.table_name  = dtm.table_name

          AND st.object_type = 'TABLE'

    WHERE (dtm.inserts + dtm.updates + dtm.deletes) > 0

      AND dtm.table_owner NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP','GSMADMIN_INTERNAL','XDB','APPQOSSYS','ORACLE_OCM')

    GROUP BY 

        dtm.table_owner, dtm.table_name, dtm.inserts, dtm.updates, dtm.deletes, dtm.timestamp

    ORDER BY "Rank"

)

WHERE ROWNUM <= 50;   -- Top 50 only — clean and readable

================

SELECT

    owner AS "Schema",

    object_name AS "Table Name",

    TO_CHAR(created, 'DD-MON-YYYY HH24:MI') AS "Date Created"

FROM

    dba_objects

WHERE

    owner = 'YOUR_SCHEMA_NAME'  -- <<< REPLACE with the schema name

    AND object_type = 'TABLE'

ORDER BY

    created DESC;

==========

SELECT

    -- 1. RANK & SCHEMA INFO

    RANK() OVER (ORDER BY dtm.inserts DESC, dtm.updates DESC, dtm.deletes DESC) AS "Rank",

    dtm.table_owner AS "Schema",

    dtm.table_name AS "Table Name",

    

    -- 2. DML COUNTS (Formatted)

    TO_CHAR(dtm.inserts, 'FM999,999,999,990') AS "Rows Inserted",

    TO_CHAR(dtm.updates, 'FM999,999,999,990') AS "Rows Updated",

    TO_CHAR(dtm.deletes, 'FM999,999,999,990') AS "Rows Deleted",

    TO_CHAR(dtm.inserts + dtm.updates + dtm.deletes, 'FM999,999,999,990') AS "Total DML",

    -- 3. RATE & TIMESTAMP INFO

    TO_CHAR(ROUND(dtm.inserts / NULLIF((SYSDATE - dtm.timestamp), 0)), 'FM999,999,999,990') AS "Avg Inserts/Day",

    TO_CHAR(dtm.timestamp, 'DD-MON-YYYY HH24:MI') AS "Modifications Since",

    dt.last_analyzed AS "Stats Last Collected", -- Pulled from DBA_TABLES

    ROUND(SYSDATE - dtm.timestamp, 1) AS "Days Since Mods"

FROM

    dba_tab_modifications dtm

JOIN

    dba_tables dt ON dtm.table_owner = dt.owner AND dtm.table_name = dt.table_name

WHERE

    (dtm.inserts > 0 OR dtm.updates > 0 OR dtm.deletes > 0) -- Filter for any DML activity

    AND dtm.table_owner NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP','GSMADMIN_INTERNAL','XDB','APPQOSSYS')

ORDER BY

    "Rank" ASC;