report4

--! SET IGNORE_SPLIT ON
BEGIN
  DBMS_OUTPUT.PUT_LINE(RPAD('TABLE_NAME',40) ||
                       RPAD('ROWS_LAST_45_DAYS',20) ||
                       'TOTAL_ROWS (approx)   %');
  DBMS_OUTPUT.PUT_LINE(LPAD('-',100,'-'));

  FOR r IN (
    SELECT table_name
    FROM   user_tab_columns
    WHERE  column_name = 'INTERNTIMESTAMP'
      AND  data_type LIKE 'TIMESTAMP%'
    ORDER BY table_name
  )
  LOOP
    DECLARE
      v_cnt   NUMBER := 0;
      v_total NUMBER := 0;
    BEGIN
      EXECUTE IMMEDIATE
        'SELECT COUNT(*) FROM "' || r.table_name ||
        '" WHERE INTERNTIMESTAMP >= SYSDATE - 45'
        INTO v_cnt;

      SELECT num_rows INTO v_total
      FROM   user_tables
      WHERE  table_name = r.table_name;

      DBMS_OUTPUT.PUT_LINE(
        RPAD(r.table_name,39) || ' ' ||
        LPAD(TO_CHAR(v_cnt,'99999999999'),18) || '  ' ||
        LPAD(TO_CHAR(NVL(v_total,0),'99999999999'),18) || '   ' ||
        ROUND(v_cnt/GREATEST(v_total,1)*100,2) || '%'
      );
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(r.table_name || '  →  ERROR: ' || SQLERRM);
    END;
  END LOOP;
END;