Sunday, August 31, 2025

Batch Execution:

SET SERVEROUTPUT ON;
-- Step 1: Drop and recreate tables
BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE tablespace_control';
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -942 THEN NULL;
    ELSE RAISE;
    END IF;
END;
/
BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE tablespace_log';
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -942 THEN NULL;
    ELSE RAISE;
    END IF;
END;
/
BEGIN
  EXECUTE IMMEDIATE q'[
    CREATE TABLE tablespace_control (
      tablespace_name VARCHAR2(30) PRIMARY KEY,
      initial_size_gb NUMBER NOT NULL,
      batch_id NUMBER, -- Added for batch processing
      status VARCHAR2(10) DEFAULT 'PENDING' CHECK (status IN ('PENDING', 'RUNNING', 'COMPLETED', 'FAILED')),
      retry_count NUMBER DEFAULT 0,
      last_attempt TIMESTAMP,
      created_by VARCHAR2(30) DEFAULT USER,
      CONSTRAINT chk_size_positive CHECK (initial_size_gb > 0)
    )
  ]';
  DBMS_OUTPUT.PUT_LINE('Created tablespace_control');
END;
/
BEGIN
  EXECUTE IMMEDIATE q'[
    CREATE TABLE tablespace_log (
      log_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
      tablespace_name VARCHAR2(30),
      batch_id NUMBER, -- Added for batch tracking
      action VARCHAR2(20),
      status VARCHAR2(10),
      error_message VARCHAR2(4000),
      log_time TIMESTAMP DEFAULT SYSTIMESTAMP,
      session_user VARCHAR2(30) DEFAULT USER,
      session_id NUMBER DEFAULT SYS_CONTEXT('USERENV', 'SID')
    )
  ]';
  DBMS_OUTPUT.PUT_LINE('Created tablespace_log');
END;
/
-- Step 2: Populate tablespace_control with batch assignments
BEGIN
  INSERT INTO tablespace_control (tablespace_name, initial_size_gb, batch_id)
  VALUES ('TS_REVANTH', 6268, 1);
  INSERT INTO tablespace_control (tablespace_name, initial_size_gb, batch_id)
  VALUES ('TABLESPACE2', 3000, 1);
  INSERT INTO tablespace_control (tablespace_name, initial_size_gb, batch_id)
  VALUES ('TABLESPACE3', 2500, 1);
  -- Add remaining 61 tablespaces with batch_id (5 per batch, last batch may have fewer)
  -- Example loop for testing (replace with your actual data):
  /*
  FOR i IN 4..64 LOOP
    INSERT INTO tablespace_control (tablespace_name, initial_size_gb, batch_id)
    VALUES ('TABLESPACE' || i, CASE WHEN MOD(i, 2) = 0 THEN 3000 ELSE 2000 END, CEIL(i/5));
  END LOOP;
  */
  COMMIT;
  DBMS_OUTPUT.PUT_LINE('Populated tablespace_control with batch assignments');
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error populating tablespace_control: ' || SQLERRM);
    RAISE;
END;
/
-- Step 3: Create multiple scheduler jobs for batch processing
DECLARE
  v_common_clause CONSTANT VARCHAR2(500) := 'AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING DEFAULT NO INMEMORY ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON';
  v_max_retries CONSTANT NUMBER := 3;
  v_batch_size CONSTANT NUMBER := 5; -- Tablespaces per batch
  v_job_name VARCHAR2(30);
  v_sql VARCHAR2(1000);
  v_batch_count NUMBER;
BEGIN
  DBMS_OUTPUT.ENABLE(1000000);
  -- Get number of batches
  SELECT CEIL(COUNT(*) / v_batch_size) INTO v_batch_count
  FROM tablespace_control;
  -- Create a job for each batch
  FOR i IN 1..v_batch_count LOOP
    v_job_name := USER || '.CREATE_TS_BATCH_' || i;
    
    -- Drop existing job if it exists
    BEGIN
      DBMS_SCHEDULER.DROP_JOB(v_job_name, force => TRUE);
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE = -27475 THEN NULL;
        ELSE RAISE;
        END IF;
    END;
    -- Create job for batch i
    DBMS_SCHEDULER.CREATE_JOB(
      job_name        => v_job_name,
      job_type        => 'PLSQL_BLOCK',
      job_action      => q'[
        DECLARE
          v_sql VARCHAR2(1000);
          v_common_clause CONSTANT VARCHAR2(500) := 'AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING DEFAULT NO INMEMORY ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON';
          v_max_retries CONSTANT NUMBER := 3;
          v_error_msg VARCHAR2(4000);
        BEGIN
          FOR rec IN (
            SELECT tablespace_name, initial_size_gb, retry_count
            FROM tablespace_control
            WHERE batch_id = ]' || i || q'[ AND status IN ('PENDING', 'FAILED')
            ORDER BY tablespace_name
          ) LOOP
            IF NOT REGEXP_LIKE(rec.tablespace_name, '^[A-Z][A-Z0-9_]{1,29}$') THEN
              v_error_msg := 'Invalid tablespace name: ' || rec.tablespace_name;
              UPDATE tablespace_control SET status = 'FAILED', retry_count = retry_count + 1, last_attempt = SYSTIMESTAMP
              WHERE tablespace_name = rec.tablespace_name;
              INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, error_message)
              VALUES (rec.tablespace_name, ]' || i || q'[, 'VALIDATE', 'FAILED', v_error_msg);
              COMMIT;
              CONTINUE;
            END IF;
            DECLARE
              v_exists NUMBER;
            BEGIN
              SELECT COUNT(*) INTO v_exists FROM DBA_TABLESPACES WHERE tablespace_name = rec.tablespace_name;
              IF v_exists > 0 THEN
                UPDATE tablespace_control SET status = 'COMPLETED', last_attempt = SYSTIMESTAMP
                WHERE tablespace_name = rec.tablespace_name;
                INSERT INTO tablespace_log (tablespace_name, batch_id, action, status)
                VALUES (rec.tablespace_name, ]' || i || q'[, 'SKIP_EXISTS', 'COMPLETED');
                COMMIT;
                CONTINUE;
              END IF;
            END;
            UPDATE tablespace_control SET status = 'RUNNING', last_attempt = SYSTIMESTAMP
            WHERE tablespace_name = rec.tablespace_name;
            COMMIT;
            v_sql := 'CREATE BIGFILE TABLESPACE ' || rec.tablespace_name || ' DATAFILE SIZE ' || rec.initial_size_gb || 'G ' || v_common_clause;
            BEGIN
              EXECUTE IMMEDIATE v_sql;
              UPDATE tablespace_control SET status = 'COMPLETED', retry_count = 0, last_attempt = SYSTIMESTAMP
              WHERE tablespace_name = rec.tablespace_name;
              INSERT INTO tablespace_log (tablespace_name, batch_id, action, status)
              VALUES (rec.tablespace_name, ]' || i || q'[, 'CREATE', 'COMPLETED');
              COMMIT;
            EXCEPTION
              WHEN OTHERS THEN
                v_error_msg := SQLERRM;
                UPDATE tablespace_control SET status = 'FAILED', retry_count = retry_count + 1, last_attempt = SYSTIMESTAMP
                WHERE tablespace_name = rec.tablespace_name;
                INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, error_message)
                VALUES (rec.tablespace_name, ]' || i || q'[, 'CREATE', 'FAILED', v_error_msg);
                COMMIT;
                IF rec.retry_count + 1 < v_max_retries THEN
                  INSERT INTO tablespace_log (tablespace_name, batch_id, action, status)
                  VALUES (rec.tablespace_name, ]' || i || q'[, 'AUTO_RETRY', 'SCHEDULED');
                  COMMIT;
                END IF;
            END;
          END LOOP;
        END;
      ]',
      start_date      => SYSTIMESTAMP,
      repeat_interval => NULL,
      enabled         => TRUE,
      auto_drop       => FALSE,
      comments        => 'Create bigfile tablespaces for batch ' || i
    );
    DBMS_OUTPUT.PUT_LINE('Submitted job ' || v_job_name || ' for batch ' || i);
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('All batch jobs submitted. Monitor with DBA_SCHEDULER_JOBS and tablespace_log.');
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error submitting jobs: ' || SQLERRM);
END;
/





SELECT 'INSERT INTO tablespace_control (tablespace_name, initial_size_gb, batch_id) VALUES (''' || 
       REGEXP_SUBSTR(line, 'TABLESPACE\s+(\w+)', 1, 1, NULL, 1) || ''', ' || 
       REGEXP_SUBSTR(line, 'SIZE\s+(\d+)', 1, 1, NULL, 1) || ', ' ||
       CEIL(ROWNUM/5) || ');'
FROM (/* Paste your 64 CREATE statements */);


example:

BEGIN
-- Batch 1 (batch_id = 1)
INSERT INTO tablespace_control (tablespace_name, initial_size_gb, batch_id) VALUES ('TS_REVANTH', 1, 1);
INSERT INTO tablespace_control (tablespace_name, initial_size_gb, batch_id) VALUES ('TABLESPACE2', 1, 1);
INSERT INTO tablespace_control (tablespace_name, initial_size_gb, batch_id) VALUES ('TABLESPACE3', 1, 1);
INSERT INTO tablespace_control (tablespace_name, initial_size_gb, batch_id) VALUES ('TS_DATA_04', 1, 1);
INSERT INTO tablespace_control (tablespace_name, initial_size_gb, batch_id) VALUES ('TS_DATA_05', 1, 1);

-- Batch 2 (batch_id = 2)
INSERT INTO tablespace_control (tablespace_name, initial_size_gb, batch_id) VALUES ('TS_DATA_06', 1, 2);
INSERT INTO tablespace_control (tablespace_name, initial_size_gb, batch_id) VALUES ('TS_DATA_07', 1, 2);
INSERT INTO tablespace_control (tablespace_name, initial_size_gb, batch_id) VALUES ('TS_DATA_08', 1, 2);
INSERT INTO tablespace_control (tablespace_name, initial_size_gb, batch_id) VALUES ('TS_DATA_09', 1, 2);
INSERT INTO tablespace_control (tablespace_name, initial_size_gb, batch_id) VALUES ('TS_DATA_10', 1, 2);

-- Batch 3 (batch_id = 3)
INSERT INTO tablespace_control (tablespace_name, initial_size_gb, batch_id) VALUES ('TS_DATA_11', 1, 3);
INSERT INTO tablespace_control (tablespace_name, initial_size_gb, batch_id) VALUES ('TS_DATA_12', 1, 3);
INSERT INTO tablespace_control (tablespace_name, initial_size_gb, batch_id) VALUES ('TS_DATA_13', 1, 3);
INSERT INTO tablespace_control (tablespace_name, initial_size_gb, batch_id) VALUES ('TS_DATA_14', 1, 3);
INSERT INTO tablespace_control (tablespace_name, initial_size_gb, batch_id) VALUES ('TS_DATA_15', 1, 3);

    • Verify:
      sql
      SELECT batch_id, COUNT(*) FROM tablespace_control GROUP BY batch_id;
  2. Run the Script:
    • In TOAD, execute with SET SERVEROUTPUT ON;.
    • Output example:
      text
      Created tablespace_control
      Created tablespace_log
      Populated tablespace_control with batch assignments
      Submitted job YOUR_USER.CREATE_TS_BATCH_1 for batch 1
      Submitted job YOUR_USER.CREATE_TS_BATCH_2 for batch 2
      ...
      All batch jobs submitted. Monitor with DBA_SCHEDULER_JOBS and tablespace_log.
  3. Monitor:
    • Job status:
      sql
      SELECT job_name, owner, state, run_count, failure_count
      FROM DBA_SCHEDULED_JOBS
      WHERE job_name LIKE 'CREATE_TS_BATCH_%';
    • Run details:
      sql
      SELECT job_name, status, start_date, run_duration, additional_info
      FROM DBA_SCHEDULER_JOB_RUN_DETAILS
      WHERE job_name LIKE 'CREATE_TS_BATCH_%'
      ORDER BY start_date DESC;
    • Logs:
      sql
      SELECT log_id, tablespace_name, batch_id, action, status, error_message, log_time
      FROM tablespace_log
      WHERE action IN ('CREATE', 'SKIP_EXISTS', 'AUTO_RETRY', 'VALIDATE')
      ORDER BY batch_id, log_time DESC;
    • Status:
      sql
      SELECT batch_id, tablespace_name, initial_size_gb, status, retry_count
      FROM tablespace_control
      ORDER BY batch_id, tablespace_name;
  4. Retry Failures:
    sql
    UPDATE tablespace_control
    SET status = 'PENDING', retry_count = 0
    WHERE status = 'FAILED';
    COMMIT;
    Re-run the script to resubmit jobs for failed batches.
  5. Stop Jobs (if stuck):
    sql
    BEGIN
    FOR rec IN (SELECT job_name FROM DBA_SCHEDULER_JOBS WHERE job_name LIKE 'CREATE_TS_BATCH_%') LOOP
    DBMS_SCHEDULER.STOP_JOB(rec.job_name, force => TRUE);
    DBMS_OUTPUT.PUT_LINE('Stopped: ' || rec.job_name);
    END LOOP;
    END;
    /
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)