create8

WITH params AS (
    SELECT SYSDATE - 45 AS cutoff_date FROM dual
),
tables_with_col AS (
    SELECT table_name
    FROM   user_tab_columns
    WHERE  UPPER(column_name) = 'INTERNTIMESTAMP'
      AND  data_type LIKE 'TIMESTAMP%'
)
SELECT 
    t.table_name,
    cnt.rows_last_45_days
FROM   tables_with_col t
     CROSS JOIN LATERAL (
         SELECT COUNT(*) AS rows_last_45_days
         FROM   TABLE(CAST(
                    MULTISET(
                        SELECT 1
                        FROM   (SELECT * FROM table(t.table_name)) src
                        WHERE  src.INTERNTIMESTAMP >= (SELECT cutoff_date FROM params)
                    ) AS SYS.ODCINUMBERLIST
                ))
     ) cnt
ORDER BY cnt.rows_last_45_days DESC, t.table_name;