Validation

 


SELECT
    owner,
    table_name,
    TO_CHAR(num_rows, '999,999,999,999') AS dictionary_row_estimate,
    TO_CHAR(data_mb, '999,999.00') AS size_mb
FROM
    (
        SELECT
            t.owner,
            t.table_name,
            t.num_rows,
            (s.bytes / 1024 / 1024) AS data_mb
        FROM
            all_tables t
        JOIN
            dba_segments s ON t.owner = s.owner AND t.table_name = s.segment_name
        WHERE
            t.owner IN ('HR', 'SALES', 'FINANCE') -- 👈 ADJUST SCHEMAS IF NECESSARY
            AND t.table_name NOT LIKE 'BIN$%'
            AND s.segment_type = 'TABLE'
        ORDER BY
            t.num_rows DESC
    )
WHERE
    ROWNUM <= 10;

SELECT

    'Verified Top 10 Report' AS report_title,

    t.owner AS schema_name,

    t.table_name,

    -- 1. Dictionary Estimate (Often Stale)

    TO_CHAR(t.num_rows, '999,999,999,999') AS dictionary_estimate,

    

    -- 2. Your Verified Live Count (From the parallel system)

    TO_CHAR(l.row_count, '999,999,999,999') AS verified_live_count,

    

    -- 3. Status and Timestamp

    c.status AS final_status,

    TO_CHAR(l.count_timestamp, 'YYYY-MM-DD HH24:MI:SS') AS verification_time

    

FROM

    all_tables t

JOIN

    dba_segments s ON t.owner = s.owner AND t.table_name = s.segment_name

LEFT JOIN

    row_count_control c ON t.owner = c.schema_name AND t.table_name = c.table_name

LEFT JOIN

    row_counts_scheduler_log l ON t.owner = l.schema_name AND t.table_name = l.table_name

WHERE

    t.owner IN ('HR', 'SALES', 'FINANCE')

    AND s.segment_type = 'TABLE'

ORDER BY

    t.num_rows DESC -- Keep the largest tables at the top

FETCH NEXT 10 ROWS ONLY;

SELECT

    '3. Overall Project Completion' AS report_title,

    successful_count AS tables_processed_successfully,

    failed_count AS tables_requiring_dba_fix,

    pct_complete || '%' AS completion_rate

FROM

    row_count_summary;

=====

SET SERVEROUTPUT ON SIZE UNLIMITED

WHENEVER SQLERROR EXIT FAILURE

DECLARE

    -- Variables to hold data from cursors for output formatting

    v_output_line VARCHAR2(500);

    v_total_tasks NUMBER;

    v_pct_complete NUMBER;

    

    -- Cursor for Query 2 (Per-Schema Breakdown)

    CURSOR c_schema_status IS

        SELECT

            schema_name,

            SUM(CASE WHEN status = 'COMPLETE' THEN 1 ELSE 0 END) AS done_count,

            SUM(CASE WHEN status = 'FAILED' THEN 1 ELSE 0 END) AS failed_count,

            ROUND(SUM(CASE WHEN status = 'COMPLETE' THEN 1 ELSE 0 END) / COUNT(*) * 100, 1) AS pct_done

        FROM

            row_count_control

        GROUP BY

            schema_name

        ORDER BY

            pct_done DESC;

            

    -- Cursor for Query 4 (Specific Table Lookup/Validation Example)

    CURSOR c_validation_check IS

        SELECT

            t.owner AS schema_name,

            t.table_name,

            TO_CHAR(t.num_rows, '999,999,999,999') AS estimate,

            TO_CHAR(l.row_count, '999,999,999,999') AS live_count,

            c.status AS final_status

        FROM

            all_tables t

        LEFT JOIN

            row_count_control c ON t.owner = c.schema_name AND t.table_name = c.table_name

        LEFT JOIN

            row_counts_scheduler_log l ON t.owner = l.schema_name AND t.table_name = l.table_name

        WHERE

            t.owner IN ('HR', 'SALES', 'FINANCE')

            -- Limit to 10 rows for a quick validation snapshot

            AND ROWNUM <= 10

        ORDER BY

            t.table_name; 

BEGIN

    DBMS_OUTPUT.PUT_LINE(CHR(10) || '========================================================================');

    DBMS_OUTPUT.PUT_LINE('  I. OVERALL SYSTEM HEALTH DASHBOARD');

    DBMS_OUTPUT.PUT_LINE('========================================================================');

    

    -- Query 1: Overall Dashboard (Using the View)

    SELECT total_tasks, pct_complete 

    INTO v_total_tasks, v_pct_complete

    FROM row_count_summary;

    DBMS_OUTPUT.PUT_LINE(RPAD('Total Workload Tasks:', 30) || TO_CHAR(v_total_tasks, '999,999'));

    DBMS_OUTPUT.PUT_LINE(RPAD('Overall Completion Rate:', 30) || TO_CHAR(v_pct_complete, '990.0') || '%');

    

    -- Query the view for full status line (simpler than fetching 8 columns into variables)

    FOR r IN (SELECT * FROM row_count_summary) LOOP

        DBMS_OUTPUT.PUT_LINE(RPAD('  Success Count:', 30) || TO_CHAR(r.success_count, '999,999'));

        DBMS_OUTPUT.PUT_LINE(RPAD('  Pending Count (Remaining):', 30) || TO_CHAR(r.pending_count, '999,999'));

        DBMS_OUTPUT.PUT_LINE(RPAD('  Failed Count (Requires DBA):', 30) || TO_CHAR(r.failed_count, '999'));

        DBMS_OUTPUT.PUT_LINE(RPAD('  Last Completion Time:', 30) || TO_CHAR(r.last_completion_time, 'HH24:MI:SS'));

    END LOOP;

    DBMS_OUTPUT.PUT_LINE(CHR(10) || '========================================================================');

    DBMS_OUTPUT.PUT_LINE('  II. STATUS BREAKDOWN PER SCHEMA');

    DBMS_OUTPUT.PUT_LINE('========================================================================');

    -- Query 2: Status Breakdown Per Schema

    DBMS_OUTPUT.PUT_LINE(RPAD('SCHEMA', 15) || RPAD('TOTAL_TABLES', 15) || RPAD('COMPLETE', 10) || RPAD('FAILED', 10) || RPAD('%_DONE', 8));

    DBMS_OUTPUT.PUT_LINE(RPAD('-', 58, '-'));

    FOR r_schema IN c_schema_status LOOP

        DBMS_OUTPUT.PUT_LINE(

            RPAD(r_schema.schema_name, 15) || 

            RPAD(TO_CHAR(r_schema.done_count + r_schema.failed_count + r_schema.pending_count, '999,999'), 15) || 

            RPAD(TO_CHAR(r_schema.done_count, '999,999'), 10) ||

            RPAD(TO_CHAR(r_schema.failed_count, '999'), 10) ||

            RPAD(TO_CHAR(r_schema.pct_done, '990.0'), 8)

        );

    END LOOP;

    DBMS_OUTPUT.PUT_LINE(CHR(10) || '========================================================================');

    DBMS_OUTPUT.PUT_LINE('  III. THREE-WAY TABLE COUNT VALIDATION (SAMPLE)');

    DBMS_OUTPUT.PUT_LINE('========================================================================');

    DBMS_OUTPUT.PUT_LINE(RPAD('SCHEMA.TABLE', 30) || RPAD('ALL_TABLES (EST)', 20) || RPAD('LIVE_COUNT', 20) || RPAD('STATUS', 10));

    DBMS_OUTPUT.PUT_LINE(RPAD('-', 80, '-'));

    -- Query 4: Detailed Table Validation

    FOR r_val IN c_validation_check LOOP

        DBMS_OUTPUT.PUT_LINE(

            RPAD(r_val.schema_name || '.' || r_val.table_name, 30) ||

            RPAD(r_val.estimate, 20) ||

            RPAD(r_val.live_count, 20) ||

            RPAD(r_val.final_status, 10)

        );

    END LOOP;

    

    DBMS_OUTPUT.PUT_LINE(CHR(10) || '--- END OF MONITORING REPORT ---');

EXCEPTION

    WHEN OTHERS THEN

        DBMS_OUTPUT.PUT_LINE('!!! FATAL ERROR DURING MONITORING !!! SQLERRM: ' || SQLERRM);

        -- Note: We don't rollback here as this is a read-only process

END;

/

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

1. Overall System Health Dashboard (The Quick Status)

This query gives you the overall progress, success rate, and active job counts from the summary view.

SQL

SELECT
    '1. OVERALL SYSTEM STATUS' AS metric_group,
    t.total_tasks,
    t.successful_count AS tasks_complete,
    t.pending_count AS tasks_waiting,
    t.running_count AS tasks_active,
    t.failed_count AS tasks_failed,
    t.pct_complete || '%' AS completion_rate,
    TO_CHAR(t.last_completion_time, 'YYYY-MM-DD HH24:MI:SS') AS last_success_time
FROM
    row_count_summary t;

---

2. Status Breakdown Per Schema (Workload Health)

This query uses the row_count_control table to show which schemas are complete, failed, or still pending work.

SQL

SELECT
    schema_name,
    COUNT(*) AS total_tables,
    SUM(CASE WHEN status = 'COMPLETE' THEN 1 ELSE 0 END) AS done_count,
    SUM(CASE WHEN status = 'FAILED' THEN 1 ELSE 0 END) AS failed_count,
    SUM(CASE WHEN status = 'PENDING' THEN 1 ELSE 0 END) AS pending_count,
    ROUND(SUM(CASE WHEN status = 'COMPLETE' THEN 1 ELSE 0 END) / COUNT(*) * 100, 1) AS pct_done
FROM
    row_count_control
GROUP BY
    schema_name
ORDER BY
    pct_done DESC, schema_name;

---

3. Detailed Failed Task Report (Troubleshooting)

This query pinpoints every task currently marked as FAILED and shows the exact error message that caused the stall.

SQL

SELECT
    '3. FAILED TASK REPORT' AS metric_group,
    job_partition_id AS batch_id,
    schema_name,
    table_name,
    error_message,
    TO_CHAR(end_time, 'YYYY-MM-DD HH24:MI:SS') AS failure_time
FROM
    row_count_control
WHERE
    status = 'FAILED'
ORDER BY
    end_time DESC;

---

4. Three-Way Table Count Validation (Accuracy Check)

This query proves the integrity of your system by comparing Oracle's stale estimate (ALL_TABLES) against your verified, live count (row_counts_scheduler_log) for a sample of tables.

SQL

SELECT
    '4. LIVE COUNT VALIDATION (SAMPLE)' AS metric_group,
    c.schema_name,
    c.table_name,
    -- 1. Source Estimate (Unverified)
    TO_CHAR(t.num_rows, '999,999,999,999') AS all_tables_estimate,
    -- 2. Your Verified Live Count (Final Result)
    TO_CHAR(l.row_count, '999,999,999,999') AS log_table_live_count,
    -- 3. Control Status
    c.status AS final_control_status
FROM
    all_tables t
LEFT JOIN
    row_count_control c ON t.owner = c.schema_name AND t.table_name = c.table_name
LEFT JOIN
    row_counts_scheduler_log l ON t.owner = l.schema_name AND t.table_name = l.table_name
WHERE
    c.status IN ('COMPLETE', 'PENDING', 'RUNNING') -- Focus on tasks in the control queue
ORDER BY
    c.status DESC, c.schema_name, c.table_name
FETCH NEXT 10 ROWS ONLY; -- Adjust ROWNUM limit as needed