CREATE OR REPLACE PROCEDURE count_table_batch(p_batch_id NUMBER) AS
v_sql_count VARCHAR2(200);
v_cnt NUMBER;
v_job_name_log CONSTANT VARCHAR2(30) := 'BATCH_' || p_batch_id;
-- Declare the error variable (CRITICAL FIX)
v_error_msg VARCHAR2(4000);
-- Variables for status output
v_resuming_count NUMBER;
v_status_message VARCHAR2(100);
BEGIN
-- Determine initial status for output
SELECT COUNT(*) INTO v_resuming_count
FROM row_counts_scheduler_log
WHERE batch_id = p_batch_id AND row_count >= 0;
IF v_resuming_count > 0 THEN
v_status_message := 'RESUMING (Skipping ' || v_resuming_count || ' successful tables in this batch)';
ELSE
v_status_message := 'STARTING FRESH';
END IF;
DBMS_OUTPUT.PUT_LINE('--- BATCH ' || p_batch_id || ' Status: ' || v_status_message || ' ---');
-- Cursor: Filters out successfully completed tables (The Resume Logic)
FOR r IN (
SELECT
t.owner,
t.table_name
FROM
all_tables t
LEFT JOIN
row_counts_scheduler_log l
ON (t.owner = l.schema_name AND t.table_name = l.table_name AND l.row_count >= 0)
WHERE
t.owner IN ('HR','SALES','FINANCE') AND t.table_name NOT LIKE 'BIN$%'
AND MOD(ORA_HASH(t.owner||t.table_name),100)+1=p_batch_id
AND l.table_name IS NULL -- Only include tables that haven't been successfully logged
) LOOP
BEGIN
-- 1. COUNT
v_sql_count := 'SELECT /*+ PARALLEL(8) */ COUNT(*) FROM "'||r.owner||'"."'||r.table_name||'"';
EXECUTE IMMEDIATE v_sql_count INTO v_cnt;
-- 2. SAFE DELETE then INSERT (Atomic update logic)
DELETE FROM row_counts_scheduler_log
WHERE schema_name = r.owner AND table_name = r.table_name;
INSERT INTO row_counts_scheduler_log
(schema_name, table_name, row_count, count_timestamp, job_name, batch_id, error_message)
VALUES (r.owner, r.table_name, v_cnt, SYSTIMESTAMP, v_job_name_log, p_batch_id, NULL);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
-- CRITICAL FIX: Capture and truncate SQLERRM safely
v_error_msg := SUBSTR(SQLERRM, 1, 4000);
-- Log failure with explicit column names
INSERT INTO row_counts_scheduler_log
(schema_name, table_name, row_count, count_timestamp, job_name, batch_id, error_message)
VALUES (r.owner, r.table_name, -1, SYSTIMESTAMP, v_job_name_log, p_batch_id, v_error_msg);
COMMIT;
END;
END LOOP;
END;
/
========
2. Final DBMS_SCHEDULER Control Script (The Submission)
This script calculates the required batches and uses the robust create/set/enable sequence to launch all jobs in parallel. It automatically uses the live logged count to provide an accurate status message.
SET SERVEROUTPUT ON SIZE UNLIMITED
DECLARE
c_batch_size CONSTANT NUMBER := 100;
c_job_prefix CONSTANT VARCHAR2(15) := 'BATCH_COUNT_';
v_total_source_count NUMBER;
v_total_logged_count NUMBER; -- Stores the actual current logged count
v_batches_needed NUMBER;
v_job_name VARCHAR2(128);
v_total_submitted NUMBER := 0;
-- Helper procedure for safe DDL cleanup (omitted for brevity, assume defined)
PROCEDURE execute_ddl(p_sql IN VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE p_sql;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE NOT IN (-27475, -2443) THEN RAISE; END IF;
END;
BEGIN
-- 1. Calculate the definitive source count and current logged count
SELECT COUNT(*) INTO v_total_source_count
FROM all_tables WHERE owner IN ('HR','SALES','FINANCE') AND table_name NOT LIKE 'BIN$%';
SELECT COUNT(DISTINCT schema_name || '.' || table_name)
INTO v_total_logged_count
FROM row_counts_scheduler_log
WHERE row_count >= 0;
v_batches_needed := CEIL(v_total_source_count / c_batch_size);
DBMS_OUTPUT.PUT_LINE('===================================================================');
DBMS_OUTPUT.PUT_LINE('MASTER JOB SUBMISSION: ROW COUNT INITIATION');
DBMS_OUTPUT.PUT_LINE('===================================================================');
DBMS_OUTPUT.PUT_LINE('Source Tables Found: ' || v_total_source_count);
DBMS_OUTPUT.PUT_LINE('Currently Logged: ' || v_total_logged_count);
DBMS_OUTPUT.PUT_LINE('Jobs to Submit: ' || v_batches_needed);
DBMS_OUTPUT.PUT_LINE('Status: Submitting ALL jobs for parallel analysis.');
FOR i IN 1..v_batches_needed LOOP
v_job_name := c_job_prefix || LPAD(i, 2, '0');
-- 1. Cleanup old job definitions (Ensures no old job definition conflicts)
execute_ddl('BEGIN DBMS_SCHEDULER.DROP_JOB(''' || v_job_name || ''', TRUE); END;');
-- 2. Create the job DISABLED (CRUCIAL for argument setting safety)
DBMS_SCHEDULER.CREATE_JOB (
job_name => v_job_name, job_type => 'STORED_PROCEDURE', job_action => 'COUNT_TABLE_BATCH',
number_of_arguments => 1, start_date => SYSTIMESTAMP, repeat_interval => NULL, enabled => FALSE
);
-- 3. Set argument and ENABLE (The robust sequence)
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(v_job_name, 1, i);
DBMS_SCHEDULER.ENABLE(v_job_name);
v_total_submitted := v_total_submitted + 1;
END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('Result: ' || v_total_submitted || ' parallel jobs successfully launched.');
DBMS_OUTPUT.PUT_LINE('Monitoring: Use the status script to track live progress.');
DBMS_OUTPUT.PUT_LINE('===================================================================');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('!!! FATAL ERROR DURING JOB SUBMISSION !!!');
DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
ROLLBACK;
END;
/
=================
step 3 - clean up
SET SERVEROUTPUT ON
DECLARE
-- The job prefix used in your submission script
c_job_prefix CONSTANT VARCHAR2(15) := 'BATCH_COUNT_';
v_job_name VARCHAR2(128);
v_jobs_dropped NUMBER := 0;
-- Cursor selects all jobs matching the prefix that belong to the current user
CURSOR job_cur IS
SELECT job_name
FROM dba_scheduler_jobs
WHERE job_name LIKE c_job_prefix || '%'
AND owner = USER;
BEGIN
DBMS_OUTPUT.PUT_LINE('--- INITIATING SYSTEM CLEANUP: DROPPING ALL BATCH JOBS ---');
FOR rec IN job_cur LOOP
v_job_name := rec.job_name;
-- 1. OPTIONAL STOP ATTEMPT (Best effort to terminate running sessions cleanly)
-- If the job is running, we try to stop it first. If it fails, we ignore the error.
BEGIN
DBMS_SCHEDULER.STOP_JOB(job_name => v_job_name, force => TRUE);
EXCEPTION
-- Ignore errors if the job already finished or is not running/stuck
WHEN OTHERS THEN
NULL;
END;
-- 2. DROP the job permanently (The mandatory cleanup action)
BEGIN
-- FORCE => TRUE ensures the drop occurs even if the job was running
DBMS_SCHEDULER.DROP_JOB(job_name => v_job_name, force => TRUE);
v_jobs_dropped := v_jobs_dropped + 1;
DBMS_OUTPUT.PUT_LINE('Dropped job definition: ' || v_job_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('CRITICAL ERROR: Failed to drop job ' || v_job_name || '. SQLERRM: ' || SQLERRM);
END;
END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('Total Job Definitions Removed: ' || v_jobs_dropped);
DBMS_OUTPUT.PUT_LINE('The job queue is now clear of batch tasks.');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('!!! FATAL ERROR DURING CLEANUP: ' || SQLERRM);
ROLLBACK;
END;
/
No comments:
Post a Comment