old version - lets see

This is very important and also be careful in changing the values especially tablespace_name, storage and batch_id remember it is very important.

MERGE INTO tablespace_control t
  USING (SELECT 'TS_REVANTH_6' AS tablespace_name, 6268 AS initial_size_gb, 6 AS batch_id FROM DUAL)

/*

 v_min_new_batch_id NUMBER := 6; -- Start from batch_id 6 to skip 1-5
  v_max_new_batch_id NUMBER := 8; -- Cap at batch_id 8 to avoid spill over

*/

SET SERVEROUTPUT ON;
-- Step 1: Create tables if they don't exist
BEGIN
  EXECUTE IMMEDIATE q'[
    CREATE TABLE tablespace_control (
      tablespace_name VARCHAR2(30) PRIMARY KEY,
      initial_size_gb NUMBER NOT NULL,
      batch_id NUMBER,
      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');
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -955 THEN -- Table already exists
      DBMS_OUTPUT.PUT_LINE('Table tablespace_control already exists, skipping creation');
    ELSE
      RAISE;
    END IF;
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,
      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');
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -955 THEN -- Table already exists
      DBMS_OUTPUT.PUT_LINE('Table tablespace_log already exists, skipping creation');
    ELSE
      RAISE;
    END IF;
END;
/
-- Step 2: Populate tablespace_control with new batch assignments (conditional to avoid duplicates)
BEGIN
  -- Insert new tablespaces if not exists (example for batch_id 6-8)
  MERGE INTO tablespace_control t
  USING (SELECT 'TS_REVANTH_6' AS tablespace_name, 6268 AS initial_size_gb, 6 AS batch_id FROM DUAL) s
  ON (t.tablespace_name = s.tablespace_name)
  WHEN NOT MATCHED THEN
  INSERT (tablespace_name, initial_size_gb, batch_id)
  VALUES (s.tablespace_name, s.initial_size_gb, s.batch_id);
  
  MERGE INTO tablespace_control t
  USING (SELECT 'TS_REVANTH_7' AS tablespace_name, 6268 AS initial_size_gb, 7 AS batch_id FROM DUAL) s
  ON (t.tablespace_name = s.tablespace_name)
  WHEN NOT MATCHED THEN
  INSERT (tablespace_name, initial_size_gb, batch_id)
  VALUES (s.tablespace_name, s.initial_size_gb, s.batch_id);
  
  MERGE INTO tablespace_control t
  USING (SELECT 'TS_REVANTH_8' AS tablespace_name, 6268 AS initial_size_gb, 8 AS batch_id FROM DUAL) s
  ON (t.tablespace_name = s.tablespace_name)
  WHEN NOT MATCHED THEN
  INSERT (tablespace_name, initial_size_gb, batch_id)
  VALUES (s.tablespace_name, s.initial_size_gb, s.batch_id);
  
  COMMIT;
  DBMS_OUTPUT.PUT_LINE('Populated tablespace_control with new batch assignments (6-8, skipped existing)');
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error populating tablespace_control: ' || SQLERRM);
    RAISE;
END;
/
-- Step 3: Create scheduler jobs only for new batch_ids within min and max range without existing jobs
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_job_name VARCHAR2(30);
  v_sql VARCHAR2(1000);
  v_created_count NUMBER := 0;
  v_min_new_batch_id NUMBER := 6; -- Start from batch_id 6 to skip 1-5
  v_max_new_batch_id NUMBER := 8; -- Cap at batch_id 8 to avoid spill over
BEGIN
  DBMS_OUTPUT.ENABLE(1000000);
  
  -- Debug: List existing jobs
  DBMS_OUTPUT.PUT_LINE('Existing scheduler jobs:');
  FOR job_rec IN (
    SELECT JOB_NAME, STATE
    FROM DBA_SCHEDULER_JOBS
    WHERE JOB_NAME LIKE UPPER(USER) || '.CREATE_TS_BATCH_%'
    ORDER BY JOB_NAME
  ) LOOP
    DBMS_OUTPUT.PUT_LINE('Job: ' || job_rec.JOB_NAME || ', State: ' || job_rec.STATE);
  END LOOP;
  
  FOR rec IN (
    SELECT DISTINCT batch_id
    FROM tablespace_control
    WHERE batch_id IS NOT NULL
    AND batch_id >= v_min_new_batch_id
    AND batch_id <= v_max_new_batch_id
    AND NOT EXISTS (
      SELECT 1
      FROM DBA_SCHEDULER_JOBS
      WHERE UPPER(JOB_NAME) = UPPER(USER) || '.CREATE_TS_BATCH_' || batch_id
    )
    ORDER BY batch_id
  ) LOOP
    v_job_name := UPPER(USER) || '.CREATE_TS_BATCH_' || rec.batch_id;
    DBMS_OUTPUT.PUT_LINE('Creating job for batch_id: ' || rec.batch_id);
    
    -- Create job for batch rec.batch_id
    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 = ]' || rec.batch_id || 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, ]' || rec.batch_id || q'[, 'VALIDATE', 'FAILED', v_error_msg);
              COMMIT;
              CONTINUE;
            END IF;
            DECLARE
              v_exists NUMBER;
            BEGIN
              SELECT COUNT(*) INTO v_exists FROM DBA_TABLESPACES WHERE UPPER(tablespace_name) = UPPER(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, ]' || rec.batch_id || 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, ]' || rec.batch_id || 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, ]' || rec.batch_id || 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, ]' || rec.batch_id || 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 ' || rec.batch_id
    );
    DBMS_OUTPUT.PUT_LINE('Submitted job ' || v_job_name || ' for batch ' || rec.batch_id || ' (enabled: TRUE)');
    v_created_count := v_created_count + 1;
  END LOOP;
  
  IF v_created_count = 0 THEN
    DBMS_OUTPUT.PUT_LINE('No new batch jobs needed; all required batch_ids (between ' || v_min_new_batch_id || ' and ' || v_max_new_batch_id || ') have jobs.');
  ELSE
    DBMS_OUTPUT.PUT_LINE(v_created_count || ' new batch jobs submitted and enabled for parallel execution. Monitor with DBA_SCHEDULER_JOBS and tablespace_log.');
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error submitting jobs: ' || SQLERRM);
    RAISE;
END;
/

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

Summary for Notes

MERGE Inserts:Efficiently inserts new tablespaces into tablespace_control without duplicating existing records.
Uses MERGE to check for existing tablespace_name before inserting, ensuring idempotency.

• Critical for scripts that rerun to avoid errors and maintain data consistency.
• Example: Inserts TS_REVANTH_6 with batch_id = 6 only if it doesn’t exist.

• v_min_new_batch_id NUMBER := 6;:
  • Filters job creation to batch_ids >= 6, skipping existing jobs for 1–5.
  • Prevents ORA-27477 by avoiding recreation of existing jobs.
  • Allows focused processing of new batch_ids (6–8) in parallel with existing jobs.
  • Adjustable for future runs to accommodate additional batch_ids.

How It Works:

• Filter Condition: The batch_id >= v_min_new_batch_id clause restricts the job creation loop to only process batch_ids 6 and above (e.g., 6, 7, 8).
• Combined with NOT EXISTS: Further ensures jobs are only created for batch_ids without existing scheduler jobs in DBA_SCHEDULER_JOBS.
• Dynamic Control: Setting v_min_new_batch_id to 6 reflects your requirement to start processing from batch_id = 6 (after jobs for 1–5 are already created or running).

SET SERVEROUTPUT ON;

-- Step 1: Create tables if they don't exist

BEGIN

  EXECUTE IMMEDIATE q'[

    CREATE TABLE tablespace_control (

      tablespace_name VARCHAR2(30) PRIMARY KEY,

      initial_size_gb NUMBER NOT NULL,

      batch_id NUMBER,

      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');

EXCEPTION

  WHEN OTHERS THEN

    IF SQLCODE = -955 THEN -- Table already exists

      DBMS_OUTPUT.PUT_LINE('Table tablespace_control already exists, skipping creation');

    ELSE

      RAISE;

    END IF;

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,

      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');

EXCEPTION

  WHEN OTHERS THEN

    IF SQLCODE = -955 THEN -- Table already exists

      DBMS_OUTPUT.PUT_LINE('Table tablespace_log already exists, skipping creation');

    ELSE

      RAISE;

    END IF;

END;

/

-- Step 2: Populate tablespace_control with new batch assignments (conditional to avoid duplicates)

BEGIN

  -- Insert new tablespaces if not exists (example for batch_id 6-8)

  MERGE INTO tablespace_control t

  USING (SELECT 'TS_REVANTH_6' AS tablespace_name, 6268 AS initial_size_gb, 6 AS batch_id FROM DUAL) s

  ON (t.tablespace_name = s.tablespace_name)

  WHEN NOT MATCHED THEN

  INSERT (tablespace_name, initial_size_gb, batch_id)

  VALUES (s.tablespace_name, s.initial_size_gb, s.batch_id);

  

  MERGE INTO tablespace_control t

  USING (SELECT 'TS_REVANTH_7' AS tablespace_name, 6268 AS initial_size_gb, 7 AS batch_id FROM DUAL) s

  ON (t.tablespace_name = s.tablespace_name)

  WHEN NOT MATCHED THEN

  INSERT (tablespace_name, initial_size_gb, batch_id)

  VALUES (s.tablespace_name, s.initial_size_gb, s.batch_id);

  

  MERGE INTO tablespace_control t

  USING (SELECT 'TS_REVANTH_8' AS tablespace_name, 6268 AS initial_size_gb, 8 AS batch_id FROM DUAL) s

  ON (t.tablespace_name = s.tablespace_name)

  WHEN NOT MATCHED THEN

  INSERT (tablespace_name, initial_size_gb, batch_id)

  VALUES (s.tablespace_name, s.initial_size_gb, s.batch_id);

  

  COMMIT;

  DBMS_OUTPUT.PUT_LINE('Populated tablespace_control with new batch assignments (6-8, skipped existing)');

EXCEPTION

  WHEN OTHERS THEN

    DBMS_OUTPUT.PUT_LINE('Error populating tablespace_control: ' || SQLERRM);

    RAISE;

END;

/

-- Step 3: Create scheduler jobs only for new batch_ids (>= 6) without existing jobs

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_job_name VARCHAR2(30);

  v_sql VARCHAR2(1000);

  v_created_count NUMBER := 0;

  v_min_new_batch_id NUMBER := 6; -- Start from batch_id 6 to skip 1-5

BEGIN

  DBMS_OUTPUT.ENABLE(1000000);

  

  -- Debug: List existing jobs

  DBMS_OUTPUT.PUT_LINE('Existing scheduler jobs:');

  FOR job_rec IN (

    SELECT JOB_NAME, STATE

    FROM DBA_SCHEDULER_JOBS

    WHERE JOB_NAME LIKE UPPER(USER) || '.CREATE_TS_BATCH_%'

    ORDER BY JOB_NAME

  ) LOOP

    DBMS_OUTPUT.PUT_LINE('Job: ' || job_rec.JOB_NAME || ', State: ' || job_rec.STATE);

  END LOOP;

  

  FOR rec IN (

    SELECT DISTINCT batch_id

    FROM tablespace_control

    WHERE batch_id IS NOT NULL

    AND batch_id >= v_min_new_batch_id

    AND NOT EXISTS (

      SELECT 1

      FROM DBA_SCHEDULER_JOBS

      WHERE UPPER(JOB_NAME) = UPPER(USER) || '.CREATE_TS_BATCH_' || batch_id

    )

    ORDER BY batch_id

  ) LOOP

    v_job_name := UPPER(USER) || '.CREATE_TS_BATCH_' || rec.batch_id;

    DBMS_OUTPUT.PUT_LINE('Creating job for batch_id: ' || rec.batch_id);

    

    -- Create job for batch rec.batch_id

    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 = ]' || rec.batch_id || 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, ]' || rec.batch_id || q'[, 'VALIDATE', 'FAILED', v_error_msg);

              COMMIT;

              CONTINUE;

            END IF;

            DECLARE

              v_exists NUMBER;

            BEGIN

              SELECT COUNT(*) INTO v_exists FROM DBA_TABLESPACES WHERE UPPER(tablespace_name) = UPPER(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, ]' || rec.batch_id || 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, ]' || rec.batch_id || 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, ]' || rec.batch_id || 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, ]' || rec.batch_id || 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 ' || rec.batch_id

    );

    DBMS_OUTPUT.PUT_LINE('Submitted job ' || v_job_name || ' for batch ' || rec.batch_id || ' (enabled: TRUE)');

    v_created_count := v_created_count + 1;

  END LOOP;

  

  IF v_created_count = 0 THEN

    DBMS_OUTPUT.PUT_LINE('No new batch jobs needed; all required batch_ids (>= ' || v_min_new_batch_id || ') have jobs.');

  ELSE

    DBMS_OUTPUT.PUT_LINE(v_created_count || ' new batch jobs submitted and enabled for parallel execution. Monitor with DBA_SCHEDULER_JOBS and tablespace_log.');

  END IF;

EXCEPTION

  WHEN OTHERS THEN

    DBMS_OUTPUT.PUT_LINE('Error submitting jobs: ' || SQLERRM);

    RAISE;

END;

/

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

Individual job and working fine:

SET SERVEROUTPUT ON;

-- Step 1: Create tables if they don't exist

BEGIN

  EXECUTE IMMEDIATE q'[

    CREATE TABLE tablespace_control (

      tablespace_name VARCHAR2(30) PRIMARY KEY,

      initial_size_gb NUMBER NOT NULL,

      batch_id NUMBER,

      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');

EXCEPTION

  WHEN OTHERS THEN

    IF SQLCODE = -955 THEN -- Table already exists

      DBMS_OUTPUT.PUT_LINE('Table tablespace_control already exists, skipping creation');

    ELSE

      RAISE;

    END IF;

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,

      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');

EXCEPTION

  WHEN OTHERS THEN

    IF SQLCODE = -955 THEN -- Table already exists

      DBMS_OUTPUT.PUT_LINE('Table tablespace_log already exists, skipping creation');

    ELSE

      RAISE;

    END IF;

END;

/

-- Step 2: Insert new tablespace with next batch_id

DECLARE

  v_next_batch_id NUMBER;

BEGIN

  -- Get the next available batch_id

  SELECT NVL(MAX(batch_id), 0) + 1 INTO v_next_batch_id

  FROM tablespace_control;

  

  -- Insert new tablespace with next batch_id

  INSERT INTO tablespace_control (tablespace_name, initial_size_gb, batch_id)

  VALUES ('TS_REVANTH_5', 6268, v_next_batch_id);

  

  COMMIT;

  DBMS_OUTPUT.PUT_LINE('Inserted new tablespace with batch_id ' || v_next_batch_id);

EXCEPTION

  WHEN OTHERS THEN

    DBMS_OUTPUT.PUT_LINE('Error inserting into tablespace_control: ' || SQLERRM);

    RAISE;

END;

/

-- Step 3: Create scheduler job only for the new batch_id

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_job_name VARCHAR2(30);

  v_sql VARCHAR2(1000);

  v_new_batch_id NUMBER;

  v_job_exists NUMBER;

BEGIN

  DBMS_OUTPUT.ENABLE(1000000);

  

  -- Get the new batch_id (highest)

  SELECT MAX(batch_id) INTO v_new_batch_id

  FROM tablespace_control;

  

  IF v_new_batch_id IS NULL THEN

    DBMS_OUTPUT.PUT_LINE('No batches found in tablespace_control.');

    RETURN;

  END IF;

  

  v_job_name := USER || '.CREATE_TS_BATCH_' || v_new_batch_id;

  

  -- Check if job already exists

  SELECT COUNT(*)

  INTO v_job_exists

  FROM DBA_SCHEDULER_JOBS

  WHERE JOB_NAME = v_job_name;

  

  IF v_job_exists = 0 THEN

    -- Create job for the new batch_id

    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 = ]' || v_new_batch_id || 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, ]' || v_new_batch_id || 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, ]' || v_new_batch_id || 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, ]' || v_new_batch_id || 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, ]' || v_new_batch_id || 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, ]' || v_new_batch_id || 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 ' || v_new_batch_id

    );

    DBMS_OUTPUT.PUT_LINE('Submitted job ' || v_job_name || ' for batch ' || v_new_batch_id || ' (enabled: TRUE)');

  ELSE

    DBMS_OUTPUT.PUT_LINE('Job ' || v_job_name || ' already exists, skipping creation');

  END IF;

  

  DBMS_OUTPUT.PUT_LINE('New batch job creation complete. Monitor with DBA_SCHEDULER_JOBS and tablespace_log.');

EXCEPTION

  WHEN OTHERS THEN

    DBMS_OUTPUT.PUT_LINE('Error submitting job: ' || SQLERRM);

END;

/

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

submits individual job with all batch as individual runs: - working fine

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

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 individual batch assignments

BEGIN

  -- Insert tablespace with unique batch_id for each

  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 ('TS_REVANTH_2', 6268, 2);

  INSERT INTO tablespace_control (tablespace_name, initial_size_gb, batch_id)

  VALUES ('TABLESPACE3', 2500, 3);

  -- Add more tablespaces with incrementing batch_id as needed

  COMMIT;

  DBMS_OUTPUT.PUT_LINE('Populated tablespace_control with individual batch assignments');

EXCEPTION

  WHEN OTHERS THEN

    DBMS_OUTPUT.PUT_LINE('Error populating tablespace_control: ' || SQLERRM);

    RAISE;

END;

/

-- Step 3: Create scheduler jobs for each batch (all enabled for parallel execution)

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_job_name VARCHAR2(30);

  v_sql VARCHAR2(1000);

  v_batch_count NUMBER;

BEGIN

  DBMS_OUTPUT.ENABLE(1000000);

  -- Get number of batches based on MAX(batch_id)

  SELECT NVL(MAX(batch_id), 0) INTO v_batch_count

  FROM tablespace_control;

  IF v_batch_count = 0 THEN

    DBMS_OUTPUT.PUT_LINE('No batches found in tablespace_control.');

    RETURN;

  END IF;

  -- 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 || ' (enabled: TRUE)');

  END LOOP;

  DBMS_OUTPUT.PUT_LINE('All batch jobs submitted and enabled for parallel execution. Monitor with DBA_SCHEDULER_JOBS and tablespace_log.');

EXCEPTION

  WHEN OTHERS THEN

    DBMS_OUTPUT.PUT_LINE('Error submitting jobs: ' || SQLERRM);

END;

/

BEGIN

  DBMS_SCHEDULER.STOP_JOB(UPPER(USER) || '.CREATE_TS_BATCH_6', force => TRUE);

  DBMS_SCHEDULER.STOP_JOB(UPPER(USER) || '.CREATE_TS_BATCH_7', force => TRUE);

  DBMS_SCHEDULER.STOP_JOB(UPPER(USER) || '.CREATE_TS_BATCH_8', force => TRUE);

  DBMS_SCHEDULER.DROP_JOB(UPPER(USER) || '.CREATE_TS_BATCH_6', force => TRUE);

  DBMS_SCHEDULER.DROP_JOB(UPPER(USER) || '.CREATE_TS_BATCH_7', force => TRUE);

  DBMS_SCHEDULER.DROP_JOB(UPPER(USER) || '.CREATE_TS_BATCH_8', force => TRUE);

EXCEPTION

  WHEN OTHERS THEN

    IF SQLCODE = -27475 THEN NULL; -- Job does not exist

    ELSE RAISE;

    END IF;

END;

/

DELETE FROM tablespace_control WHERE batch_id IN (6, 7, 8);

COMMIT;

SELECT * FROM tablespace_control WHERE batch_id IN (6, 7, 8); -- Should return no rows

Old Version: 1

 

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;

/