Monday, September 1, 2025

Enhanced-v1

Example Usage

1 . Initial Run (Batch 1-10):

DECLARE
  p_restart VARCHAR2(3) := 'YES';
  v_min_batch_id NUMBER := 1;
  v_max_batch_id NUMBER := 10;
  v_tablespaces t_tablespace_list := t_tablespace_list(
    t_tablespace('TS_REVANTH_1', 6268),
    t_tablespace('TS_REVANTH_2', 6268),
    t_tablespace('TS_REVANTH_3', 6268),
    t_tablespace('TS_REVANTH_4', 6268),
    t_tablespace('TS_REVANTH_5', 6268),
    t_tablespace('TS_REVANTH_6', 6268),
    t_tablespace('TS_REVANTH_7', 6268),
    t_tablespace('TS_REVANTH_8', 6268),
    t_tablespace('TS_REVANTH_9', 6268),
    t_tablespace('TS_REVANTH_10', 6268)
  );
  ...
-- Rest of the script as above

  • Creates 10 jobs, one per batch ID (1-10).
2. Incremental Run (Batch 11-20):

DECLARE 
 p_restart VARCHAR2(3) := 'NO'; 
 v_min_batch_id NUMBER := 11; 
 v_max_batch_id NUMBER := 20; 
 v_tablespaces t_tablespace_list := t_tablespace_list( 
 t_tablespace('TS_REVANTH_11', 5000), 
 t_tablespace('TS_REVANTH_12', 5000), 
 t_tablespace('TS_REVANTH_13', 5000), 
 t_tablespace('TS_REVANTH_14', 5000), 
 t_tablespace('TS_REVANTH_15', 5000), 
 t_tablespace('TS_REVANTH_16', 5000), 
 t_tablespace('TS_REVANTH_17', 5000), 
 t_tablespace('TS_REVANTH_18', 5000), 
 t_tablespace('TS_REVANTH_19', 5000), 
 t_tablespace('TS_REVANTH_20', 5000) 
 ); ... -- Rest of the script as above

Restart After Mistake (e.g., after batch 9):
  • Set p_restart := 'YES' and rerun with desired batch range and tablespaces.
  • Drops all tables and jobs, then creates new ones.
SET SERVEROUTPUT ON;

-- Main script for tablespace creation with user-defined sizes and batch IDs
DECLARE
  -- User-configurable parameters
  p_restart VARCHAR2(3) := 'NO'; -- 'YES' to restart fresh, 'NO' to continue incrementally
  v_min_batch_id NUMBER := 6; -- Minimum batch ID for this run
  v_max_batch_id NUMBER := 8; -- Maximum batch ID for this run

  -- User-provided tablespace names and sizes (environment-specific)
  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('TS_REVANTH_6', 6268), -- Environment-specific size
    t_tablespace('TS_REVANTH_7', 6268),
    t_tablespace('TS_REVANTH_8', 6268)
    -- Add more as needed, e.g., t_tablespace('TS_REVANTH_9', 5000)
  );

  -- Internal variables
  v_next_batch_id NUMBER;
  v_error_count NUMBER := 0;
  v_created_count NUMBER := 0;
  v_error_msg VARCHAR2(4000);
  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';

  -- Function to check if table exists
  FUNCTION table_exists(p_table_name IN VARCHAR2) RETURN BOOLEAN IS
    v_count NUMBER;
  BEGIN
    SELECT COUNT(*) INTO v_count FROM user_tables WHERE table_name = UPPER(p_table_name);
    RETURN v_count > 0;
  END;

  -- Procedure to drop all scheduler jobs
  PROCEDURE drop_all_jobs IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Dropping all scheduler jobs matching pattern ' || UPPER(USER) || '.CREATE_TS_BATCH_%');
    FOR rec IN (
      SELECT job_name
      FROM DBA_SCHEDULER_JOBS
      WHERE job_name LIKE UPPER(USER) || '.CREATE_TS_BATCH_%'
    ) LOOP
      BEGIN
        DBMS_SCHEDULER.STOP_JOB(job_name => rec.job_name, force => TRUE);
        DBMS_SCHEDULER.DROP_JOB(job_name => rec.job_name, force => TRUE);
        DBMS_OUTPUT.PUT_LINE('Dropped job ' || rec.job_name);
        log_action(NULL, TO_NUMBER(REGEXP_SUBSTR(rec.job_name, '\d+$')), 'DROP_JOB', 'COMPLETED', NULL, NULL, NULL);
      EXCEPTION
        WHEN OTHERS THEN
          v_error_msg := 'Failed to drop job ' || rec.job_name || ': ' || SQLERRM;
          DBMS_OUTPUT.PUT_LINE(v_error_msg);
          log_action(NULL, TO_NUMBER(REGEXP_SUBSTR(rec.job_name, '\d+$')), 'DROP_JOB', 'FAILED', v_error_msg, NULL, NULL);
      END;
    END LOOP;
  END;

  -- Procedure to create control and log tables
  PROCEDURE create_tables IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Creating tablespace_control table');
    EXECUTE IMMEDIATE q'[
      CREATE TABLE tablespace_control (
        tablespace_name VARCHAR2(30) PRIMARY KEY,
        initial_size_gb NUMBER NOT NULL,
        batch_id NUMBER UNIQUE,
        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,
        created_at TIMESTAMP DEFAULT SYSTIMESTAMP,
        CONSTRAINT chk_size_positive CHECK (initial_size_gb > 0)
      )
    ]';

    DBMS_OUTPUT.PUT_LINE('Creating tablespace_log table');
    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'),
        job_start_time TIMESTAMP,
        job_end_time TIMESTAMP
      )
    ]';

    -- Add indexes for performance
    BEGIN
      EXECUTE IMMEDIATE 'CREATE INDEX idx_ts_control_batch_id ON tablespace_control(batch_id)';
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE = -955 THEN
          DBMS_OUTPUT.PUT_LINE('Index idx_ts_control_batch_id already exists, skipping');
        ELSE
          RAISE;
        END IF;
    END;
    BEGIN
      EXECUTE IMMEDIATE 'CREATE INDEX idx_ts_log_batch_id ON tablespace_log(batch_id)';
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE = -955 THEN
          DBMS_OUTPUT.PUT_LINE('Index idx_ts_log_batch_id already exists, skipping');
        ELSE
          RAISE;
        END IF;
    END;
    BEGIN
      EXECUTE IMMEDIATE 'CREATE INDEX idx_ts_log_action ON tablespace_log(action)';
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE = -955 THEN
          DBMS_OUTPUT.PUT_LINE('Index idx_ts_log_action already exists, skipping');
        ELSE
          RAISE;
        END IF;
    END;
    BEGIN
      EXECUTE IMMEDIATE 'CREATE INDEX idx_ts_log_times ON tablespace_log(job_start_time, job_end_time)';
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE = -955 THEN
          DBMS_OUTPUT.PUT_LINE('Index idx_ts_log_times already exists, skipping');
        ELSE
          RAISE;
        END IF;
    END;
  EXCEPTION
    WHEN OTHERS THEN
      v_error_msg := 'Error creating tables: ' || SQLERRM;
      DBMS_OUTPUT.PUT_LINE(v_error_msg);
      log_action(NULL, 0, 'CREATE_TABLES', 'FAILED', v_error_msg, NULL, NULL);
      RAISE;
  END;

  -- Procedure to log actions
  PROCEDURE log_action(p_tablespace_name IN VARCHAR2, p_batch_id IN NUMBER, p_action IN VARCHAR2, p_status IN VARCHAR2, p_message IN VARCHAR2 DEFAULT NULL, p_start_time IN TIMESTAMP DEFAULT NULL, p_end_time IN TIMESTAMP DEFAULT NULL) IS
  BEGIN
    IF table_exists('TABLESPACE_LOG') THEN
      INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, error_message, job_start_time, job_end_time)
      VALUES (NVL(p_tablespace_name, 'N/A'), p_batch_id, p_action, p_status, SUBSTR(p_message, 1, 4000), p_start_time, p_end_time);
      COMMIT;
    ELSE
      DBMS_OUTPUT.PUT_LINE('Log table does not exist; action logging skipped: ' || p_action);
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error logging action: ' || SQLERRM);
  END;

BEGIN
  DBMS_OUTPUT.ENABLE(1000000);
  DBMS_OUTPUT.PUT_LINE('Starting tablespace creation script with batch range ' || v_min_batch_id || ' to ' || v_max_batch_id || ', restart: ' || p_restart);

  -- Step 1: Handle restart or incremental mode
  IF UPPER(p_restart) = 'YES' THEN
    DBMS_OUTPUT.PUT_LINE('Restart requested: Dropping tables and jobs');
    BEGIN
      IF table_exists('TABLESPACE_CONTROL') THEN
        EXECUTE IMMEDIATE 'DROP TABLE tablespace_control';
        DBMS_OUTPUT.PUT_LINE('Dropped tablespace_control');
      END IF;
      IF table_exists('TABLESPACE_LOG') THEN
        EXECUTE IMMEDIATE 'DROP TABLE tablespace_log';
        DBMS_OUTPUT.PUT_LINE('Dropped tablespace_log');
      END IF;
      drop_all_jobs;
      create_tables;
      v_next_batch_id := v_min_batch_id;
      log_action(NULL, 0, 'RESTART', 'COMPLETED', 'Restart completed: Tables and jobs dropped', SYSTIMESTAMP, SYSTIMESTAMP);
    EXCEPTION
      WHEN OTHERS THEN
        v_error_msg := 'Error during restart: ' || SQLERRM;
        DBMS_OUTPUT.PUT_LINE(v_error_msg);
        log_action(NULL, 0, 'RESTART', 'FAILED', v_error_msg, SYSTIMESTAMP, SYSTIMESTAMP);
        RAISE;
    END;
  ELSE
    -- Incremental mode: Create tables if not exist
    IF NOT table_exists('TABLESPACE_CONTROL') OR NOT table_exists('TABLESPACE_LOG') THEN
      create_tables;
    END IF;
    -- Get next batch ID, ensuring it aligns with input range
    SELECT NVL(MAX(batch_id), v_min_batch_id - 1) + 1 INTO v_next_batch_id FROM tablespace_control;
    IF v_next_batch_id < v_min_batch_id THEN
      v_next_batch_id := v_min_batch_id;
    END IF;
  END IF;

  -- Step 2: Validate inputs
  IF v_tablespaces.COUNT <> (v_max_batch_id - v_min_batch_id + 1) THEN
    v_error_msg := 'Number of tablespaces (' || v_tablespaces.COUNT || ') does not match batch range (' || (v_max_batch_id - v_min_batch_id + 1) || ')';
    DBMS_OUTPUT.PUT_LINE('Error: ' || v_error_msg);
    log_action(NULL, 0, 'VALIDATE_INPUT', 'FAILED', v_error_msg, SYSTIMESTAMP, SYSTIMESTAMP);
    RAISE_APPLICATION_ERROR(-20004, v_error_msg);
  END IF;

  IF v_next_batch_id > v_max_batch_id THEN
    v_error_msg := 'Next batch_id ' || v_next_batch_id || ' exceeds max_batch_id ' || v_max_batch_id;
    DBMS_OUTPUT.PUT_LINE('Error: ' || v_error_msg);
    log_action(NULL, 0, 'VALIDATE_BATCH', 'FAILED', v_error_msg, SYSTIMESTAMP, SYSTIMESTAMP);
    RAISE_APPLICATION_ERROR(-20005, v_error_msg);
  END IF;

  -- Step 3: Populate tablespace_control
  DBMS_OUTPUT.PUT_LINE('Populating tablespace_control starting from batch_id ' || v_next_batch_id);
  FOR i IN 1..v_tablespaces.COUNT LOOP
    IF v_next_batch_id > v_max_batch_id THEN
      v_error_msg := 'Batch_id ' || v_next_batch_id || ' exceeds max_batch_id ' || v_max_batch_id;
      DBMS_OUTPUT.PUT_LINE('Error: ' || v_error_msg);
      log_action(v_tablespaces(i).tablespace_name, v_next_batch_id, 'VALIDATE_BATCH', 'FAILED', v_error_msg, SYSTIMESTAMP, SYSTIMESTAMP);
      v_error_count := v_error_count + 1;
      CONTINUE;
    END IF;

    -- Validate tablespace name
    IF NOT REGEXP_LIKE(v_tablespaces(i).tablespace_name, '^[A-Z][A-Z0-9_]{1,29}$') THEN
      v_error_msg := 'Invalid tablespace name: ' || v_tablespaces(i).tablespace_name;
      DBMS_OUTPUT.PUT_LINE('Error: ' || v_error_msg);
      log_action(v_tablespaces(i).tablespace_name, v_next_batch_id, 'VALIDATE_NAME', 'FAILED', v_error_msg, SYSTIMESTAMP, SYSTIMESTAMP);
      v_error_count := v_error_count + 1;
      CONTINUE;
    END IF;

    -- Validate initial size
    IF v_tablespaces(i).initial_size_gb <= 0 THEN
      v_error_msg := 'Invalid initial size for ' || v_tablespaces(i).tablespace_name || ': ' || v_tablespaces(i).initial_size_gb;
      DBMS_OUTPUT.PUT_LINE('Error: ' || v_error_msg);
      log_action(v_tablespaces(i).tablespace_name, v_next_batch_id, 'VALIDATE_SIZE', 'FAILED', v_error_msg, SYSTIMESTAMP, SYSTIMESTAMP);
      v_error_count := v_error_count + 1;
      CONTINUE;
    END IF;

    -- Insert into tablespace_control
    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 OR t.batch_id = s.batch_id)
      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 ' || v_tablespaces(i).tablespace_name || ' with batch_id ' || v_next_batch_id);
      log_action(v_tablespaces(i).tablespace_name, v_next_batch_id, 'POPULATE', 'COMPLETED', NULL, SYSTIMESTAMP, SYSTIMESTAMP);
      v_next_batch_id := v_next_batch_id + 1;
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
        v_error_msg := 'Duplicate tablespace name or batch_id: ' || v_tablespaces(i).tablespace_name || ', batch ' || v_next_batch_id;
        DBMS_OUTPUT.PUT_LINE('Error: ' || v_error_msg);
        log_action(v_tablespaces(i).tablespace_name, v_next_batch_id, 'POPULATE', 'FAILED', v_error_msg, SYSTIMESTAMP, SYSTIMESTAMP);
        v_error_count := v_error_count + 1;
        CONTINUE;
      WHEN OTHERS THEN
        v_error_msg := 'Error populating ' || v_tablespaces(i).tablespace_name || ': ' || SQLERRM;
        DBMS_OUTPUT.PUT_LINE('Error: ' || v_error_msg);
        log_action(v_tablespaces(i).tablespace_name, v_next_batch_id, 'POPULATE', 'FAILED', v_error_msg, SYSTIMESTAMP, SYSTIMESTAMP);
        v_error_count := v_error_count + 1;
        CONTINUE;
    END;
    COMMIT;
  END LOOP;

  IF v_error_count > 0 THEN
    v_error_msg := 'Population completed with ' || v_error_count || ' errors';
    DBMS_OUTPUT.PUT_LINE('Error: ' || v_error_msg);
    log_action(NULL, 0, 'POPULATE_SUMMARY', 'FAILED', v_error_msg, SYSTIMESTAMP, SYSTIMESTAMP);
    RAISE_APPLICATION_ERROR(-20006, v_error_msg);
  ELSE
    DBMS_OUTPUT.PUT_LINE('Successfully populated ' || v_tablespaces.COUNT || ' entries');
    log_action(NULL, 0, 'POPULATE_SUMMARY', 'COMPLETED', 'Populated ' || v_tablespaces.COUNT || ' entries', SYSTIMESTAMP, SYSTIMESTAMP);
  END IF;

  -- Step 4: Create individual scheduler jobs
  DBMS_OUTPUT.PUT_LINE('Creating jobs for batch IDs ' || v_min_batch_id || ' to ' || v_max_batch_id);
  FOR rec IN (
    SELECT batch_id, tablespace_name, initial_size_gb
    FROM tablespace_control
    WHERE batch_id BETWEEN v_min_batch_id AND v_max_batch_id
    AND status = 'PENDING'
    AND NOT EXISTS (
      SELECT 1 FROM DBA_SCHEDULER_JOBS WHERE job_name = UPPER(USER) || '.CREATE_TS_BATCH_' || batch_id
    )
    ORDER BY batch_id
  ) LOOP
    DECLARE
      v_job_name VARCHAR2(30) := UPPER(USER) || '.CREATE_TS_BATCH_' || rec.batch_id;
      v_sql VARCHAR2(1000);
      v_job_start_time TIMESTAMP := SYSTIMESTAMP;
    BEGIN
      v_sql := 'CREATE BIGFILE TABLESPACE ' || rec.tablespace_name || ' DATAFILE SIZE ' || rec.initial_size_gb || 'G ' || v_common_clause;
      DBMS_SCHEDULER.CREATE_JOB(
        job_name => v_job_name,
        job_type => 'PLSQL_BLOCK',
        job_action => q'[
          DECLARE
            v_sql VARCHAR2(1000) := ']' || v_sql || q'[';
            v_max_retries CONSTANT NUMBER := 3;
            v_error_msg VARCHAR2(4000);
            v_job_start_time TIMESTAMP := SYSTIMESTAMP;
            PROCEDURE log_action(p_tablespace_name IN VARCHAR2, p_batch_id IN NUMBER, p_action IN VARCHAR2, p_status IN VARCHAR2, p_message IN VARCHAR2 DEFAULT NULL) IS
            BEGIN
              INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, error_message, job_start_time, job_end_time)
              VALUES (NVL(p_tablespace_name, 'N/A'), p_batch_id, p_action, p_status, SUBSTR(p_message, 1, 4000), v_job_start_time, SYSTIMESTAMP);
              COMMIT;
            END;
          BEGIN
            log_action(']' || rec.tablespace_name || q'[', ]' || rec.batch_id || q'[, 'JOB_START', 'RUNNING');
            -- Validate tablespace name
            IF NOT REGEXP_LIKE(']' || rec.tablespace_name || q'[', '^[A-Z][A-Z0-9_]{1,29}$') THEN
              v_error_msg := 'Invalid tablespace name: ]' || rec.tablespace_name || q'[';
              UPDATE tablespace_control SET status = 'FAILED', retry_count = retry_count + 1, last_attempt = SYSTIMESTAMP
              WHERE tablespace_name = ']' || rec.tablespace_name || q'[';
              log_action(']' || rec.tablespace_name || q'[', ]' || rec.batch_id || q'[, 'VALIDATE', 'FAILED', v_error_msg);
              RAISE_APPLICATION_ERROR(-20007, v_error_msg);
            END IF;
            -- Check if tablespace exists
            DECLARE
              v_exists NUMBER;
            BEGIN
              SELECT COUNT(*) INTO v_exists FROM DBA_TABLESPACES WHERE tablespace_name = ']' || rec.tablespace_name || q'[';
              IF v_exists > 0 THEN
                UPDATE tablespace_control SET status = 'COMPLETED', last_attempt = SYSTIMESTAMP
                WHERE tablespace_name = ']' || rec.tablespace_name || q'[';
                log_action(']' || rec.tablespace_name || q'[', ]' || rec.batch_id || q'[, 'SKIP_EXISTS', 'COMPLETED');
                COMMIT;
              ELSE
                -- Create tablespace with retries
                UPDATE tablespace_control SET status = 'RUNNING', last_attempt = SYSTIMESTAMP
                WHERE tablespace_name = ']' || rec.tablespace_name || q'[';
                COMMIT;
                FOR retry IN 1..v_max_retries LOOP
                  BEGIN
                    EXECUTE IMMEDIATE v_sql;
                    UPDATE tablespace_control SET status = 'COMPLETED', retry_count = 0, last_attempt = SYSTIMESTAMP
                    WHERE tablespace_name = ']' || rec.tablespace_name || q'[';
                    log_action(']' || rec.tablespace_name || q'[', ]' || rec.batch_id || q'[, 'CREATE', 'COMPLETED');
                    COMMIT;
                    EXIT;
                  EXCEPTION
                    WHEN OTHERS THEN
                      v_error_msg := SQLERRM;
                      IF retry < v_max_retries THEN
                        log_action(']' || rec.tablespace_name || q'[', ]' || rec.batch_id || q'[, 'RETRY', 'ATTEMPTED', 'Retry ' || retry || ': ' || v_error_msg);
                        DBMS_LOCK.SLEEP(60);
                      ELSE
                        UPDATE tablespace_control SET status = 'FAILED', retry_count = v_max_retries, last_attempt = SYSTIMESTAMP
                        WHERE tablespace_name = ']' || rec.tablespace_name || q'[';
                        log_action(']' || rec.tablespace_name || q'[', ]' || rec.batch_id || q'[, 'CREATE', 'FAILED', v_error_msg);
                        COMMIT;
                        RAISE_APPLICATION_ERROR(-20008, v_error_msg);
                      END IF;
                  END;
                END LOOP;
              END IF;
            END;
            log_action(']' || rec.tablespace_name || q'[', ]' || rec.batch_id || q'[, 'JOB_END', 'COMPLETED', NULL);
          EXCEPTION
            WHEN OTHERS THEN
              v_error_msg := 'Job failed: ' || SQLERRM;
              UPDATE tablespace_control SET status = 'FAILED', retry_count = retry_count + 1, last_attempt = SYSTIMESTAMP
              WHERE tablespace_name = ']' || rec.tablespace_name || q'[';
              log_action(']' || rec.tablespace_name || q'[', ]' || rec.batch_id || q'[, 'JOB_ERROR', 'FAILED', v_error_msg);
              RAISE;
          END;
        ]',
        start_date => SYSTIMESTAMP,
        repeat_interval => NULL,
        enabled => TRUE,
        auto_drop => FALSE,
        comments => 'Job to create tablespace ' || rec.tablespace_name || ' for batch ' || rec.batch_id
      );
      DBMS_OUTPUT.PUT_LINE('Created job ' || v_job_name || ' for batch ' || rec.batch_id);
      log_action(rec.tablespace_name, rec.batch_id, 'JOB_CREATE', 'COMPLETED', NULL, v_job_start_time, SYSTIMESTAMP);
      v_created_count := v_created_count + 1;
    EXCEPTION
      WHEN OTHERS THEN
        v_error_msg := 'Failed to create job for batch ' || rec.batch_id || ': ' || SQLERRM;
        DBMS_OUTPUT.PUT_LINE('Error: ' || v_error_msg);
        log_action(rec.tablespace_name, rec.batch_id, 'JOB_CREATE', 'FAILED', v_error_msg, v_job_start_time, SYSTIMESTAMP);
        v_error_count := v_error_count + 1;
    END;
  END LOOP;

  -- Validate job creation
  IF v_created_count <> (v_max_batch_id - v_min_batch_id + 1) THEN
    v_error_msg := 'Job creation mismatch: Expected ' || (v_max_batch_id - v_min_batch_id + 1) || ', created ' || v_created_count;
    DBMS_OUTPUT.PUT_LINE('Error: ' || v_error_msg);
    log_action(NULL, 0, 'JOB_SUMMARY', 'FAILED', v_error_msg, SYSTIMESTAMP, SYSTIMESTAMP);
    RAISE_APPLICATION_ERROR(-20009, v_error_msg);
  ELSE
    DBMS_OUTPUT.PUT_LINE('Successfully created ' || v_created_count || ' jobs');
    log_action(NULL, 0, 'JOB_SUMMARY', 'COMPLETED', 'Created ' || v_created_count || ' jobs', SYSTIMESTAMP, SYSTIMESTAMP);
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    v_error_msg := 'Unexpected script error: ' || SQLERRM;
    DBMS_OUTPUT.PUT_LINE(v_error_msg);
    log_action(NULL, 0, 'SCRIPT_ERROR', 'FAILED', v_error_msg, SYSTIMESTAMP, SYSTIMESTAMP);
    ROLLBACK;
    RAISE;
END;
/

Monitoring and Verification

  • Check tablespace_control:

    SELECT tablespace_name, batch_id, initial_size_gb, status, last_attempt, created_at
    FROM tablespace_control
    WHERE batch_id BETWEEN 6 AND 8
    ORDER BY batch_id;

    • Check tablespace_log with Job Duration:

    SELECT log_id, tablespace_name, batch_id, action, status, error_message, job_start_time, job_end_time, (job_end_time - job_start_time) DAY TO SECOND AS job_duration FROM tablespace_log WHERE batch_id BETWEEN 6 AND 8 AND action IN ('JOB_START', 'JOB_END', 'JOB_ERROR') ORDER BY log_time;

    • Verify Tablespaces:

    SELECT tablespace_name, bytes / 1024 / 1024 / 1024 AS size_gb, maxbytes / 1024 / 1024 / 1024 AS max_size_gb FROM dba_data_files WHERE tablespace_name LIKE 'TS_REVANTH%';

    • Check Jobs:

    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_%';

    Posted by at

    No comments:

    Post a Comment

    Subscribe to: Post Comments (Atom)