create10

Source: Dev.to

SQL 쿼리

SELECT 
    utc.table_name,
    xt.rows_last_45_days
FROM (
    SELECT table_name
    FROM   user_tab_columns
    WHERE  UPPER(column_name) = 'INTERNTIMESTAMP'
      AND  data_type LIKE 'TIMESTAMP%'
) utc
CROSS JOIN XMLTABLE(
    '/ROWSET/ROW/C/text()'
    PASSING DBMS_XMLGEN.GETXMLTYPE(
        'SELECT COUNT(*) c FROM "' || utc.table_name || 
        '" WHERE INTERNTIMESTAMP >= SYSDATE - 45'
    )
    COLUMNS rows_last_45_days NUMBER PATH '.'
) xt
ORDER BY xt.rows_last_45_days DESC NULLS LAST, utc.table_name;