table count

DECLARE

  h1 NUMBER;

BEGIN

  h1 := DBMS_DATAPUMP.ATTACH('JOB_NAME_HERE','OWNER_NAME_HERE');

  DBMS_DATAPUMP.STOP_JOB (

    handle => h1, 

    immediate => 1, 

    abort => 1

  );

  DBMS_DATAPUMP.DETACH(h1);

END;

/

-- Create staging table for counts

CREATE TABLE staging_table_counts (

  owner        VARCHAR2(128),

  table_name   VARCHAR2(128),

  row_count    NUMBER,

  count_date   TIMESTAMP DEFAULT SYSTIMESTAMP

);

-- Create error log table

CREATE TABLE count_error_log (

  owner        VARCHAR2(128),

  table_name   VARCHAR2(128),

  error_msg    VARCHAR2(4000),

  error_time   TIMESTAMP DEFAULT SYSTIMESTAMP

);

-- Create or replace the batch processing procedure

CREATE OR REPLACE PROCEDURE calculate_table_counts_batch (

  p_batch_id        IN NUMBER,

  p_total_batches   IN NUMBER DEFAULT 20  -- ~189 tables/batch for 3775 tables

) AS

  CURSOR c_tables IS

    SELECT owner, table_name

    FROM (

      SELECT owner, table_name, 

             ROW_NUMBER() OVER (ORDER BY owner, table_name) AS rn

      FROM all_tables

      WHERE owner NOT IN ('SYS', 'SYSTEM', 'OUTLN', 'ORDSYS', 'MDSYS')  -- Exclude system schemas

    )

    WHERE MOD(rn - 1, p_total_batches) = p_batch_id - 1;  -- Batch filter

  v_count   NUMBER;

  v_sql     VARCHAR2(4000);

BEGIN

  FOR rec IN c_tables LOOP

    BEGIN

      -- Dynamic SQL with 5-minute timeout and NO_PARALLEL hint

      v_sql := 'BEGIN DBMS_SESSION.SET_NLS(''query_timeout'', ''300''); ' ||

               'SELECT /*+ NO_PARALLEL */ COUNT(*) FROM "' || 

               DBMS_ASSERT.SCHEMA_NAME(rec.owner) || '"."' || 

               DBMS_ASSERT.SQL_OBJECT_NAME(rec.table_name) || '"; END;';

      EXECUTE IMMEDIATE v_sql INTO v_count;

      -- Insert into staging table

      INSERT INTO staging_table_counts (owner, table_name, row_count)

      VALUES (rec.owner, rec.table_name, v_count);

      COMMIT;  -- Commit per table to avoid long transactions

      -- Log success

      DBMS_OUTPUT.PUT_LINE('Processed: ' || rec.owner || '.' || rec.table_name || ' | Count: ' || v_count);

    

    EXCEPTION

      WHEN OTHERS THEN

        -- Log error to table and continue

        INSERT INTO count_error_log (owner, table_name, error_msg)

        VALUES (rec.owner, rec.table_name, SQLERRM);

        COMMIT;

        DBMS_OUTPUT.PUT_LINE('Error for ' || rec.owner || '.' || rec.table_name || ': ' || SQLERRM);

    END;

  END LOOP;

EXCEPTION

  WHEN OTHERS THEN

    DBMS_OUTPUT.PUT_LINE('Batch error: ' || SQLERRM);

    RAISE;

END;

/