Monday, September 1, 2025

Simplify




Modifications:

Script to Re-submit Batch ID 3

Below is a script to reset and re-submit the task for batch_id = 3 in the background, including UPDATE statements and a scheduler job creation.

sql
SET SERVEROUTPUT ON;

-- Step 1: Identify tablespace for batch_id = 3
BEGIN
  DBMS_OUTPUT.PUT_LINE('Checking tablespace for batch_id = 3:');
  FOR rec IN (
    SELECT tablespace_name, initial_size_gb, batch_id, status, retry_count
    FROM tablespace_control
    WHERE batch_id = 3
  ) LOOP
    DBMS_OUTPUT.PUT_LINE('Tablespace: ' || rec.tablespace_name || 
                         ', Size: ' || rec.initial_size_gb || ' GB, Status: ' || rec.status || 
                         ', Retry Count: ' || rec.retry_count);
  END LOOP;
END;
/

-- Step 2: Reset status to PENDING for batch_id = 3
BEGIN
  UPDATE tablespace_control
  SET status = 'PENDING', retry_count = 0, last_attempt = NULL
  WHERE batch_id = 3;
  COMMIT;
  DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' tablespaces reset to PENDING for batch_id = 3.');
END;
/

-- Step 3: Create scheduler job for batch_id = 3
DECLARE
  v_job_name VARCHAR2(30);
  v_created_count NUMBER := 0;
BEGIN
  DBMS_OUTPUT.ENABLE(1000000);
  
  -- Drop existing job for batch_id = 3 if it exists
  FOR rec IN (
    SELECT tablespace_name
    FROM tablespace_control
    WHERE batch_id = 3
  ) LOOP
    v_job_name := UPPER(USER) || '.CREATE_TS_' || rec.tablespace_name;
    BEGIN
      DBMS_SCHEDULER.DROP_JOB(job_name => v_job_name, force => TRUE);
      DBMS_OUTPUT.PUT_LINE('Dropped existing job: ' || v_job_name);
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE = -27475 THEN -- Job does not exist
          DBMS_OUTPUT.PUT_LINE('Job ' || v_job_name || ' does not exist, proceeding to create.');
        ELSE
          RAISE;
        END IF;
    END;
  END LOOP;
  
  -- Create new scheduler job for batch_id = 3
  FOR rec IN (
    SELECT tablespace_name, initial_size_gb, batch_id
    FROM tablespace_control
    WHERE batch_id = 3
      AND status = 'PENDING'
  ) LOOP
    v_job_name := UPPER(USER) || '.CREATE_TS_' || rec.tablespace_name;
    DBMS_OUTPUT.PUT_LINE('Creating job for tablespace: ' || rec.tablespace_name);
    
    DBMS_SCHEDULER.CREATE_JOB(
      job_name => v_job_name,
      job_type => 'STORED_PROCEDURE',
      job_action => 'CREATE_SINGLE_TABLESPACE',
      number_of_arguments => 3,
      start_date => SYSTIMESTAMP,
      repeat_interval => NULL,
      enabled => FALSE,
      auto_drop => FALSE,
      comments => 'Retry bigfile tablespace ' || rec.tablespace_name
    );
    
    -- Set arguments: p_tablespace_name, p_initial_size_gb, p_batch_id
    DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
      job_name => v_job_name,
      argument_position => 1,
      argument_value => rec.tablespace_name
    );
    DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
      job_name => v_job_name,
      argument_position => 2,
      argument_value => TO_CHAR(rec.initial_size_gb)
    );
    DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
      job_name => v_job_name,
      argument_position => 3,
      argument_value => TO_CHAR(rec.batch_id)
    );
    
    -- Enable the job
    DBMS_SCHEDULER.ENABLE(v_job_name);
    DBMS_OUTPUT.PUT_LINE('Submitted and enabled job ' || v_job_name || ' for tablespace ' || rec.tablespace_name);
    v_created_count := v_created_count + 1;
  END LOOP;
  
  IF v_created_count = 0 THEN
    DBMS_OUTPUT.PUT_LINE('No jobs created for batch_id = 3; check tablespace_control status.');
  ELSE
    DBMS_OUTPUT.PUT_LINE(v_created_count || ' job(s) created for batch_id = 3.');
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error creating job for batch_id = 3: ' || SQLERRM);
    RAISE;
END;
/

Monitoring After Retry

Monitor the retry attempt with these queries:

  1. Check tablespace_log:
    sql
    SELECT tablespace_name, batch_id, action, status, error_message, start_time, end_time,
       ROUND(
         EXTRACT(DAY FROM (end_time - start_time)) * 1440 +
         EXTRACT(HOUR FROM (end_time - start_time)) * 60 +
         EXTRACT(MINUTE FROM (end_time - start_time)) +
         EXTRACT(SECOND FROM (end_time - start_time)) / 60,
         2
       ) AS duration_minutes
FROM tablespace_log
WHERE batch_id = 3
  AND action = 'CREATE'
ORDER BY start_time DESC;
  2. Check tablespace_monitor:
    sql
    SELECT tablespace_name, batch_id, status, log_status, error_message, start_time, end_time
FROM tablespace_monitor
WHERE batch_id = 3
  AND action_status IN ('Failed', 'Long Running')
ORDER BY batch_id;
  3. Check Scheduler Job:
    sql
    SELECT job_name, state, run_count, failure_count
FROM DBA_SCHEDULER_JOBS
WHERE job_name LIKE UPPER(USER) || '.CREATE_TS_%' AND job_name LIKE '%TS_PROD_03%'; -- Adjust for your tablespace name
SELECT job_name, status, error#, error_msg
FROM DBA_SCHEDULER_JOB_RUN_DETAILS
WHERE job_name LIKE UPPER(USER) || '.CREATE_TS_%' AND job_name LIKE '%TS_PROD_03%';

Notes

  • Batch ID 3: The script targets only batch_id = 3, resetting its status to PENDING and creating a scheduler job to retry in the background.
  • Tablespace Name: The script uses the tablespace name from tablespace_control for batch_id = 3 (e.g., TS_PROD_03 in v1.0 or a custom name like FTI_DATA_03 if using the modified Step 4). Update the monitoring queries with your exact tablespace name if different.
  • Disk Space: Since batch_id = 3 failed due to a disk space issue (addressed in a previous discussion), ensure sufficient disk space (e.g., 50 GB for TS_PROD_03 or your custom size) is available:
    sql
    SELECT SUM(bytes)/1024/1024/1024 AS free_space_gb FROM dba_free_space;
  • Fallback: If the job fails again (e.g., ORA-01031), run manually:
    sql
    EXEC create_single_tablespace('TS_PROD_03', 50, 3); -- Replace with your tablespace name and size

Run the script to retry batch_id = 3 and monitor the results. If you share the exact tablespace name for batch_id = 3, I can refine the script further!


-- Step 4: Define and populate tablespaces with dynamic names and automatic batch_id
DECLARE
  TYPE t_tablespace IS RECORD (
    tablespace_name VARCHAR2(30),
    initial_size_gb NUMBER
  );
  TYPE t_tablespace_list IS TABLE OF t_tablespace;
  v_tablespaces t_tablespace_list := t_tablespace_list(
    t_tablespace('FTI_DATA_01', 10),
    t_tablespace('FTO_INDEX_01', 20),
    t_tablespace('FL8_APP_01', 50),
    t_tablespace('FTI_DATA_02', 15),
    t_tablespace('FTO_INDEX_02', 30),
    t_tablespace('FL8_APP_02', 25),
    t_tablespace('FTI_DATA_03', 40),
    t_tablespace('FTO_INDEX_03', 60),
    t_tablespace('FL8_APP_03', 12),
    t_tablespace('FTI_DATA_04', 18),
    t_tablespace('FTO_INDEX_04', 45),
    t_tablespace('FL8_APP_04', 55),
    t_tablespace('FTI_DATA_05', 10),
    t_tablespace('FTO_INDEX_05', 20),
    t_tablespace('FL8_APP_05', 50),
    t_tablespace('FTI_DATA_06', 15),
    t_tablespace('FTO_INDEX_06', 30),
    t_tablespace('FL8_APP_06', 25),
    t_tablespace('FTI_DATA_07', 40),
    t_tablespace('FTO_INDEX_07', 60),
    t_tablespace('FL8_APP_07', 12),
    t_tablespace('FTI_DATA_08', 18),
    t_tablespace('FTO_INDEX_08', 45),
    t_tablespace('FL8_APP_08', 55)
  );
  v_inserted_count NUMBER := 0;
  v_max_batch_id NUMBER;
BEGIN
  -- Get the maximum batch_id from tablespace_control, default to 0 if none exists
  BEGIN
    SELECT NVL(MAX(batch_id), 0)
    INTO v_max_batch_id
    FROM tablespace_control;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      v_max_batch_id := 0;
  END;

  -- Insert tablespaces with incremental batch_id
  FOR i IN 1..v_tablespaces.COUNT LOOP
    MERGE INTO tablespace_control t
    USING (SELECT v_tablespaces(i).tablespace_name AS tablespace_name,
                  v_tablespaces(i).initial_size_gb AS initial_size_gb,
                  v_max_batch_id + i 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);
    
    IF SQL%ROWCOUNT > 0 THEN
      v_inserted_count := v_inserted_count + 1;
    END IF;
  END LOOP;
  
  COMMIT;
  DBMS_OUTPUT.PUT_LINE('Populated tablespace_control with ' || v_inserted_count || ' new tablespaces starting from batch_id ' || (v_max_batch_id + 1));
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE('Error populating tablespace_control: ' || SQLERRM);
    RAISE;
END;
/

-- Step 5: Create scheduler jobs for each tablespace using CREATE_SINGLE_TABLESPACE
DECLARE
  v_job_name VARCHAR2(30);
  v_created_count NUMBER := 0;
BEGIN
  DBMS_OUTPUT.ENABLE(1000000);
  
  -- Drop existing scheduler jobs
  DBMS_OUTPUT.PUT_LINE('Dropping existing scheduler jobs:');
  FOR job_rec IN (
    SELECT JOB_NAME
    FROM DBA_SCHEDULER_JOBS
    WHERE JOB_NAME LIKE UPPER(USER) || '.CREATE_TS_%'
  ) LOOP
    DBMS_SCHEDULER.DROP_JOB(job_name => job_rec.JOB_NAME, force => TRUE);
    DBMS_OUTPUT.PUT_LINE('Dropped job: ' || job_rec.JOB_NAME);
    v_created_count := v_created_count + 1;
  END LOOP;
  
  IF v_created_count = 0 THEN
    DBMS_OUTPUT.PUT_LINE('No existing scheduler jobs found.');
  ELSE
    DBMS_OUTPUT.PUT_LINE(v_created_count || ' existing scheduler jobs dropped.');
  END IF;
  
  v_created_count := 0;
  -- Create scheduler jobs for each tablespace
  DBMS_OUTPUT.PUT_LINE('Creating scheduler jobs for tablespaces:');
  FOR rec IN (
    SELECT tablespace_name, initial_size_gb, batch_id
    FROM tablespace_control
    WHERE batch_id IS NOT NULL
    ORDER BY batch_id
  ) LOOP
    v_job_name := UPPER(USER) || '.CREATE_TS_' || rec.tablespace_name;
    DBMS_OUTPUT.PUT_LINE('Creating job for tablespace: ' || rec.tablespace_name);
    
    DBMS_SCHEDULER.CREATE_JOB(
      job_name => v_job_name,
      job_type => 'STORED_PROCEDURE',
      job_action => 'CREATE_SINGLE_TABLESPACE',
      number_of_arguments => 3,
      start_date => SYSTIMESTAMP,
      repeat_interval => NULL,
      enabled => FALSE,
      auto_drop => FALSE,
      comments => 'Create bigfile tablespace ' || rec.tablespace_name
    );
    
    -- Set arguments: p_tablespace_name, p_initial_size_gb, p_batch_id
    DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
      job_name => v_job_name,
      argument_position => 1,
      argument_value => rec.tablespace_name
    );
    DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
      job_name => v_job_name,
      argument_position => 2,
      argument_value => TO_CHAR(rec.initial_size_gb)
    );
    DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
      job_name => v_job_name,
      argument_position => 3,
      argument_value => TO_CHAR(rec.batch_id)
    );
    
    -- Enable the job
    DBMS_SCHEDULER.ENABLE(v_job_name);
    DBMS_OUTPUT.PUT_LINE('Submitted and enabled job ' || v_job_name || ' for tablespace ' || rec.tablespace_name);
    v_created_count := v_created_count + 1;
  END LOOP;
  
  IF v_created_count = 0 THEN
    DBMS_OUTPUT.PUT_LINE('No new scheduler jobs created; all tablespaces may already have jobs.');
  ELSE
    DBMS_OUTPUT.PUT_LINE(v_created_count || ' new scheduler 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 creating scheduler jobs: ' || SQLERRM);
    RAISE;
END;
/

Changes:

  • Removed AND batch_id BETWEEN 1 AND 12 from the query, allowing all non-null batch_id entries to be processed.
  • No other changes needed, as the rest of the logic supports dynamic tablespaces.

Integration Instructions

  1. Backup v1.0:
    • Save a copy of TablespaceCreationParallel_v1.0 before modifying.
  2. Replace Step 4:
    • In create_tablespaces_parallel_v1.0.sql, replace the entire Step 4 (from DECLARE to the final /) with the modified Step 4 above.
    • Customize the v_tablespaces list with your actual tablespace names (e.g., FTI_DATA_XYZ, FTO_INDEX_ABC, etc.) and sizes.
  3. Replace Step 5:
    • Replace the entire Step 5 (from DECLARE to the final /) with the modified Step 5 above.
  4. No Changes to Steps 1, 2, 3:
    • Steps 1 (drop tables), 2 (create tables), and 3 (drop/create procedures) remain unchanged, as they are identical in v1.0 and v2.2.

Scaling to 48 or More Tablespaces

To add more tablespaces (e.g., 48):

  • Update the v_tablespaces list in Step 4 with additional entries:
    plsql
    v_tablespaces t_tablespace_list := t_tablespace_list(
  t_tablespace('FTI_DATA_09', 10),
  t_tablespace('FTO_INDEX_09', 20),
  ...
  t_tablespace('FL8_APP_16', 55)
);
  • The batch_id will automatically start from the highest existing batch_id in tablespace_control (e.g., 24 + 1 = 25 for the next batch).
========================================================================

WITH latest_log AS (
  SELECT tablespace_name, batch_id,
         ROW_NUMBER() OVER (PARTITION BY tablespace_name ORDER BY log_id DESC) AS rn
  FROM tablespace_log
  WHERE tablespace_name IN ('TS_PROD_01', 'TS_PROD_02', 'TS_PROD_03', 'TS_PROD_04', 'TS_PROD_05',
                           'TS_PROD_06', 'TS_PROD_07', 'TS_PROD_08', 'TS_PROD_09', 'TS_PROD_10',
                           'TS_PROD_11', 'TS_PROD_12')
)
SELECT
    COALESCE(c.tablespace_name, l.tablespace_name) AS tablespace_name,
    c.batch_id AS control_batch_id,
    l.batch_id AS log_batch_id,
    CASE
        WHEN c.tablespace_name IS NULL THEN 'Missing in tablespace_control'
        WHEN l.tablespace_name IS NULL THEN 'Missing in tablespace_log'
        WHEN c.batch_id != l.batch_id THEN 'Batch ID mismatch'
        WHEN c.batch_id IS NULL AND l.batch_id IS NOT NULL THEN 'Batch ID missing in control'
        WHEN c.batch_id IS NOT NULL AND l.batch_id IS NULL THEN 'Batch ID missing in log'
        ELSE 'Match'
    END AS validation_result
FROM tablespace_control c
FULL OUTER JOIN latest_log l
    ON c.tablespace_name = l.tablespace_name
    AND l.rn = 1
WHERE COALESCE(c.tablespace_name, l.tablespace_name) IN (
        'TS_PROD_01', 'TS_PROD_02', 'TS_PROD_03', 'TS_PROD_04', 'TS_PROD_05',
        'TS_PROD_06', 'TS_PROD_07', 'TS_PROD_08', 'TS_PROD_09', 'TS_PROD_10',
        'TS_PROD_11', 'TS_PROD_12'
    )
ORDER BY COALESCE(c.batch_id, l.batch_id);


SET SERVEROUTPUT ON;

-- Step 1: Drop tables if they exist
BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE tablespace_control PURGE';
  DBMS_OUTPUT.PUT_LINE('Dropped tablespace_control');
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -942 THEN -- Table does not exist
      DBMS_OUTPUT.PUT_LINE('Table tablespace_control does not exist, skipping drop');
    ELSE
      RAISE;
    END IF;
END;
/

BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE tablespace_log PURGE';
  DBMS_OUTPUT.PUT_LINE('Dropped tablespace_log');
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -942 THEN -- Table does not exist
      DBMS_OUTPUT.PUT_LINE('Table tablespace_log does not exist, skipping drop');
    ELSE
      RAISE;
    END IF;
END;
/

-- Step 2: Create tables
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
    DBMS_OUTPUT.PUT_LINE('Error creating tablespace_control: ' || SQLERRM);
    RAISE;
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),
      start_time TIMESTAMP DEFAULT SYSTIMESTAMP,
      end_time TIMESTAMP,
      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
    DBMS_OUTPUT.PUT_LINE('Error creating tablespace_log: ' || SQLERRM);
    RAISE;
END;
/

-- Step 3: Drop existing procedures if they exist
BEGIN
  EXECUTE IMMEDIATE 'DROP PROCEDURE create_tablespace_batch';
  DBMS_OUTPUT.PUT_LINE('Dropped create_tablespace_batch');
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -4043 THEN -- Procedure does not exist
      DBMS_OUTPUT.PUT_LINE('Procedure create_tablespace_batch does not exist, skipping drop');
    ELSE
      RAISE;
    END IF;
END;
/

BEGIN
  EXECUTE IMMEDIATE 'DROP PROCEDURE create_single_tablespace';
  DBMS_OUTPUT.PUT_LINE('Dropped create_single_tablespace');
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -4043 THEN -- Procedure does not exist
      DBMS_OUTPUT.PUT_LINE('Procedure create_single_tablespace does not exist, skipping drop');
    ELSE
      RAISE;
    END IF;
END;
/

-- Step 3a: Create stored procedure for batch tablespace creation (kept for reference but not used)
BEGIN
  EXECUTE IMMEDIATE q'[
    CREATE OR REPLACE PROCEDURE create_tablespace_batch (p_batch_id IN NUMBER) AUTHID CURRENT_USER AS
      v_sql VARCHAR2(1000);
      v_common_clause CONSTANT VARCHAR2(500) := 'AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING DEFAULT COMPRESS FOR OLTP NO INMEMORY EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON';
      v_max_retries CONSTANT NUMBER := 3;
      v_error_msg VARCHAR2(4000);
      v_log_id NUMBER;
      v_sqlerr_msg VARCHAR2(4000);
    BEGIN
      INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, start_time)
      VALUES (NULL, p_batch_id, 'JOB_EXEC', 'RUNNING', SYSTIMESTAMP)
      RETURNING log_id INTO v_log_id;
      COMMIT;
      
      FOR rec IN (
        SELECT tablespace_name, initial_size_gb, retry_count
        FROM tablespace_control
        WHERE batch_id = p_batch_id 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, start_time, end_time)
          VALUES (rec.tablespace_name, p_batch_id, 'VALIDATE', 'FAILED', v_error_msg, SYSTIMESTAMP, SYSTIMESTAMP);
          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, start_time, end_time)
            VALUES (rec.tablespace_name, p_batch_id, 'SKIP_EXISTS', 'COMPLETED', SYSTIMESTAMP, SYSTIMESTAMP);
            COMMIT;
            CONTINUE;
          END IF;
        END;
        UPDATE tablespace_control SET status = 'RUNNING', last_attempt = SYSTIMESTAMP
        WHERE tablespace_name = rec.tablespace_name;
        INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, start_time)
        VALUES (rec.tablespace_name, p_batch_id, 'CREATE', 'RUNNING', SYSTIMESTAMP)
        RETURNING log_id INTO v_log_id;
        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;
          UPDATE tablespace_log SET status = 'COMPLETED', end_time = SYSTIMESTAMP
          WHERE log_id = v_log_id;
          COMMIT;
        EXCEPTION
          WHEN OTHERS THEN
            v_sqlerr_msg := SQLERRM;
            UPDATE tablespace_control SET status = 'FAILED', retry_count = retry_count + 1, last_attempt = SYSTIMESTAMP
            WHERE tablespace_name = rec.tablespace_name;
            UPDATE tablespace_log SET status = 'FAILED', error_message = v_sqlerr_msg, end_time = SYSTIMESTAMP
            WHERE log_id = v_log_id;
            COMMIT;
            IF rec.retry_count + 1 < v_max_retries THEN
              INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, start_time)
              VALUES (rec.tablespace_name, p_batch_id, 'AUTO_RETRY', 'SCHEDULED', SYSTIMESTAMP);
              COMMIT;
            END IF;
        END;
      END LOOP;
      UPDATE tablespace_log SET status = 'COMPLETED', end_time = SYSTIMESTAMP
      WHERE log_id = v_log_id AND status = 'RUNNING';
      COMMIT;
    EXCEPTION
      WHEN OTHERS THEN
        v_sqlerr_msg := SQLERRM;
        UPDATE tablespace_log SET status = 'FAILED', error_message = v_sqlerr_msg, end_time = SYSTIMESTAMP
        WHERE log_id = v_log_id;
        COMMIT;
        RAISE;
    END;
  ]';
  DBMS_OUTPUT.PUT_LINE('Created stored procedure create_tablespace_batch');
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error creating stored procedure create_tablespace_batch: ' || SQLERRM);
    RAISE;
END;
/

-- Step 3b: Create stored procedure for single tablespace creation
BEGIN
  EXECUTE IMMEDIATE q'[
    CREATE OR REPLACE PROCEDURE create_single_tablespace (
      p_tablespace_name IN VARCHAR2,
      p_initial_size_gb IN NUMBER
    ) AUTHID CURRENT_USER AS
      v_sql VARCHAR2(1000);
      v_common_clause CONSTANT VARCHAR2(500) := 'AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING DEFAULT COMPRESS FOR OLTP NO INMEMORY EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON';
      v_max_retries CONSTANT NUMBER := 3;
      v_error_msg VARCHAR2(4000);
      v_log_id NUMBER;
      v_sqlerr_msg VARCHAR2(4000);
      v_retry_count NUMBER;
      v_exists NUMBER;
    BEGIN
      IF NOT REGEXP_LIKE(p_tablespace_name, '^[A-Z][A-Z0-9_]{1,29}$') THEN
        v_error_msg := 'Invalid tablespace name: ' || p_tablespace_name;
        INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, error_message, start_time, end_time)
        VALUES (p_tablespace_name, NULL, 'VALIDATE', 'FAILED', v_error_msg, SYSTIMESTAMP, SYSTIMESTAMP);
        COMMIT;
        RAISE_APPLICATION_ERROR(-20001, v_error_msg);
      END IF;
      SELECT COUNT(*) INTO v_exists
      FROM DBA_TABLESPACES
      WHERE UPPER(tablespace_name) = UPPER(p_tablespace_name);
      IF v_exists > 0 THEN
        INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, start_time, end_time)
        VALUES (p_tablespace_name, NULL, 'SKIP_EXISTS', 'COMPLETED', SYSTIMESTAMP, SYSTIMESTAMP);
        COMMIT;
        RETURN;
      END IF;
      BEGIN
        SELECT retry_count INTO v_retry_count
        FROM tablespace_control
        WHERE tablespace_name = p_tablespace_name;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          v_retry_count := 0;
      END;
      MERGE INTO tablespace_control t
      USING (SELECT p_tablespace_name AS tablespace_name, p_initial_size_gb AS initial_size_gb, NULL AS batch_id FROM DUAL) s
      ON (t.tablespace_name = s.tablespace_name)
      WHEN MATCHED THEN
        UPDATE SET status = 'RUNNING', last_attempt = SYSTIMESTAMP
      WHEN NOT MATCHED THEN
        INSERT (tablespace_name, initial_size_gb, batch_id, status, last_attempt)
        VALUES (s.tablespace_name, s.initial_size_gb, s.batch_id, 'RUNNING', SYSTIMESTAMP);
      INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, start_time)
      VALUES (p_tablespace_name, NULL, 'CREATE', 'RUNNING', SYSTIMESTAMP)
      RETURNING log_id INTO v_log_id;
      COMMIT;
      v_sql := 'CREATE BIGFILE TABLESPACE ' || p_tablespace_name || ' DATAFILE SIZE ' || p_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 = p_tablespace_name;
        UPDATE tablespace_log
        SET status = 'COMPLETED', end_time = SYSTIMESTAMP
        WHERE log_id = v_log_id;
        COMMIT;
      EXCEPTION
        WHEN OTHERS THEN
          v_sqlerr_msg := SQLERRM;
          UPDATE tablespace_control
          SET status = 'FAILED', retry_count = v_retry_count + 1, last_attempt = SYSTIMESTAMP
          WHERE tablespace_name = p_tablespace_name;
          UPDATE tablespace_log
          SET status = 'FAILED', error_message = v_sqlerr_msg, end_time = SYSTIMESTAMP
          WHERE log_id = v_log_id;
          COMMIT;
          IF v_retry_count + 1 < v_max_retries THEN
            INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, start_time)
            VALUES (p_tablespace_name, NULL, 'AUTO_RETRY', 'SCHEDULED', SYSTIMESTAMP);
            COMMIT;
          END IF;
          RAISE;
      END;
    EXCEPTION
      WHEN OTHERS THEN
        v_sqlerr_msg := SQLERRM;
        INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, error_message, start_time, end_time)
        VALUES (p_tablespace_name, NULL, 'CREATE', 'FAILED', v_sqlerr_msg, SYSTIMESTAMP, SYSTIMESTAMP);
        COMMIT;
        RAISE;
    END;
  ]';
  DBMS_OUTPUT.PUT_LINE('Created stored procedure create_single_tablespace');
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error creating stored procedure create_single_tablespace: ' || SQLERRM);
    RAISE;
END;
/

-- Step 4: Define and populate tablespaces with unique batch IDs
DECLARE
  TYPE t_tablespace IS RECORD (
    tablespace_name VARCHAR2(30),
    initial_size_gb NUMBER,
    batch_id NUMBER
  );
  TYPE t_tablespace_list IS TABLE OF t_tablespace;
  v_tablespaces t_tablespace_list := t_tablespace_list(
    t_tablespace('TS_PROD_01', 10, 1),
    t_tablespace('TS_PROD_02', 20, 2),
    t_tablespace('TS_PROD_03', 50, 3),
    t_tablespace('TS_PROD_04', 15, 4),
    t_tablespace('TS_PROD_05', 30, 5),
    t_tablespace('TS_PROD_06', 25, 6),
    t_tablespace('TS_PROD_07', 40, 7),
    t_tablespace('TS_PROD_08', 60, 8),
    t_tablespace('TS_PROD_09', 12, 9),
    t_tablespace('TS_PROD_10', 18, 10),
    t_tablespace('TS_PROD_11', 45, 11),
    t_tablespace('TS_PROD_12', 55, 12)
  );
  v_inserted_count NUMBER := 0;
BEGIN
  FOR i IN 1..v_tablespaces.COUNT LOOP
    MERGE INTO tablespace_control t
    USING (SELECT v_tablespaces(i).tablespace_name AS tablespace_name,
                  v_tablespaces(i).initial_size_gb AS initial_size_gb,
                  v_tablespaces(i).batch_id 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);
    
    IF SQL%ROWCOUNT > 0 THEN
      v_inserted_count := v_inserted_count + 1;
    END IF;
  END LOOP;
  
  COMMIT;
  DBMS_OUTPUT.PUT_LINE('Populated tablespace_control with ' || v_inserted_count || ' new tablespaces (batch IDs 1-12, skipped existing)');
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE('Error populating tablespace_control: ' || SQLERRM);
    RAISE;
END;
/

-- Step 5: Create scheduler jobs for each tablespace using CREATE_SINGLE_TABLESPACE
DECLARE
  v_job_name VARCHAR2(30);
  v_created_count NUMBER := 0;
BEGIN
  DBMS_OUTPUT.ENABLE(1000000);
  
  -- Drop existing scheduler jobs
  DBMS_OUTPUT.PUT_LINE('Dropping existing scheduler jobs:');
  FOR job_rec IN (
    SELECT JOB_NAME
    FROM DBA_SCHEDULER_JOBS
    WHERE JOB_NAME LIKE UPPER(USER) || '.CREATE_TS_%'
  ) LOOP
    DBMS_SCHEDULER.DROP_JOB(job_name => job_rec.JOB_NAME, force => TRUE);
    DBMS_OUTPUT.PUT_LINE('Dropped job: ' || job_rec.JOB_NAME);
    v_created_count := v_created_count + 1;
  END LOOP;
  
  IF v_created_count = 0 THEN
    DBMS_OUTPUT.PUT_LINE('No existing scheduler jobs found.');
  ELSE
    DBMS_OUTPUT.PUT_LINE(v_created_count || ' existing scheduler jobs dropped.');
  END IF;
  
  v_created_count := 0;
  -- Create scheduler jobs for each tablespace
  DBMS_OUTPUT.PUT_LINE('Creating scheduler jobs for tablespaces:');
  FOR rec IN (
    SELECT tablespace_name, initial_size_gb
    FROM tablespace_control
    WHERE batch_id IS NOT NULL
    AND batch_id BETWEEN 1 AND 12
    ORDER BY batch_id
  ) LOOP
    v_job_name := UPPER(USER) || '.CREATE_TS_' || rec.tablespace_name;
    DBMS_OUTPUT.PUT_LINE('Creating job for tablespace: ' || rec.tablespace_name);
    
    DBMS_SCHEDULER.CREATE_JOB(
      job_name => v_job_name,
      job_type => 'STORED_PROCEDURE',
      job_action => 'CREATE_SINGLE_TABLESPACE',
      number_of_arguments => 2,
      start_date => SYSTIMESTAMP,
      repeat_interval => NULL,
      enabled => FALSE, -- Initially disabled to set arguments
      auto_drop => FALSE,
      comments => 'Create bigfile tablespace ' || rec.tablespace_name
    );
    
    -- Set arguments: tablespace_name and initial_size_gb
    DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
      job_name => v_job_name,
      argument_position => 1,
      argument_value => rec.tablespace_name
    );
    DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
      job_name => v_job_name,
      argument_position => 2,
      argument_value => TO_CHAR(rec.initial_size_gb)
    );
    
    -- Enable the job
    DBMS_SCHEDULER.ENABLE(v_job_name);
    DBMS_OUTPUT.PUT_LINE('Submitted and enabled job ' || v_job_name || ' for tablespace ' || rec.tablespace_name);
    v_created_count := v_created_count + 1;
  END LOOP;
  
  IF v_created_count = 0 THEN
    DBMS_OUTPUT.PUT_LINE('No new scheduler jobs created; all tablespaces may already have jobs.');
  ELSE
    DBMS_OUTPUT.PUT_LINE(v_created_count || ' new scheduler 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 creating scheduler jobs: ' || SQLERRM);
    RAISE;
END;
/

Corrected Query

To fix the ORA-00932 error, you can use one of these methods to convert the interval to a numeric value (e.g., minutes):

  1. Use EXTRACT: Extract components (days, hours, minutes, seconds) from the interval and compute the total minutes.
  2. Use NUMTODSINTERVAL or Casting: Convert the interval to a numeric value compatible with multiplication.

Here’s the corrected query using the EXTRACT method, which is straightforward and widely compatible:

sql
SELECT tablespace_name,
       batch_id,
       action,
       status,
       error_message,
       start_time,
       end_time,
       ROUND(
         EXTRACT(DAY FROM (end_time - start_time)) * 1440 +
         EXTRACT(HOUR FROM (end_time - start_time)) * 60 +
         EXTRACT(MINUTE FROM (end_time - start_time)) +
         EXTRACT(SECOND FROM (end_time - start_time)) / 60,
         2
       ) AS duration_minutes,
       CASE
         WHEN end_time IS NULL AND start_time < SYSTIMESTAMP - INTERVAL '30' MINUTE THEN 'Long Running'
         WHEN status = 'FAILED' THEN 'Failed'
         WHEN status = 'COMPLETED' THEN 'Completed'
         ELSE 'In Progress'
       END AS action_status
FROM tablespace_log
WHERE tablespace_name IN ('TS_PROD_01', 'TS_PROD_02', 'TS_PROD_03', 'TS_PROD_04', 'TS_PROD_05',
                         'TS_PROD_06', 'TS_PROD_07', 'TS_PROD_08', 'TS_PROD_09', 'TS_PROD_10',
                         'TS_PROD_11', 'TS_PROD_12')
   OR tablespace_name IS NULL
ORDER BY start_time DESC;

Explanation of Fix

  • Interval Conversion:
    • end_time - start_time produces a DAY TO SECOND INTERVAL.
    • EXTRACT retrieves components:
      • EXTRACT(DAY FROM ...): Number of days.
      • EXTRACT(HOUR FROM ...): Hours (0-23).
      • EXTRACT(MINUTE FROM ...): Minutes (0-59).
      • EXTRACT(SECOND FROM ...): Seconds (including microseconds).
    • Convert to minutes:
      • Days to minutes: days * 1440.
      • Hours to minutes: hours * 60.
      • Minutes: Direct.
      • Seconds to minutes: seconds / 60.
    • Combine: (days * 1440) + (hours * 60) + minutes + (seconds / 60).
  • ROUND: Applies ROUND(..., 2) to the numeric result, giving duration in minutes with two decimal places.
  • Null Handling: If end_time is NULL (action still running), duration_minutes will be NULL, which is appropriate.

Alternative Approach

If you prefer a simpler method, you can use NUMTODSINTERVAL or a timestamp difference in seconds:

sql
SELECT tablespace_name,
       batch_id,
       action,
       status,
       error_message,
       start_time,
       end_time,
       ROUND(
         EXTRACT(DAY FROM (end_time - start_time) * 1440) +
         EXTRACT(HOUR FROM (end_time - start_time)) * 60 +
         EXTRACT(MINUTE FROM (end_time - start_time)) +
         EXTRACT(SECOND FROM (end_time - start_time)) / 60,
         2
       ) AS duration_minutes,
       CASE
         WHEN end_time IS NULL AND start_time < SYSTIMESTAMP - INTERVAL '30' MINUTE THEN 'Long Running'
         WHEN status = 'FAILED' THEN 'Failed'
         WHEN status = 'COMPLETED' THEN 'Completed'
         ELSE 'In Progress'
       END AS action_status
FROM tablespace_log
WHERE tablespace_name IN ('TS_PROD_01', 'TS_PROD_02', 'TS_PROD_03', 'TS_PROD_04', 'TS_PROD_05',
                         'TS_PROD_06', 'TS_PROD_07', 'TS_PROD_08', 'TS_PROD_09', 'TS_PROD_10',
                         'TS_PROD_11', 'TS_PROD_12')
   OR tablespace_name IS NULL
ORDER BY start_time DESC;

This query avoids direct interval multiplication and should work across Oracle versions (e.g., 19c, 23c).

Monitoring with the Corrected Query

To effectively monitor start_time and end_time for your 12 tablespaces in the context of your script (using CREATE_SINGLE_TABLESPACE in scheduler jobs for parallel background execution):

  1. Run the Query:
    • Execute the corrected query above to monitor all actions for the 12 tablespaces (TS_PROD_01 to TS_PROD_12) and JOB_EXEC entries (tablespace_name IS NULL).
    • Example output might look like:
      text
      TABLESPACE_NAME | BATCH_ID | ACTION     | STATUS    | ERROR_MESSAGE                | START_TIME                | END_TIME                  | DURATION_MINUTES | ACTION_STATUS
TS_PROD_01      | 1        | CREATE     | COMPLETED |                              | 2025-09-06 12:00:00 | 2025-09-06 12:05:00 | 5.00             | Completed
TS_PROD_02      | 2        | CREATE     | FAILED    | ORA-01031: insufficient priv | 2025-09-06 12:00:00 | 2025-09-06 12:00:10 | 0.17             | Failed
NULL            | 1        | JOB_EXEC   | COMPLETED |                              | 2025-09-06 12:00:00 | 2025-09-06 12:05:00 | 5.00             | Completed
  2. Focus on Failures:
    • Filter for failed actions to diagnose issues like ORA-01031:
      sql
      SELECT tablespace_name, batch_id, action, status, error_message, start_time, end_time,
       ROUND(
         EXTRACT(DAY FROM (end_time - start_time)) * 1440 +
         EXTRACT(HOUR FROM (end_time - start_time)) * 60 +
         EXTRACT(MINUTE FROM (end_time - start_time)) +
         EXTRACT(SECOND FROM (end_time - start_time)) / 60,
         2
       ) AS duration_minutes
FROM tablespace_log
WHERE status = 'FAILED'
  AND (tablespace_name IN ('TS_PROD_01', 'TS_PROD_02', 'TS_PROD_03', 'TS_PROD_04', 'TS_PROD_05',
                          'TS_PROD_06', 'TS_PROD_07', 'TS_PROD_08', 'TS_PROD_09', 'TS_PROD_10',
                          'TS_PROD_11', 'TS_PROD_12')
       OR tablespace_name IS NULL)
ORDER BY start_time DESC;
  3. Check Long-Running Tasks:
    • Identify actions running longer than 30 minutes:
      sql
      SELECT tablespace_name, batch_id, action, status, start_time,
       ROUND((SYSTIMESTAMP - start_time) * 1440, 2) AS running_minutes
FROM tablespace_log
WHERE end_time IS NULL
  AND start_time < SYSTIMESTAMP - INTERVAL '30' MINUTE
  AND (tablespace_name IN ('TS_PROD_01', 'TS_PROD_02', 'TS_PROD_03', 'TS_PROD_04', 'TS_PROD_05',
                          'TS_PROD_06', 'TS_PROD_07', 'TS_PROD_08', 'TS_PROD_09', 'TS_PROD_10',
                          'TS_PROD_11', 'TS_PROD_12')
       OR tablespace_name IS NULL)
ORDER BY start_time DESC;
  4. Update tablespace_monitor View:
    • Update the view to include the corrected duration calculation:
      sql
      CREATE OR REPLACE VIEW tablespace_monitor AS
SELECT c.tablespace_name,
       c.initial_size_gb,
       c.batch_id,
       c.status,
       c.retry_count,
       l.action,
       l.status AS log_status,
       l.error_message,
       l.start_time,
       l.end_time,
       ROUND(
         EXTRACT(DAY FROM (l.end_time - l.start_time)) * 1440 +
         EXTRACT(HOUR FROM (l.end_time - l.start_time)) * 60 +
         EXTRACT(MINUTE FROM (l.end_time - l.start_time)) +
         EXTRACT(SECOND FROM (l.end_time - l.start_time)) / 60,
         2
       ) AS duration_minutes,
       CASE
         WHEN l.end_time IS NULL AND l.start_time < SYSTIMESTAMP - INTERVAL '30' MINUTE THEN 'Long Running'
         WHEN l.status = 'FAILED' THEN 'Failed'
         WHEN l.status = 'COMPLETED' THEN 'Completed'
         ELSE 'In Progress'
       END AS action_status
FROM tablespace_control c
LEFT JOIN tablespace_log l
   ON c.tablespace_name = l.tablespace_name
   AND l.log_id = (
     SELECT MAX(log_id)
     FROM tablespace_log
     WHERE tablespace_name = c.tablespace_name
   )
ORDER BY c.batch_id, c.tablespace_name;
  5. Monitor with View:
    sql
    SELECT tablespace_name, batch_id, status, log_status, error_message, start_time, end_time, duration_minutes, action_status
FROM tablespace_monitor
WHERE action_status IN ('Failed', 'Long Running')
   OR status IN ('PENDING', 'FAILED')
ORDER BY batch_id;

Context with Your Script

  • Script Status: Your latest script (from my previous response) uses DBMS_SCHEDULER jobs to call CREATE_SINGLE_TABLESPACE for each of the 12 tablespaces, aiming for parallel background execution. If you’re still seeing ORA-01031 errors in tablespace_log, the RDS scheduler restrictions may still apply.
  • Fallback Option: If scheduler jobs fail, use direct execution of CREATE_SINGLE_TABLESPACE (as you tested successfully):
    plsql
    BEGIN
  create_single_tablespace('TS_PROD_01', 10);
  create_single_tablespace('TS_PROD_02', 20);
  create_single_tablespace('TS_PROD_03', 50);
  create_single_tablespace('TS_PROD_04', 15);
  create_single_tablespace('TS_PROD_05', 30);
  create_single_tablespace('TS_PROD_06', 25);
  create_single_tablespace('TS_PROD_07', 40);
  create_single_tablespace('TS_PROD_08', 60);
  create_single_tablespace('TS_PROD_09', 12);
  create_single_tablespace('TS_PROD_10', 18);
  create_single_tablespace('TS_PROD_11', 45);
  create_single_tablespace('TS_PROD_12', 55);
END;
/
    Note: This is sequential and foreground, not parallel or background.

If Scheduler Jobs Fail

If the script’s jobs still fail with ORA-01031:

  1. Check Job Errors:
    sql
    SELECT job_name, status, error#, error_msg
FROM DBA_SCHEDULER_JOB_RUN_DETAILS
WHERE job_name LIKE UPPER(USER) || '.CREATE_TS_%';
  2. Contact AWS Support: Confirm if RDS restricts CREATE TABLESPACE in scheduler jobs, even with AUTHID CURRENT_USER.
  3. Alternative Parallelism: Use a PL/SQL block with DBMS_PARALLEL_EXECUTE for parallel execution without scheduler jobs, if needed.

Please run the corrected query and check tablespace_log and tablespace_monitor for results. If you see errors (e.g., ORA-01031), share the output, and I’ll provide further fixes. Let me know if you need a modified script with a different approach (e.g., DBMS_PARALLEL_EXECUTE)!

If Failures Persist

If CREATE_TABLESPACE_BATCH still fails:

  • Check Errors: Review tablespace_log for new error messages beyond ORA-01031.
  • Verify Procedure:
    sql
    SELECT object_name, status
FROM user_objects
WHERE object_name IN ('CREATE_TABLESPACE_BATCH', 'CREATE_SINGLE_TABLESPACE');
SELECT line, text
FROM user_source
WHERE name = 'CREATE_TABLESPACE_BATCH'
AND text LIKE '%v_common_clause%';
  • RDS Support: Contact AWS support to confirm if RDS restricts CREATE TABLESPACE in procedures, even with direct execution.
  • Fallback: Use CREATE_SINGLE_TABLESPACE for all 12 tablespaces:
    plsql
    BEGIN
  create_single_tablespace('TS_PROD_01', 10);
  create_single_tablespace('TS_PROD_02', 20);
  create_single_tablespace('TS_PROD_03', 50);
  create_single_tablespace('TS_PROD_04', 15);
  create_single_tablespace('TS_PROD_05', 30);
  create_single_tablespace('TS_PROD_06', 25);
  create_single_tablespace('TS_PROD_07', 40);
  create_single_tablespace('TS_PROD_08', 60);
  create_single_tablespace('TS_PROD_09', 12);
  create_single_tablespace('TS_PROD_10', 18);
  create_single_tablespace('TS_PROD_11', 45);
  create_single_tablespace('TS_PROD_12', 55);
END;
/

Summary

This script integrates all fixes (ORA-01031, ORA-00904, ORA-25142, procedure version issues) and uses direct execution to create 12 tablespaces in batches, leveraging the success of CREATE_SINGLE_TABLESPACE. The tablespace_monitor view provides comprehensive tracking. If you encounter new errors, share the output from tablespace_log or tablespace_monitor, and I’ll assist further. Let me know if you need additional tweaks (e.g., parallelism, alerts)!

Monitoring Best Practices (Unchanged)

Status Check:

SELECT tablespace_name, batch_id, status, retry_count, last_attempt
FROM tablespace_control
WHERE status IN ('FAILED', 'PENDING')
ORDER BY batch_id;

Detailed Logs:

SELECT tablespace_name, batch_id, action, status, error_message, ROUND((end_time - start_time) * 1440, 2) AS duration_minutes FROM tablespace_log WHERE status = 'FAILED' ORDER BY start_time DESC;

Scheduler Job Status:

SELECT job_name, state, enabled, run_count, failure_count, last_start_date

FROM DBA_SCHEDULER_JOBS WHERE job_name LIKE UPPER(USER) || '.CREATE_TS_BATCH_%';

Monitoring View:

CREATE OR REPLACE VIEW tablespace_monitor AS
SELECT c.tablespace_name, c.initial_size_gb, c.batch_id, c.status, c.retry_count,
       l.action, l.status AS log_status, l.error_message, l.start_time, l.end_time,
       ROUND((end_time - l.start_time) * 1440, 2) AS duration_minutes
FROM tablespace_control c
LEFT JOIN tablespace_log l
   ON c.tablespace_name = l.tablespace_name
   AND l.log_id = (
     SELECT MAX(log_id)
     FROM tablespace_log
     WHERE tablespace_name = c.tablespace_name
   )
ORDER BY c.batch_id, c.tablespace_name;

UPDATE tablespace_log l
SET l.batch_id = (SELECT c.batch_id FROM tablespace_control c WHERE c.tablespace_name = l.tablespace_name)
WHERE l.tablespace_name = 'TS_PROD_01'
AND l.batch_id IS NULL;

UPDATE tablespace_log l
SET l.batch_id = (SELECT c.batch_id FROM tablespace_control c WHERE c.tablespace_name = l.tablespace_name)
WHERE l.tablespace_name = 'TS_PROD_02'
AND l.batch_id IS NULL;

UPDATE tablespace_log l
SET l.batch_id = (SELECT c.batch_id FROM tablespace_control c WHERE c.tablespace_name = l.tablespace_name)
WHERE l.tablespace_name = 'TS_PROD_03'
AND l.batch_id IS NULL;

UPDATE tablespace_log l
SET l.batch_id = (SELECT c.batch_id FROM tablespace_control c WHERE c.tablespace_name = l.tablespace_name)
WHERE l.tablespace_name = 'TS_PROD_04'
AND l.batch_id IS NULL;

UPDATE tablespace_log l
SET l.batch_id = (SELECT c.batch_id FROM tablespace_control c WHERE c.tablespace_name = l.tablespace_name)
WHERE l.tablespace_name = 'TS_PROD_05'
AND l.batch_id IS NULL;

UPDATE tablespace_log l
SET l.batch_id = (SELECT c.batch_id FROM tablespace_control c WHERE c.tablespace_name = l.tablespace_name)
WHERE l.tablespace_name = 'TS_PROD_06'
AND l.batch_id IS NULL;

UPDATE tablespace_log l
SET l.batch_id = (SELECT c.batch_id FROM tablespace_control c WHERE c.tablespace_name = l.tablespace_name)
WHERE l.tablespace_name = 'TS_PROD_07'
AND l.batch_id IS NULL;

UPDATE tablespace_log l
SET l.batch_id = (SELECT c.batch_id FROM tablespace_control c WHERE c.tablespace_name = l.tablespace_name)
WHERE l.tablespace_name = 'TS_PROD_08'
AND l.batch_id IS NULL;

UPDATE tablespace_log l
SET l.batch_id = (SELECT c.batch_id FROM tablespace_control c WHERE c.tablespace_name = l.tablespace_name)
WHERE l.tablespace_name = 'TS_PROD_09'
AND l.batch_id IS NULL;

UPDATE tablespace_log l
SET l.batch_id = (SELECT c.batch_id FROM tablespace_control c WHERE c.tablespace_name = l.tablespace_name)
WHERE l.tablespace_name = 'TS_PROD_10'
AND l.batch_id IS NULL;

UPDATE tablespace_log l
SET l.batch_id = (SELECT c.batch_id FROM tablespace_control c WHERE c.tablespace_name = l.tablespace_name)
WHERE l.tablespace_name = 'TS_PROD_11'
AND l.batch_id IS NULL;

UPDATE tablespace_log l
SET l.batch_id = (SELECT c.batch_id FROM tablespace_control c WHERE c.tablespace_name = l.tablespace_name)
WHERE l.tablespace_name = 'TS_PROD_12'
AND l.batch_id IS NULL;

COMMIT;

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

version -2 yet to be tested

batch_id generated - fixed in tablespace_log

```plsql
SET SERVEROUTPUT ON;

-- Step 1: Drop tables if they exist
BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE tablespace_control PURGE';
  DBMS_OUTPUT.PUT_LINE('Dropped tablespace_control');
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -942 THEN -- Table does not exist
      DBMS_OUTPUT.PUT_LINE('Table tablespace_control does not exist, skipping drop');
    ELSE
      RAISE;
    END IF;
END;
/

BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE tablespace_log PURGE';
  DBMS_OUTPUT.PUT_LINE('Dropped tablespace_log');
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -942 THEN -- Table does not exist
      DBMS_OUTPUT.PUT_LINE('Table tablespace_log does not exist, skipping drop');
    ELSE
      RAISE;
    END IF;
END;
/

-- Step 2: Create tables
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
    DBMS_OUTPUT.PUT_LINE('Error creating tablespace_control: ' || SQLERRM);
    RAISE;
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),
      start_time TIMESTAMP DEFAULT SYSTIMESTAMP,
      end_time TIMESTAMP,
      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
    DBMS_OUTPUT.PUT_LINE('Error creating tablespace_log: ' || SQLERRM);
    RAISE;
END;
/

-- Step 3: Drop existing procedures if they exist
BEGIN
  EXECUTE IMMEDIATE 'DROP PROCEDURE create_tablespace_batch';
  DBMS_OUTPUT.PUT_LINE('Dropped create_tablespace_batch');
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -4043 THEN -- Procedure does not exist
      DBMS_OUTPUT.PUT_LINE('Procedure create_tablespace_batch does not exist, skipping drop');
    ELSE
      RAISE;
    END IF;
END;
/

BEGIN
  EXECUTE IMMEDIATE 'DROP PROCEDURE create_single_tablespace';
  DBMS_OUTPUT.PUT_LINE('Dropped create_single_tablespace');
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -4043 THEN -- Procedure does not exist
      DBMS_OUTPUT.PUT_LINE('Procedure create_single_tablespace does not exist, skipping drop');
    ELSE
      RAISE;
    END IF;
END;
/

-- Step 3a: Create stored procedure for batch tablespace creation (kept for reference)
BEGIN
  EXECUTE IMMEDIATE q'[
    CREATE OR REPLACE PROCEDURE create_tablespace_batch (p_batch_id IN NUMBER) AUTHID CURRENT_USER AS
      v_sql VARCHAR2(1000);
      v_common_clause CONSTANT VARCHAR2(500) := 'AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING DEFAULT COMPRESS FOR OLTP NO INMEMORY EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON';
      v_max_retries CONSTANT NUMBER := 3;
      v_error_msg VARCHAR2(4000);
      v_log_id NUMBER;
      v_sqlerr_msg VARCHAR2(4000);
    BEGIN
      INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, start_time)
      VALUES (NULL, p_batch_id, 'JOB_EXEC', 'RUNNING', SYSTIMESTAMP)
      RETURNING log_id INTO v_log_id;
      COMMIT;
      
      FOR rec IN (
        SELECT tablespace_name, initial_size_gb, retry_count
        FROM tablespace_control
        WHERE batch_id = p_batch_id 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, start_time, end_time)
          VALUES (rec.tablespace_name, p_batch_id, 'VALIDATE', 'FAILED', v_error_msg, SYSTIMESTAMP, SYSTIMESTAMP);
          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, start_time, end_time)
            VALUES (rec.tablespace_name, p_batch_id, 'SKIP_EXISTS', 'COMPLETED', SYSTIMESTAMP, SYSTIMESTAMP);
            COMMIT;
            CONTINUE;
          END IF;
        END;
        UPDATE tablespace_control SET status = 'RUNNING', last_attempt = SYSTIMESTAMP
        WHERE tablespace_name = rec.tablespace_name;
        INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, start_time)
        VALUES (rec.tablespace_name, p_batch_id, 'CREATE', 'RUNNING', SYSTIMESTAMP)
        RETURNING log_id INTO v_log_id;
        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;
          UPDATE tablespace_log SET status = 'COMPLETED', end_time = SYSTIMESTAMP
          WHERE log_id = v_log_id;
          COMMIT;
        EXCEPTION
          WHEN OTHERS THEN
            v_sqlerr_msg := SQLERRM;
            UPDATE tablespace_control SET status = 'FAILED', retry_count = retry_count + 1, last_attempt = SYSTIMESTAMP
            WHERE tablespace_name = rec.tablespace_name;
            UPDATE tablespace_log SET status = 'FAILED', error_message = v_sqlerr_msg, end_time = SYSTIMESTAMP
            WHERE log_id = v_log_id;
            COMMIT;
            IF rec.retry_count + 1 < v_max_retries THEN
              INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, start_time)
              VALUES (rec.tablespace_name, p_batch_id, 'AUTO_RETRY', 'SCHEDULED', SYSTIMESTAMP);
              COMMIT;
            END IF;
        END;
      END LOOP;
      UPDATE tablespace_log SET status = 'COMPLETED', end_time = SYSTIMESTAMP
      WHERE log_id = v_log_id AND status = 'RUNNING';
      COMMIT;
    EXCEPTION
      WHEN OTHERS THEN
        v_sqlerr_msg := SQLERRM;
        UPDATE tablespace_log SET status = 'FAILED', error_message = v_sqlerr_msg, end_time = SYSTIMESTAMP
        WHERE log_id = v_log_id;
        COMMIT;
        RAISE;
    END;
  ]';
  DBMS_OUTPUT.PUT_LINE('Created stored procedure create_tablespace_batch');
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error creating stored procedure create_tablespace_batch: ' || SQLERRM);
    RAISE;
END;
/

-- Step 3b: Create stored procedure for single tablespace creation
CREATE OR REPLACE PROCEDURE create_single_tablespace (
  p_tablespace_name IN VARCHAR2,
  p_initial_size_gb IN NUMBER,
  p_batch_id IN NUMBER DEFAULT NULL
) AUTHID CURRENT_USER AS
  v_sql VARCHAR2(1000);
  v_common_clause CONSTANT VARCHAR2(500) := 'AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING DEFAULT COMPRESS FOR OLTP NO INMEMORY EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON';
  v_max_retries CONSTANT NUMBER := 3;
  v_error_msg VARCHAR2(4000);
  v_log_id NUMBER;
  v_sqlerr_msg VARCHAR2(4000);
  v_retry_count NUMBER;
  v_exists NUMBER;
BEGIN
  IF p_tablespace_name IS NULL OR TRIM(p_tablespace_name) IS NULL THEN
    v_error_msg := 'Tablespace name cannot be NULL or empty';
    INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, error_message, start_time, end_time)
    VALUES (p_tablespace_name, p_batch_id, 'VALIDATE', 'FAILED', v_error_msg, SYSTIMESTAMP, SYSTIMESTAMP);
    COMMIT;
    RAISE_APPLICATION_ERROR(-20001, v_error_msg);
  END IF;
  IF NOT REGEXP_LIKE(p_tablespace_name, '^[A-Z][A-Z0-9_]{1,29}$') THEN
    v_error_msg := 'Invalid tablespace name: ' || p_tablespace_name;
    INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, error_message, start_time, end_time)
  VALUES (p_tablespace_name, p_batch_id, 'VALIDATE', 'FAILED', v_error_msg, SYSTIMESTAMP, SYSTIMESTAMP);
    COMMIT;
    RAISE_APPLICATION_ERROR(-20001, v_error_msg);
  END IF;
  SELECT COUNT(*) INTO v_exists
  FROM DBA_TABLESPACES
  WHERE UPPER(tablespace_name) = UPPER(p_tablespace_name);
  IF v_exists > 0 THEN
    INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, start_time, end_time)
    VALUES (p_tablespace_name, p_batch_id, 'SKIP_EXISTS', 'COMPLETED', SYSTIMESTAMP, SYSTIMESTAMP);
    COMMIT;
    RETURN;
  END IF;
  BEGIN
    SELECT retry_count INTO v_retry_count
    FROM tablespace_control
    WHERE tablespace_name = p_tablespace_name;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      v_retry_count := 0;
  END;
  MERGE INTO tablespace_control t
  USING (SELECT p_tablespace_name AS tablespace_name, p_initial_size_gb AS initial_size_gb, p_batch_id AS batch_id FROM DUAL) s
  ON (t.tablespace_name = s.tablespace_name)
  WHEN MATCHED THEN
    UPDATE SET status = 'RUNNING', last_attempt = SYSTIMESTAMP, batch_id = s.batch_id
  WHEN NOT MATCHED THEN
    INSERT (tablespace_name, initial_size_gb, batch_id, status, last_attempt)
    VALUES (s.tablespace_name, s.initial_size_gb, s.batch_id, 'RUNNING', SYSTIMESTAMP);
  INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, start_time)
  VALUES (p_tablespace_name, p_batch_id, 'CREATE', 'RUNNING', SYSTIMESTAMP)
  RETURNING log_id INTO v_log_id;
  COMMIT;
  v_sql := 'CREATE BIGFILE TABLESPACE ' || p_tablespace_name || ' DATAFILE SIZE ' || p_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 = p_tablespace_name;
    UPDATE tablespace_log
    SET status = 'COMPLETED', end_time = SYSTIMESTAMP
    WHERE log_id = v_log_id;
    COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
      v_sqlerr_msg := SQLERRM;
      UPDATE tablespace_control
      SET status = 'FAILED', retry_count = v_retry_count + 1, last_attempt = SYSTIMESTAMP
      WHERE tablespace_name = p_tablespace_name;
      UPDATE tablespace_log
      SET status = 'FAILED', error_message = v_sqlerr_msg, end_time = SYSTIMESTAMP
      WHERE log_id = v_log_id;
      COMMIT;
      IF v_retry_count + 1 < v_max_retries THEN
        INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, start_time)
        VALUES (p_tablespace_name, p_batch_id, 'AUTO_RETRY', 'SCHEDULED', SYSTIMESTAMP);
        COMMIT;
      END IF;
      RAISE;
  END;
EXCEPTION
  WHEN OTHERS THEN
    v_sqlerr_msg := SQLERRM;
    INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, error_message, start_time, end_time)
    VALUES (p_tablespace_name, p_batch_id, 'CREATE', 'FAILED', v_sqlerr_msg, SYSTIMESTAMP, SYSTIMESTAMP);
    COMMIT;
    RAISE;
END;
/
BEGIN
  DBMS_OUTPUT.PUT_LINE('Created stored procedure create_single_tablespace');
END;
/

-- Step 4: Define and populate tablespaces with unique batch IDs
DECLARE
  TYPE t_tablespace IS RECORD (
    tablespace_name VARCHAR2(30),
    initial_size_gb NUMBER,
    batch_id NUMBER
  );
  TYPE t_tablespace_list IS TABLE OF t_tablespace;
  v_tablespaces t_tablespace_list := t_tablespace_list(
    t_tablespace('TS_PROD_01', 10, 1),
    t_tablespace('TS_PROD_02', 20, 2),
    t_tablespace('TS_PROD_03', 50, 3),
    t_tablespace('TS_PROD_04', 15, 4),
    t_tablespace('TS_PROD_05', 30, 5),
    t_tablespace('TS_PROD_06', 25, 6),
    t_tablespace('TS_PROD_07', 40, 7),
    t_tablespace('TS_PROD_08', 60, 8),
    t_tablespace('TS_PROD_09', 12, 9),
    t_tablespace('TS_PROD_10', 18, 10),
    t_tablespace('TS_PROD_11', 45, 11),
    t_tablespace('TS_PROD_12', 55, 12)
  );
  v_inserted_count NUMBER := 0;
BEGIN
  FOR i IN 1..v_tablespaces.COUNT LOOP
    MERGE INTO tablespace_control t
    USING (SELECT v_tablespaces(i).tablespace_name AS tablespace_name,
                  v_tablespaces(i).initial_size_gb AS initial_size_gb,
                  v_tablespaces(i).batch_id 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);
    
    IF SQL%ROWCOUNT > 0 THEN
      v_inserted_count := v_inserted_count + 1;
    END IF;
  END LOOP;
  
  COMMIT;
  DBMS_OUTPUT.PUT_LINE('Populated tablespace_control with ' || v_inserted_count || ' new tablespaces (batch IDs 1-12, skipped existing)');
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE('Error populating tablespace_control: ' || SQLERRM);
    RAISE;
END;
/

-- Step 5: Create scheduler jobs for each tablespace using CREATE_SINGLE_TABLESPACE
DECLARE
  v_job_name VARCHAR2(30);
  v_created_count NUMBER := 0;
BEGIN
  DBMS_OUTPUT.ENABLE(1000000);
  
  -- Drop existing scheduler jobs
  DBMS_OUTPUT.PUT_LINE('Dropping existing scheduler jobs:');
  FOR job_rec IN (
    SELECT JOB_NAME
    FROM DBA_SCHEDULER_JOBS
    WHERE JOB_NAME LIKE UPPER(USER) || '.CREATE_TS_%'
  ) LOOP
    DBMS_SCHEDULER.DROP_JOB(job_name => job_rec.JOB_NAME, force => TRUE);
    DBMS_OUTPUT.PUT_LINE('Dropped job: ' || job_rec.JOB_NAME);
    v_created_count := v_created_count + 1;
  END LOOP;
  
  IF v_created_count = 0 THEN
    DBMS_OUTPUT.PUT_LINE('No existing scheduler jobs found.');
  ELSE
    DBMS_OUTPUT.PUT_LINE(v_created_count || ' existing scheduler jobs dropped.');
  END IF;
  
  v_created_count := 0;
  -- Create scheduler jobs for each tablespace
  DBMS_OUTPUT.PUT_LINE('Creating scheduler jobs for tablespaces:');
  FOR rec IN (
    SELECT tablespace_name, initial_size_gb, batch_id
    FROM tablespace_control
    WHERE batch_id IS NOT NULL
    AND batch_id BETWEEN 1 AND 12
    ORDER BY batch_id
  ) LOOP
    v_job_name := UPPER(USER) || '.CREATE_TS_' || rec.tablespace_name;
    DBMS_OUTPUT.PUT_LINE('Creating job for tablespace: ' || rec.tablespace_name);
    
    DBMS_SCHEDULER.CREATE_JOB(
      job_name => v_job_name,
      job_type => 'STORED_PROCEDURE',
      job_action => 'CREATE_SINGLE_TABLESPACE',
      number_of_arguments => 3,
      start_date => SYSTIMESTAMP,
      repeat_interval => NULL,
      enabled => FALSE,
      auto_drop => FALSE,
      comments => 'Create bigfile tablespace ' || rec.tablespace_name
    );
    
    -- Set arguments: p_tablespace_name, p_initial_size_gb, p_batch_id
    DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
      job_name => v_job_name,
      argument_position => 1,
      argument_value => rec.tablespace_name
    );
    DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
      job_name => v_job_name,
      argument_position => 2,
      argument_value => TO_CHAR(rec.initial_size_gb)
    );
    DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
      job_name => v_job_name,
      argument_position => 3,
      argument_value => TO_CHAR(rec.batch_id)
    );
    
    -- Enable the job
    DBMS_SCHEDULER.ENABLE(v_job_name);
    DBMS_OUTPUT.PUT_LINE('Submitted and enabled job ' || v_job_name || ' for tablespace ' || rec.tablespace_name);
    v_created_count := v_created_count + 1;
  END LOOP;
  
  IF v_created_count = 0 THEN
    DBMS_OUTPUT.PUT_LINE('No new scheduler jobs created; all tablespaces may already have jobs.');
  ELSE
    DBMS_OUTPUT.PUT_LINE(v_created_count || ' new scheduler 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 creating scheduler jobs: ' || SQLERRM);
    RAISE;
END;
/
```

### Execution and Verification
1. **Run the Script**:
   - Save as `create_tablespaces_parallel.sql` and run as `rdsadm` in TOAD or SQL*Plus:
     ```sql
     @create_tablespaces_parallel.sql
     ```

2. **Backfill `batch_id`** (if needed for prior runs):
   - Run the `UPDATE` statements to fix existing `tablespace_log` entries:
     ```sql
     BEGIN
       UPDATE tablespace_log l
       SET l.batch_id = (SELECT c.batch_id FROM tablespace_control c WHERE c.tablespace_name = l.tablespace_name)
       WHERE l.tablespace_name = 'TS_PROD_01' AND l.batch_id IS NULL;
       UPDATE tablespace_log l
       SET l.batch_id = (SELECT c.batch_id FROM tablespace_control c WHERE c.tablespace_name = l.tablespace_name)
       WHERE l.tablespace_name = 'TS_PROD_02' AND l.batch_id IS NULL;
       UPDATE tablespace_log l
       SET l.batch_id = (SELECT c.batch_id FROM tablespace_control c WHERE c.tablespace_name = l.tablespace_name)
       WHERE l.tablespace_name = 'TS_PROD_03' AND l.batch_id IS NULL;
       UPDATE tablespace_log l
       SET l.batch_id = (SELECT c.batch_id FROM tablespace_control c WHERE c.tablespace_name = l.tablespace_name)
       WHERE l.tablespace_name = 'TS_PROD_04' AND l.batch_id IS NULL;
       UPDATE tablespace_log l
       SET l.batch_id = (SELECT c.batch_id FROM tablespace_control c WHERE c.tablespace_name = l.tablespace_name)
       WHERE l.tablespace_name = 'TS_PROD_05' AND l.batch_id IS NULL;
       UPDATE tablespace_log l
       SET l.batch_id = (SELECT c.batch_id FROM tablespace_control c WHERE c.tablespace_name = l.tablespace_name)
       WHERE l.tablespace_name = 'TS_PROD_06' AND l.batch_id IS NULL;
       UPDATE tablespace_log l
       SET l.batch_id = (SELECT c.batch_id FROM tablespace_control c WHERE c.tablespace_name = l.tablespace_name)
       WHERE l.tablespace_name = 'TS_PROD_07' AND l.batch_id IS NULL;
       UPDATE tablespace_log l
       SET l.batch_id = (SELECT c.batch_id FROM tablespace_control c WHERE c.tablespace_name = l.tablespace_name)
       WHERE l.tablespace_name = 'TS_PROD_08' AND l.batch_id IS NULL;
       UPDATE tablespace_log l
       SET l.batch_id = (SELECT c.batch_id FROM tablespace_control c WHERE c.tablespace_name = l.tablespace_name)
       WHERE l.tablespace_name = 'TS_PROD_09' AND l.batch_id IS NULL;
       UPDATE tablespace_log l
       SET l.batch_id = (SELECT c.batch_id FROM tablespace_control c WHERE c.tablespace_name = l.tablespace_name)
       WHERE l.tablespace_name = 'TS_PROD_10' AND l.batch_id IS NULL;
       UPDATE tablespace_log l
       SET l.batch_id = (SELECT c.batch_id FROM tablespace_control c WHERE c.tablespace_name = l.tablespace_name)
       WHERE l.tablespace_name = 'TS_PROD_11' AND l.batch_id IS NULL;
       UPDATE tablespace_log l
       SET l.batch_id = (SELECT c.batch_id FROM tablespace_control c WHERE c.tablespace_name = l.tablespace_name)
       WHERE l.tablespace_name = 'TS_PROD_12' AND l.batch_id IS NULL;
       COMMIT;
     END;
     /
     ```

3. **Monitor Execution**:
   - Use the corrected monitoring query (fixes `ORA-00932`):
     ```sql
     SELECT tablespace_name,
            batch_id,
            action,
            status,
            error_message,
            start_time,
            end_time,
            ROUND(
              EXTRACT(DAY FROM (end_time - start_time)) * 1440 +
              EXTRACT(HOUR FROM (end_time - start_time)) * 60 +
              EXTRACT(MINUTE FROM (end_time - start_time)) +
              EXTRACT(SECOND FROM (end_time - start_time)) / 60,
              2
            ) AS duration_minutes,
            CASE
              WHEN end_time IS NULL AND start_time < SYSTIMESTAMP - INTERVAL '30' MINUTE THEN 'Long Running'
              WHEN status = 'FAILED' THEN 'Failed'
              WHEN status = 'COMPLETED' THEN 'Completed'
              ELSE 'In Progress'
            END AS action_status
     FROM tablespace_log
     WHERE tablespace_name IN ('TS_PROD_01', 'TS_PROD_02', 'TS_PROD_03', 'TS_PROD_04', 'TS_PROD_05',
                              'TS_PROD_06', 'TS_PROD_07', 'TS_PROD_08', 'TS_PROD_09', 'TS_PROD_10',
                              'TS_PROD_11', 'TS_PROD_12')
        OR tablespace_name IS NULL
     ORDER BY start_time DESC;
     ```
   - Check `tablespace_monitor`:
     ```sql
     SELECT tablespace_name, batch_id, status, log_status, error_message, start_time, end_time, duration_minutes
     FROM tablespace_monitor
     WHERE action_status IN ('Failed', 'Long Running')
     ORDER BY batch_id;
     ```

4. **Verify Scheduler Jobs**:
   ```sql
   SELECT job_name, state, run_count, failure_count
   FROM DBA_SCHEDULER_JOBS
   WHERE job_name LIKE UPPER(USER) || '.CREATE_TS_%';
   SELECT job_name, status, error#, error_msg
   FROM DBA_SCHEDULER_JOB_RUN_DETAILS
   WHERE job_name LIKE UPPER(USER) || '.CREATE_TS_%';
   ```

### If Issues Persist
If `ORA-01031` or other errors occur:
- **Check Job Errors**:
  ```sql
  SELECT job_name, status, error#, error_msg
  FROM DBA_SCHEDULER_JOB_RUN_DETAILS
  WHERE job_name LIKE UPPER(USER) || '.CREATE_TS_%';
  ```
- **Fallback to Direct Execution**:
  ```plsql
  BEGIN
    create_single_tablespace('TS_PROD_01', 10, 1);
    create_single_tablespace('TS_PROD_02', 20, 2);
    create_single_tablespace('TS_PROD_03', 50, 3);
    create_single_tablespace('TS_PROD_04', 15, 4);
    create_single_tablespace('TS_PROD_05', 30, 5);
    create_single_tablespace('TS_PROD_06', 25, 6);
    create_single_tablespace('TS_PROD_07', 40, 7);
    create_single_tablespace('TS_PROD_08', 60, 8);
    create_single_tablespace('TS_PROD_09', 12, 9);
    create_single_tablespace('TS_PROD_10', 18, 10);
    create_single_tablespace('TS_PROD_11', 45, 11);
    create_single_tablespace('TS_PROD_12', 55, 12);
  END;
  /
  ```
- **Contact AWS Support**: Confirm RDS restrictions on `CREATE TABLESPACE` in scheduler jobs.

Please run the updated script and the `UPDATE` statements for existing logs. If you see new errors, share the output from `tablespace_log` or `DBA_SCHEDULER_JOB_RUN_DETAILS`.

END
==================================================================

BEGIN
  EXECUTE IMMEDIATE q'[
    CREATE OR REPLACE PROCEDURE create_single_tablespace (
      p_tablespace_name IN VARCHAR2,
      p_initial_size_gb IN NUMBER
    ) AUTHID CURRENT_USER AS
      v_sql VARCHAR2(1000);
      v_common_clause CONSTANT VARCHAR2(500) := 'AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING DEFAULT COMPRESS FOR OLTP NO INMEMORY EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON';
      v_max_retries CONSTANT NUMBER := 3;
      v_error_msg VARCHAR2(4000);
      v_log_id NUMBER;
      v_sqlerr_msg VARCHAR2(4000);
      v_retry_count NUMBER;
      v_exists NUMBER;
    BEGIN
      -- Validate tablespace name
      IF NOT REGEXP_LIKE(p_tablespace_name, '^[A-Z][A-Z0-9_]{1,29}$') THEN
        v_error_msg := 'Invalid tablespace name: ' || p_tablespace_name;
        INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, error_message, start_time, end_time)
        VALUES (p_tablespace_name, NULL, 'VALIDATE', 'FAILED', v_error_msg, SYSTIMESTAMP, SYSTIMESTAMP);
        COMMIT;
        RAISE_APPLICATION_ERROR(-20001, v_error_msg);
      END IF;

      -- Check if tablespace exists
      SELECT COUNT(*) INTO v_exists
      FROM DBA_TABLESPACES
      WHERE UPPER(tablespace_name) = UPPER(p_tablespace_name);
      IF v_exists > 0 THEN
        INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, start_time, end_time)
        VALUES (p_tablespace_name, NULL, 'SKIP_EXISTS', 'COMPLETED', SYSTIMESTAMP, SYSTIMESTAMP);
        COMMIT;
        RETURN;
      END IF;

      -- Get retry count from tablespace_control, if exists
      BEGIN
        SELECT retry_count INTO v_retry_count
        FROM tablespace_control
        WHERE tablespace_name = p_tablespace_name;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          v_retry_count := 0;
      END;

      -- Update or insert into tablespace_control
      MERGE INTO tablespace_control t
      USING (SELECT p_tablespace_name AS tablespace_name, p_initial_size_gb AS initial_size_gb, NULL AS batch_id FROM DUAL) s
      ON (t.tablespace_name = s.tablespace_name)
      WHEN MATCHED THEN
        UPDATE SET status = 'RUNNING', last_attempt = SYSTIMESTAMP
      WHEN NOT MATCHED THEN
        INSERT (tablespace_name, initial_size_gb, batch_id, status, last_attempt)
        VALUES (s.tablespace_name, s.initial_size_gb, s.batch_id, 'RUNNING', SYSTIMESTAMP);

      INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, start_time)
      VALUES (p_tablespace_name, NULL, 'CREATE', 'RUNNING', SYSTIMESTAMP)
      RETURNING log_id INTO v_log_id;
      COMMIT;

      -- Create tablespace
      v_sql := 'CREATE BIGFILE TABLESPACE ' || p_tablespace_name || ' DATAFILE SIZE ' || p_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 = p_tablespace_name;
        UPDATE tablespace_log
        SET status = 'COMPLETED', end_time = SYSTIMESTAMP
        WHERE log_id = v_log_id;
        COMMIT;
      EXCEPTION
        WHEN OTHERS THEN
          v_sqlerr_msg := SQLERRM;
          UPDATE tablespace_control
          SET status = 'FAILED', retry_count = v_retry_count + 1, last_attempt = SYSTIMESTAMP
          WHERE tablespace_name = p_tablespace_name;
          UPDATE tablespace_log
          SET status = 'FAILED', error_message = v_sqlerr_msg, end_time = SYSTIMESTAMP
          WHERE log_id = v_log_id;
          COMMIT;
          IF v_retry_count + 1 < v_max_retries THEN
            INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, start_time)
            VALUES (p_tablespace_name, NULL, 'AUTO_RETRY', 'SCHEDULED', SYSTIMESTAMP);
            COMMIT;
          END IF;
          RAISE;
      END;
    EXCEPTION
      WHEN OTHERS THEN
        v_sqlerr_msg := SQLERRM;
        INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, error_message, start_time, end_time)
        VALUES (p_tablespace_name, NULL, 'CREATE', 'FAILED', v_sqlerr_msg, SYSTIMESTAMP, SYSTIMESTAMP);
        COMMIT;
        RAISE;
    END;
  ]';
  DBMS_OUTPUT.PUT_LINE('Created stored procedure create_single_tablespace');
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error creating stored procedure create_single_tablespace: ' || SQLERRM);
    RAISE;
END;
/

EXEC create_single_tablespace('TS_PROD_01', 10);

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

SET SERVEROUTPUT ON;
-- Step 2: Populate tablespace_control with new batch assignments (dynamic and validated)
DECLARE
  -- Define a record type for tablespace details
  TYPE t_tablespace IS RECORD (
    tablespace_name VARCHAR2(30),
    initial_size_gb NUMBER
  );
  -- Define a collection type for multiple tablespaces
  TYPE t_tablespace_list IS TABLE OF t_tablespace;
  -- Initialize the collection with tablespaces to insert
  v_tablespaces t_tablespace_list := t_tablespace_list(
    t_tablespace('TS_REVANTH_6', 6268),
    t_tablespace('TS_REVANTH_7', 6268),
    t_tablespace('TS_REVANTH_8', 6268)
    -- Add more tablespaces here as needed, e.g., t_tablespace('TS_REVANTH_9', 2500)
  );
  v_next_batch_id NUMBER;
  v_error_count NUMBER := 0;
BEGIN
  DBMS_OUTPUT.ENABLE(1000000);
  
  -- Get the next available batch_id
  SELECT NVL(MAX(batch_id), 0) + 1 INTO v_next_batch_id FROM tablespace_control;
  
  -- Loop through the collection to perform MERGE operations
  FOR i IN 1..v_tablespaces.COUNT LOOP
    -- Validate tablespace_name
    IF NOT REGEXP_LIKE(v_tablespaces(i).tablespace_name, '^[A-Z][A-Z0-9_]{1,29}$') THEN
      DBMS_OUTPUT.PUT_LINE('Error: Invalid tablespace name: ' || v_tablespaces(i).tablespace_name);
      INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, error_message)
      VALUES (v_tablespaces(i).tablespace_name, v_next_batch_id, 'VALIDATE', 'FAILED', 
              'Invalid tablespace name format (must start with letter, followed by letters/numbers/underscores, max 30 chars)');
      COMMIT;
      v_error_count := v_error_count + 1;
      CONTINUE;
    END IF;
    
    -- Validate initial_size_gb
    IF v_tablespaces(i).initial_size_gb <= 0 THEN
      DBMS_OUTPUT.PUT_LINE('Error: Invalid size for ' || v_tablespaces(i).tablespace_name || ': ' || v_tablespaces(i).initial_size_gb || ' GB');
      INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, error_message)
      VALUES (v_tablespaces(i).tablespace_name, v_next_batch_id, 'VALIDATE', 'FAILED', 
              'Initial size must be positive: ' || v_tablespaces(i).initial_size_gb);
      COMMIT;
      v_error_count := v_error_count + 1;
      CONTINUE;
    END IF;
    
    -- Perform MERGE to insert if not exists
    BEGIN
      MERGE INTO tablespace_control t
      USING (SELECT v_tablespaces(i).tablespace_name AS tablespace_name, 
                    v_tablespaces(i).initial_size_gb AS initial_size_gb, 
                    v_next_batch_id 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, status, created_by)
      VALUES (s.tablespace_name, s.initial_size_gb, s.batch_id, 'PENDING', USER);
      
      DBMS_OUTPUT.PUT_LINE('Inserted/Updated ' || v_tablespaces(i).tablespace_name || ' with batch_id ' || v_next_batch_id);
      v_next_batch_id := v_next_batch_id + 1; -- Increment batch_id for next tablespace
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error inserting ' || v_tablespaces(i).tablespace_name || ': ' || SQLERRM);
        INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, error_message)
        VALUES (v_tablespaces(i).tablespace_name, v_next_batch_id, 'INSERT', 'FAILED', SQLERRM);
        v_error_count := v_error_count + 1;
        CONTINUE;
    END;
    COMMIT;
  END LOOP;
  
  IF v_error_count = 0 THEN
    DBMS_OUTPUT.PUT_LINE('Successfully populated tablespace_control with ' || v_tablespaces.COUNT || ' new batch assignments');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Populated tablespace_control with ' || (v_tablespaces.COUNT - v_error_count) || ' new batch assignments, ' || v_error_count || ' errors encountered');
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Unexpected error in Step 2: ' || SQLERRM);
    RAISE;
END;
/
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)