RDS-Tablespace Creation Script - 1

 

Step 1: Create Control and Log Tables

Run the table creation portion of the original script to set up tablespace_control and tablespace_log. This is idempotent (skips if tables exist) and only needs to be run once unless the tables are dropped.

• Run in TOAD: Execute the INSERT block in SQL Editor.
• Validate: Check the data:
  
  
  
  
  SELECT tablespace_name, initial_size_gb, status FROM tablespace_control ORDER BY tablespace_name;
  
  

  Ensure 64 rows with status = 'PENDING'.

  
  

  Step 3: Run the Scheduler Job

  Run the anonymous block to submit the DBMS_SCHEDULER job, which reads from tablespace_control and creates tablespaces in the background using your template:

  
  

  SET SERVEROUTPUT ON;

  -- =============================================

  -- Create Date: 2025-08-31

  -- Description: An idempotent script to check for the existence of tables,

  -- drop them if they exist, and then create them.

  -- =============================================

  SET SERVEROUTPUT ON; DECLARE -- Variables to check table existence and privileges v_tablespace_control_exists NUMBER; v_tablespace_log_exists NUMBER; v_error_msg VARCHAR2(4000); v_tables_created NUMBER := 0; BEGIN -- Purpose: Set up tablespace_control and tablespace_log for tablespace creation workflow -- Drops existing tables and creates new ones with idempotent logic DBMS_OUTPUT.ENABLE(1000000); DBMS_OUTPUT.PUT_LINE('[INFO] Starting table setup for tablespace creation workflow'); -- Step 1: Verify CREATE TABLE privilege DBMS_OUTPUT.PUT_LINE('[INFO] Checking CREATE TABLE privilege'); BEGIN -- Attempt a test CREATE TABLE to verify privilege EXECUTE IMMEDIATE 'CREATE TABLE temp_priv_check (id NUMBER)'; EXECUTE IMMEDIATE 'DROP TABLE temp_priv_check'; DBMS_OUTPUT.PUT_LINE('[SUCCESS] CREATE TABLE privilege verified'); EXCEPTION WHEN OTHERS THEN v_error_msg := 'User ' || USER || ' lacks CREATE TABLE privilege or other error: ' || SQLERRM; DBMS_OUTPUT.PUT_LINE('[ERROR] ' || v_error_msg); RAISE_APPLICATION_ERROR(-20001, v_error_msg); END; -- Step 2: Check and drop tablespace_control DBMS_OUTPUT.PUT_LINE('[INFO] Checking existence of TABLESPACE_CONTROL'); SELECT COUNT(*) INTO v_tablespace_control_exists FROM user_tables WHERE table_name = 'TABLESPACE_CONTROL'; IF v_tablespace_control_exists > 0 THEN BEGIN EXECUTE IMMEDIATE 'DROP TABLE tablespace_control'; DBMS_OUTPUT.PUT_LINE('[SUCCESS] Dropped existing table TABLESPACE_CONTROL'); EXCEPTION WHEN OTHERS THEN v_error_msg := 'Failed to drop TABLESPACE_CONTROL: ' || SQLERRM; DBMS_OUTPUT.PUT_LINE('[ERROR] ' || v_error_msg); RAISE; END; ELSE DBMS_OUTPUT.PUT_LINE('[INFO] TABLESPACE_CONTROL does not exist, no drop needed'); END IF; -- Step 3: Check and drop tablespace_log DBMS_OUTPUT.PUT_LINE('[INFO] Checking existence of TABLESPACE_LOG'); SELECT COUNT(*) INTO v_tablespace_log_exists FROM user_tables WHERE table_name = 'TABLESPACE_LOG'; IF v_tablespace_log_exists > 0 THEN BEGIN EXECUTE IMMEDIATE 'DROP TABLE tablespace_log'; DBMS_OUTPUT.PUT_LINE('[SUCCESS] Dropped existing table TABLESPACE_LOG'); EXCEPTION WHEN OTHERS THEN v_error_msg := 'Failed to drop TABLESPACE_LOG: ' || SQLERRM; DBMS_OUTPUT.PUT_LINE('[ERROR] ' || v_error_msg); RAISE; END; ELSE DBMS_OUTPUT.PUT_LINE('[INFO] TABLESPACE_LOG does not exist, no drop needed'); END IF; -- Step 4: Create tablespace_control DBMS_OUTPUT.PUT_LINE('[INFO] Creating TABLESPACE_CONTROL'); 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('[SUCCESS] Created table TABLESPACE_CONTROL'); EXCEPTION WHEN OTHERS THEN v_error_msg := 'Failed to create TABLESPACE_CONTROL: ' || SQLERRM; DBMS_OUTPUT.PUT_LINE('[ERROR] ' || v_error_msg); -- Log to DBMS_OUTPUT only, as tablespace_log doesn't exist yet RAISE; END; -- Step 5: Create tablespace_log DBMS_OUTPUT.PUT_LINE('[INFO] Creating TABLESPACE_LOG'); BEGIN EXECUTE IMMEDIATE q'[ CREATE TABLE tablespace_log ( log_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, tablespace_name VARCHAR2(30), batch_id NUMBER, action VARCHAR2(20), status VARCHAR2(10), error_message VARCHAR2(4000), log_time TIMESTAMP DEFAULT SYSTIMESTAMP, session_user VARCHAR2(30) DEFAULT USER, session_id NUMBER DEFAULT SYS_CONTEXT('USERENV', 'SID') ) ]'; DBMS_OUTPUT.PUT_LINE('[SUCCESS] Created table TABLESPACE_LOG'); EXCEPTION WHEN OTHERS THEN v_error_msg := 'Failed to create TABLESPACE_LOG: ' || SQLERRM; DBMS_OUTPUT.PUT_LINE('[ERROR] ' || v_error_msg); -- Log to tablespace_log if it was created in a prior run BEGIN EXECUTE IMMEDIATE 'INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, error_message) VALUES (''N/A'', 0, ''CREATE_TABLE'', ''FAILED'', :1)' USING v_error_msg; COMMIT; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('[WARNING] Could not log to TABLESPACE_LOG: ' || SQLERRM); END; RAISE; END; -- Step 6: Verify table creation DBMS_OUTPUT.PUT_LINE('[INFO] Verifying table creation'); SELECT COUNT(*) INTO v_tables_created FROM user_tables WHERE table_name IN ('TABLESPACE_CONTROL', 'TABLESPACE_LOG'); IF v_tables_created = 2 THEN DBMS_OUTPUT.PUT_LINE('[SUCCESS] Both TABLESPACE_CONTROL and TABLESPACE_LOG created successfully'); ELSE v_error_msg := 'Verification failed: Only ' || v_tables_created || ' of 2 tables created'; DBMS_OUTPUT.PUT_LINE('[ERROR] ' || v_error_msg); -- Log to tablespace_log BEGIN EXECUTE IMMEDIATE 'INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, error_message) VALUES (''N/A'', 0, ''VERIFY_TABLES'', ''FAILED'', :1)' USING v_error_msg; COMMIT; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('[WARNING] Could not log to TABLESPACE_LOG: ' || SQLERRM); END; RAISE_APPLICATION_ERROR(-20002, v_error_msg); END IF; DBMS_OUTPUT.PUT_LINE('---'); DBMS_OUTPUT.PUT_LINE('[SUCCESS] All tables checked, created, and verified successfully'); EXCEPTION WHEN OTHERS THEN v_error_msg := 'Unexpected error during table setup: ' || SQLERRM; DBMS_OUTPUT.PUT_LINE('[ERROR] ' || v_error_msg); -- Log to tablespace_log if available BEGIN EXECUTE IMMEDIATE 'INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, error_message) VALUES (''N/A'', 0, ''TABLE_SETUP'', ''FAILED'', :1)' USING v_error_msg; COMMIT; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('[WARNING] Could not log to TABLESPACE_LOG: ' || SQLERRM); END; RAISE; END; /

  
  -- Step 2: Populate tablespace_control (replace with your 64 tablespaces) BEGIN INSERT INTO tablespace_control (tablespace_name, initial_size_gb) VALUES ('TS_REVANTH', 6268); INSERT INTO tablespace_control (tablespace_name, initial_size_gb) VALUES ('TABLESPACE2', 3000); INSERT INTO tablespace_control (tablespace_name, initial_size_gb) VALUES ('TABLESPACE3', 2500); -- Add remaining 61 tablespaces (e.g., from parse_tablespace_script.sql) -- Example loop for testing (comment out after replacing): /* FOR i IN 4..64 LOOP INSERT INTO tablespace_control (tablespace_name, initial_size_gb) VALUES ('TABLESPACE' || i, CASE WHEN MOD(i, 2) = 0 THEN 3000 ELSE 2000 END); END LOOP; */ COMMIT; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error populating tablespace_control: ' || SQLERRM); RAISE; END; / -- Step 3: Submit the scheduler job DECLARE v_job_name CONSTANT VARCHAR2(30) := USER || '.CREATE_TS_BATCH_JOB'; -- Ensure user schema v_common_clause CONSTANT VARCHAR2(500) := 'AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING DEFAULT NO INMEMORY ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON'; v_max_retries CONSTANT NUMBER := 3; v_batch_size CONSTANT NUMBER := 3; -- Smaller batch for stability v_sql VARCHAR2(1000); BEGIN -- Drop existing job if it exists BEGIN DBMS_SCHEDULER.DROP_JOB(v_job_name, force => TRUE); EXCEPTION WHEN OTHERS THEN IF SQLCODE = -27475 THEN NULL; ELSE RAISE; END IF; END; -- Create the job DBMS_SCHEDULER.CREATE_JOB( job_name => v_job_name, job_type => 'PLSQL_BLOCK', job_action => q'[ DECLARE v_sql VARCHAR2(1000); v_common_clause CONSTANT VARCHAR2(500) := 'AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING DEFAULT NO INMEMORY ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON'; v_max_retries CONSTANT NUMBER := 3; v_batch_size CONSTANT NUMBER := 3; v_processed NUMBER := 0; v_error_msg VARCHAR2(4000); BEGIN FOR rec IN ( SELECT tablespace_name, initial_size_gb, retry_count FROM tablespace_control WHERE status IN ('PENDING', 'FAILED') ORDER BY tablespace_name ) LOOP -- Validate tablespace name 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, action, status, error_message) VALUES (rec.tablespace_name, 'VALIDATE', 'FAILED', v_error_msg); COMMIT; CONTINUE; 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; 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, action, status) VALUES (rec.tablespace_name, 'SKIP_EXISTS', 'COMPLETED'); COMMIT; CONTINUE; END IF; END; -- Mark as RUNNING UPDATE tablespace_control SET status = 'RUNNING', last_attempt = SYSTIMESTAMP WHERE tablespace_name = rec.tablespace_name; COMMIT; -- Create tablespace v_sql := 'CREATE BIGFILE TABLESPACE ' || rec.tablespace_name || ' DATAFILE SIZE ' || rec.initial_size_gb || 'G ' || v_common_clause; BEGIN EXECUTE IMMEDIATE v_sql; UPDATE tablespace_control SET status = 'COMPLETED', retry_count = 0, last_attempt = SYSTIMESTAMP WHERE tablespace_name = rec.tablespace_name; INSERT INTO tablespace_log (tablespace_name, action, status) VALUES (rec.tablespace_name, 'CREATE', 'COMPLETED'); COMMIT; EXCEPTION WHEN OTHERS THEN v_error_msg := SQLERRM; UPDATE tablespace_control SET status = 'FAILED', retry_count = retry_count + 1, last_attempt = SYSTIMESTAMP WHERE tablespace_name = rec.tablespace_name; INSERT INTO tablespace_log (tablespace_name, action, status, error_message) VALUES (rec.tablespace_name, 'CREATE', 'FAILED', v_error_msg); COMMIT; IF rec.retry_count + 1 < v_max_retries THEN INSERT INTO tablespace_log (tablespace_name, action, status) VALUES (rec.tablespace_name, 'AUTO_RETRY', 'SCHEDULED'); COMMIT; END IF; END; -- Batch pause v_processed := v_processed + 1; IF MOD(v_processed, v_batch_size) = 0 THEN DBMS_LOCK.SLEEP(180); END IF; END LOOP; END; ]', start_date => SYSTIMESTAMP, repeat_interval => NULL, enabled => TRUE, auto_drop => FALSE, comments => 'Batch create bigfile tablespaces with skip existing and validation' ); DBMS_OUTPUT.PUT_LINE('Job ' || v_job_name || ' submitted. Monitor with DBA_SCHEDULER_JOBS and tablespace_log.'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error submitting job: ' || SQLERRM); END; /

  
  

• Run in TOAD: Copy to SQL Editor, execute (F5), with SET SERVEROUTPUT ON;.
• Output: Job CREATE_TS_BATCH_JOB (re)submitted. Monitor with provided queries.
• Behavior: Reads tablespace_control, creates tablespaces in batches of 10, retries failures up to 3 times, logs to tablespace_log.

-- =============================================

-- Description:   Script to stop all running scheduler jobs

--                with a specific naming pattern.

-- =============================================

SET SERVEROUTPUT ON;

BEGIN

    DBMS_OUTPUT.PUT_LINE('=== Attempting to stop scheduled jobs ===');

    FOR rec IN (SELECT job_name FROM dba_scheduler_jobs WHERE job_name LIKE 'CREATE_TS_BATCH_%') LOOP

        BEGIN

            DBMS_SCHEDULER.STOP_JOB(

                job_name => rec.job_name,

                force    => TRUE

            );

            DBMS_OUTPUT.PUT_LINE('SUCCESS: Stopped job ' || rec.job_name);

        EXCEPTION

            WHEN OTHERS THEN

                DBMS_OUTPUT.PUT_LINE('ERROR: Failed to stop job ' || rec.job_name || '. Error: ' || SQLERRM);

        END;

    END LOOP;

    DBMS_OUTPUT.PUT_LINE('=== Job stop process complete ===');

END;

/

-- =============================================

-- Description:   Script to drop all scheduled jobs

--                with a specific naming pattern.

-- =============================================

SET SERVEROUTPUT ON;

BEGIN

    DBMS_OUTPUT.PUT_LINE('=== Attempting to drop scheduled jobs ===');

    FOR rec IN (SELECT job_name FROM dba_scheduler_jobs WHERE job_name LIKE 'CREATE_TS_BATCH_%') LOOP

        BEGIN

            DBMS_SCHEDULER.DROP_JOB(

                job_name => rec.job_name,

                force    => TRUE

            );

            DBMS_OUTPUT.PUT_LINE('SUCCESS: Dropped job ' || rec.job_name);

        EXCEPTION

            WHEN OTHERS THEN

                DBMS_OUTPUT.PUT_LINE('ERROR: Failed to drop job ' || rec.job_name || '. Error: ' || SQLERRM);

        END;

    END LOOP;

    DBMS_OUTPUT.PUT_LINE('=== Job drop process complete ===');

END;

/

Step 4: Monitor Progress

Check the job’s progress in TOAD using these queries:

1. Job Status:
   sql

   SELECT job_name, state, status, start_date, end_date, run_duration

   FROM DBA_SCHEDULER_JOB_RUN_DETAILS

   WHERE job_name = 'CREATE_TS_BATCH_JOB'

   ORDER BY start_date DESC;
2. Control Table (Progress):
   sql

   SELECT tablespace_name, initial_size_gb, status, retry_count, last_attempt

   FROM tablespace_control

   ORDER BY tablespace_name;
3. Detailed Logs:
   sql

   SELECT log_id, tablespace_name, action, status, error_message, log_time

   FROM tablespace_log

   ORDER BY log_time DESC;
4. Summary:
   sql

   SELECT status, COUNT(*) AS count

   FROM tablespace_control

   GROUP BY status;
5. Verify Tablespaces:
   sql

   SELECT tablespace_name, bigfile, bytes/1024/1024/1024 AS size_gb

   FROM dba_tablespaces

   WHERE tablespace_name LIKE 'TS_DATA_%'

   ORDER BY tablespace_name;

Step 5: Handle Failures and Restarts

• Automatic Retries: The job retries FAILED tablespaces up to 3 times (configurable via v_max_retries).
• Manual Restart:
  • Fix issues (e.g., scale RDS storage for ORA-01658 errors).
  • Reset failed tablespaces:
    sql

    UPDATE tablespace_control SET status = 'PENDING', retry_count = 0 WHERE status = 'FAILED';

    COMMIT;
  • Re-run the anonymous block (Step 3). It skips COMPLETED tablespaces.
• Stop Job (if stuck):
  sql

  EXEC DBMS_SCHEDULER.STOP_JOB('CREATE_TS_BATCH_JOB', TRUE);

Step 6: Cleanup (Optional)

After successful completion:

• Drop Tables:
  sql

  DROP TABLE tablespace_control;

  DROP TABLE tablespace_log;
• Drop Job (if auto_drop => FALSE):
  sql

  EXEC DBMS_SCHEDULER.DROP_JOB('CREATE_TS_BATCH_JOB');

RDS-Specific Notes

• Storage: 64 tablespaces at ~3000G each (~192 TB) exceeds RDS max (64 TiB for gp3). Verify with:
  sql

  SELECT SUM(bytes)/1024/1024/1024 AS total_gb FROM dba_data_files;
  Scale storage via AWS console if needed.
• Privileges: Ensure CREATE TABLESPACE, CREATE JOB, CREATE TABLE:
  sql

  GRANT CREATE TABLESPACE, CREATE JOB, CREATE TABLE TO your_username;
• Performance: Batch size (10) and 60-second pauses (DBMS_LOCK.SLEEP(60)) prevent RDS resource spikes. Adjust if needed (e.g., v_batch_size = 5, DBMS_LOCK.SLEEP(120)).
• Timeouts: The scheduler job runs server-side, avoiding TOAD’s 1-hour timeout.