-- =============================================
-- Create Date: 2025-08-31
-- Description: An idempotent script to create control and log tables,
-- populate tablespace_control with 63 tablespaces,
-- and submit DBMS_SCHEDULER jobs: individual parallel jobs for large tablespaces (>=200 GB),
-- and a batched job for small tablespaces (<200 GB) for Oracle RDS.
-- =============================================
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
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);
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);
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);
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;
-- Step 7: Populate tablespace_control with 63 tablespaces
DBMS_OUTPUT.PUT_LINE('[INFO] Populating TABLESPACE_CONTROL with 63 tablespaces');
BEGIN
-- Example: Insert 63 tablespaces with varied sizes (adjust names and sizes as needed)
INSERT INTO tablespace_control (tablespace_name, initial_size_gb) VALUES ('TS_8000_01', 8000);
INSERT INTO tablespace_control (tablespace_name, initial_size_gb) VALUES ('TS_8000_02', 8000);
INSERT INTO tablespace_control (tablespace_name, initial_size_gb) VALUES ('TS_8000_03', 8000);
INSERT INTO tablespace_control (tablespace_name, initial_size_gb) VALUES ('TS_4000_01', 4000);
INSERT INTO tablespace_control (tablespace_name, initial_size_gb) VALUES ('TS_4000_02', 4000);
INSERT INTO tablespace_control (tablespace_name, initial_size_gb) VALUES ('TS_2000_01', 2000);
INSERT INTO tablespace_control (tablespace_name, initial_size_gb) VALUES ('TS_2000_02', 2000);
INSERT INTO tablespace_control (tablespace_name, initial_size_gb) VALUES ('TS_1500_01', 1500);
INSERT INTO tablespace_control (tablespace_name, initial_size_gb) VALUES ('TS_1500_02', 1500);
INSERT INTO tablespace_control (tablespace_name, initial_size_gb) VALUES ('TS_200_01', 200);
INSERT INTO tablespace_control (tablespace_name, initial_size_gb) VALUES ('TS_200_02', 200);
-- Insert 52 more tablespaces for <60 GB (example sizes)
FOR i IN 1..52 LOOP
INSERT INTO tablespace_control (tablespace_name, initial_size_gb)
VALUES ('TS_LOW_' || LPAD(i, 2, '0'), CASE WHEN MOD(i, 2) = 0 THEN 50 ELSE 30 END);
END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE('[SUCCESS] Populated TABLESPACE_CONTROL with 63 tablespaces');
EXCEPTION
WHEN OTHERS THEN
v_error_msg := 'Error populating TABLESPACE_CONTROL: ' || SQLERRM;
DBMS_OUTPUT.PUT_LINE('[ERROR] ' || v_error_msg);
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, error_message)
VALUES (''N/A'', 0, ''POPULATE_CONTROL'', ''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 8: Assign batch IDs for small tablespaces (<200 GB)
DBMS_OUTPUT.PUT_LINE('[INFO] Assigning batch IDs for small tablespaces (<200 GB)');
DECLARE
CURSOR c_small_ts IS
SELECT tablespace_name, initial_size_gb
FROM tablespace_control
WHERE initial_size_gb < 200 AND status = 'PENDING'
ORDER BY initial_size_gb DESC, tablespace_name ASC;
v_batch_id INTEGER := 1;
v_count INTEGER := 0;
BEGIN
FOR r IN c_small_ts LOOP
v_count := v_count + 1;
UPDATE tablespace_control
SET batch_id = v_batch_id
WHERE tablespace_name = r.tablespace_name;
IF v_count = 5 THEN
v_batch_id := v_batch_id + 1;
v_count := 0;
END IF;
END LOOP;
-- For large (>=200 GB), set batch_id to NULL to indicate independent execution
UPDATE tablespace_control
SET batch_id = NULL
WHERE initial_size_gb >= 200;
COMMIT;
DBMS_OUTPUT.PUT_LINE('[SUCCESS] Assigned batch IDs to small tablespaces');
EXCEPTION
WHEN OTHERS THEN
v_error_msg := 'Error assigning batch IDs: ' || SQLERRM;
DBMS_OUTPUT.PUT_LINE('[ERROR] ' || v_error_msg);
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, error_message)
VALUES (''N/A'', 0, ''ASSIGN_BATCHES'', ''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;
DBMS_OUTPUT.PUT_LINE('---');
DBMS_OUTPUT.PUT_LINE('[SUCCESS] All tables checked, created, populated, and batch IDs assigned successfully');
EXCEPTION
WHEN OTHERS THEN
v_error_msg := 'Unexpected error during table setup: ' || SQLERRM;
DBMS_OUTPUT.PUT_LINE('[ERROR] ' || v_error_msg);
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 9: Create procedure to create a single tablespace (used in jobs)
CREATE OR REPLACE PROCEDURE create_single_tablespace (
p_tablespace_name IN VARCHAR2,
p_size_gb IN NUMBER,
p_batch_id IN NUMBER DEFAULT NULL
) AS
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_exists NUMBER;
v_error_msg VARCHAR2(4000);
v_max_retries CONSTANT NUMBER := 3;
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;
UPDATE tablespace_control
SET status = 'FAILED',
retry_count = retry_count + 1,
last_attempt = SYSTIMESTAMP
WHERE tablespace_name = p_tablespace_name;
INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, error_message)
VALUES (p_tablespace_name, p_batch_id, 'VALIDATE', 'FAILED', v_error_msg);
COMMIT;
RETURN;
END IF;
-- Check if tablespace exists
SELECT COUNT(*) INTO v_exists
FROM dba_tablespaces
WHERE tablespace_name = p_tablespace_name;
IF v_exists > 0 THEN
UPDATE tablespace_control
SET status = 'COMPLETED',
last_attempt = SYSTIMESTAMP
WHERE tablespace_name = p_tablespace_name;
INSERT INTO tablespace_log (tablespace_name, batch_id, action, status)
VALUES (p_tablespace_name, p_batch_id, 'SKIP_EXISTS', 'COMPLETED');
COMMIT;
RETURN;
END IF;
-- Mark as RUNNING
UPDATE tablespace_control
SET status = 'RUNNING',
last_attempt = SYSTIMESTAMP
WHERE tablespace_name = p_tablespace_name;
COMMIT;
-- Create tablespace
v_sql := 'CREATE BIGFILE TABLESPACE ' || DBMS_ASSERT.SCHEMA_NAME(p_tablespace_name) ||
' DATAFILE SIZE ' || p_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;
INSERT INTO tablespace_log (tablespace_name, batch_id, action, status)
VALUES (p_tablespace_name, p_batch_id, '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 = p_tablespace_name;
INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, error_message)
VALUES (p_tablespace_name, p_batch_id, 'CREATE', 'FAILED', v_error_msg);
COMMIT;
IF (SELECT retry_count FROM tablespace_control WHERE tablespace_name = p_tablespace_name) < v_max_retries THEN
INSERT INTO tablespace_log (tablespace_name, batch_id, action, status)
VALUES (p_tablespace_name, p_batch_id, 'AUTO_RETRY', 'SCHEDULED');
COMMIT;
END IF;
END;
END;
/
-- Step 10: Submit scheduler jobs - individual for large (>=200 GB), batched for small (<200 GB)
DECLARE
v_common_clause CONSTANT VARCHAR2(500) := 'AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING DEFAULT NO INMEMORY ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON';
v_max_retries CONSTANT NUMBER := 3;
v_batch_size CONSTANT NUMBER := 5;
v_sql VARCHAR2(1000);
v_job_prefix CONSTANT VARCHAR2(30) := USER || '.CREATE_TS_';
v_small_job_name CONSTANT VARCHAR2(30) := USER || '.CREATE_TS_SMALL_BATCH_JOB';
BEGIN
-- Drop existing jobs if they exist
FOR rec IN (SELECT job_name FROM user_scheduler_jobs WHERE job_name LIKE 'CREATE_TS_%') LOOP
BEGIN
DBMS_SCHEDULER.DROP_JOB(rec.job_name, force => TRUE);
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -27475 THEN
NULL;
ELSE
RAISE;
END IF;
END;
END LOOP;
-- Create individual jobs for large tablespaces (>=200 GB)
FOR rec IN (
SELECT tablespace_name, initial_size_gb
FROM tablespace_control
WHERE initial_size_gb >= 200 AND status IN ('PENDING', 'FAILED')
ORDER BY initial_size_gb DESC, tablespace_name
) LOOP
DBMS_SCHEDULER.CREATE_JOB(
job_name => v_job_prefix || REPLACE(rec.tablespace_name, '_', ''),
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN create_single_tablespace(''' || rec.tablespace_name || ''', ' || rec.initial_size_gb || '); END;',
start_date => SYSTIMESTAMP,
repeat_interval => NULL,
enabled => TRUE,
auto_drop => FALSE,
comments => 'Individual job to create large tablespace ' || rec.tablespace_name
);
DBMS_OUTPUT.PUT_LINE('Individual job ' || v_job_prefix || REPLACE(rec.tablespace_name, '_', '') || ' submitted for large tablespace ' || rec.tablespace_name);
END LOOP;
-- Create single batched job for small tablespaces (<200 GB)
DBMS_SCHEDULER.CREATE_JOB(
job_name => v_small_job_name,
job_type => 'PLSQL_BLOCK',
job_action => q'[
DECLARE
v_processed NUMBER := 0;
v_error_msg VARCHAR2(4000);
v_current_batch_id NUMBER := 0;
BEGIN
FOR batch IN (
SELECT DISTINCT batch_id
FROM tablespace_control
WHERE batch_id IS NOT NULL
ORDER BY batch_id
) LOOP
v_current_batch_id := batch.batch_id;
FOR rec IN (
SELECT tablespace_name, initial_size_gb, retry_count, batch_id
FROM tablespace_control
WHERE status IN ('PENDING', 'FAILED')
AND batch_id = v_current_batch_id
ORDER BY tablespace_name
) LOOP
create_single_tablespace(rec.tablespace_name, rec.initial_size_gb, rec.batch_id);
v_processed := v_processed + 1;
IF MOD(v_processed, 5) = 0 THEN
DBMS_LOCK.SLEEP(180); -- Pause 3 minutes between batches for small tablespaces
END IF;
END LOOP;
END LOOP;
END;
]',
start_date => SYSTIMESTAMP,
repeat_interval => NULL,
enabled => TRUE,
auto_drop => FALSE,
comments => 'Batched job to create small tablespaces in groups of 5'
);
DBMS_OUTPUT.PUT_LINE('Batched job ' || v_small_job_name || ' submitted for small tablespaces.');
EXCEPTION
WHEN OTHERS THEN
v_error_msg := 'Error submitting jobs: ' || SQLERRM;
DBMS_OUTPUT.PUT_LINE('[ERROR] ' || v_error_msg);
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, error_message)
VALUES (''N/A'', 0, ''SUBMIT_JOBS'', ''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;
/
-- =============================================
-- Description: Script to stop all running scheduler jobs
-- =============================================
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_%') 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
-- =============================================
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_%') 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;
/
-- Create Date: 2025-08-31
-- Description: An idempotent script to create control and log tables,
-- populate tablespace_control with 63 tablespaces,
-- and submit DBMS_SCHEDULER jobs: individual parallel jobs for large tablespaces (>=200 GB),
-- and a batched job for small tablespaces (<200 GB) for Oracle RDS.
-- =============================================
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
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);
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);
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);
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;
-- Step 7: Populate tablespace_control with 63 tablespaces
DBMS_OUTPUT.PUT_LINE('[INFO] Populating TABLESPACE_CONTROL with 63 tablespaces');
BEGIN
-- Example: Insert 63 tablespaces with varied sizes (adjust names and sizes as needed)
INSERT INTO tablespace_control (tablespace_name, initial_size_gb) VALUES ('TS_8000_01', 8000);
INSERT INTO tablespace_control (tablespace_name, initial_size_gb) VALUES ('TS_8000_02', 8000);
INSERT INTO tablespace_control (tablespace_name, initial_size_gb) VALUES ('TS_8000_03', 8000);
INSERT INTO tablespace_control (tablespace_name, initial_size_gb) VALUES ('TS_4000_01', 4000);
INSERT INTO tablespace_control (tablespace_name, initial_size_gb) VALUES ('TS_4000_02', 4000);
INSERT INTO tablespace_control (tablespace_name, initial_size_gb) VALUES ('TS_2000_01', 2000);
INSERT INTO tablespace_control (tablespace_name, initial_size_gb) VALUES ('TS_2000_02', 2000);
INSERT INTO tablespace_control (tablespace_name, initial_size_gb) VALUES ('TS_1500_01', 1500);
INSERT INTO tablespace_control (tablespace_name, initial_size_gb) VALUES ('TS_1500_02', 1500);
INSERT INTO tablespace_control (tablespace_name, initial_size_gb) VALUES ('TS_200_01', 200);
INSERT INTO tablespace_control (tablespace_name, initial_size_gb) VALUES ('TS_200_02', 200);
-- Insert 52 more tablespaces for <60 GB (example sizes)
FOR i IN 1..52 LOOP
INSERT INTO tablespace_control (tablespace_name, initial_size_gb)
VALUES ('TS_LOW_' || LPAD(i, 2, '0'), CASE WHEN MOD(i, 2) = 0 THEN 50 ELSE 30 END);
END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE('[SUCCESS] Populated TABLESPACE_CONTROL with 63 tablespaces');
EXCEPTION
WHEN OTHERS THEN
v_error_msg := 'Error populating TABLESPACE_CONTROL: ' || SQLERRM;
DBMS_OUTPUT.PUT_LINE('[ERROR] ' || v_error_msg);
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, error_message)
VALUES (''N/A'', 0, ''POPULATE_CONTROL'', ''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 8: Assign batch IDs for small tablespaces (<200 GB)
DBMS_OUTPUT.PUT_LINE('[INFO] Assigning batch IDs for small tablespaces (<200 GB)');
DECLARE
CURSOR c_small_ts IS
SELECT tablespace_name, initial_size_gb
FROM tablespace_control
WHERE initial_size_gb < 200 AND status = 'PENDING'
ORDER BY initial_size_gb DESC, tablespace_name ASC;
v_batch_id INTEGER := 1;
v_count INTEGER := 0;
BEGIN
FOR r IN c_small_ts LOOP
v_count := v_count + 1;
UPDATE tablespace_control
SET batch_id = v_batch_id
WHERE tablespace_name = r.tablespace_name;
IF v_count = 5 THEN
v_batch_id := v_batch_id + 1;
v_count := 0;
END IF;
END LOOP;
-- For large (>=200 GB), set batch_id to NULL to indicate independent execution
UPDATE tablespace_control
SET batch_id = NULL
WHERE initial_size_gb >= 200;
COMMIT;
DBMS_OUTPUT.PUT_LINE('[SUCCESS] Assigned batch IDs to small tablespaces');
EXCEPTION
WHEN OTHERS THEN
v_error_msg := 'Error assigning batch IDs: ' || SQLERRM;
DBMS_OUTPUT.PUT_LINE('[ERROR] ' || v_error_msg);
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, error_message)
VALUES (''N/A'', 0, ''ASSIGN_BATCHES'', ''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;
DBMS_OUTPUT.PUT_LINE('---');
DBMS_OUTPUT.PUT_LINE('[SUCCESS] All tables checked, created, populated, and batch IDs assigned successfully');
EXCEPTION
WHEN OTHERS THEN
v_error_msg := 'Unexpected error during table setup: ' || SQLERRM;
DBMS_OUTPUT.PUT_LINE('[ERROR] ' || v_error_msg);
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 9: Create procedure to create a single tablespace (used in jobs)
CREATE OR REPLACE PROCEDURE create_single_tablespace (
p_tablespace_name IN VARCHAR2,
p_size_gb IN NUMBER,
p_batch_id IN NUMBER DEFAULT NULL
) AS
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_exists NUMBER;
v_error_msg VARCHAR2(4000);
v_max_retries CONSTANT NUMBER := 3;
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;
UPDATE tablespace_control
SET status = 'FAILED',
retry_count = retry_count + 1,
last_attempt = SYSTIMESTAMP
WHERE tablespace_name = p_tablespace_name;
INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, error_message)
VALUES (p_tablespace_name, p_batch_id, 'VALIDATE', 'FAILED', v_error_msg);
COMMIT;
RETURN;
END IF;
-- Check if tablespace exists
SELECT COUNT(*) INTO v_exists
FROM dba_tablespaces
WHERE tablespace_name = p_tablespace_name;
IF v_exists > 0 THEN
UPDATE tablespace_control
SET status = 'COMPLETED',
last_attempt = SYSTIMESTAMP
WHERE tablespace_name = p_tablespace_name;
INSERT INTO tablespace_log (tablespace_name, batch_id, action, status)
VALUES (p_tablespace_name, p_batch_id, 'SKIP_EXISTS', 'COMPLETED');
COMMIT;
RETURN;
END IF;
-- Mark as RUNNING
UPDATE tablespace_control
SET status = 'RUNNING',
last_attempt = SYSTIMESTAMP
WHERE tablespace_name = p_tablespace_name;
COMMIT;
-- Create tablespace
v_sql := 'CREATE BIGFILE TABLESPACE ' || DBMS_ASSERT.SCHEMA_NAME(p_tablespace_name) ||
' DATAFILE SIZE ' || p_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;
INSERT INTO tablespace_log (tablespace_name, batch_id, action, status)
VALUES (p_tablespace_name, p_batch_id, '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 = p_tablespace_name;
INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, error_message)
VALUES (p_tablespace_name, p_batch_id, 'CREATE', 'FAILED', v_error_msg);
COMMIT;
IF (SELECT retry_count FROM tablespace_control WHERE tablespace_name = p_tablespace_name) < v_max_retries THEN
INSERT INTO tablespace_log (tablespace_name, batch_id, action, status)
VALUES (p_tablespace_name, p_batch_id, 'AUTO_RETRY', 'SCHEDULED');
COMMIT;
END IF;
END;
END;
/
-- Step 10: Submit scheduler jobs - individual for large (>=200 GB), batched for small (<200 GB)
DECLARE
v_common_clause CONSTANT VARCHAR2(500) := 'AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING DEFAULT NO INMEMORY ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON';
v_max_retries CONSTANT NUMBER := 3;
v_batch_size CONSTANT NUMBER := 5;
v_sql VARCHAR2(1000);
v_job_prefix CONSTANT VARCHAR2(30) := USER || '.CREATE_TS_';
v_small_job_name CONSTANT VARCHAR2(30) := USER || '.CREATE_TS_SMALL_BATCH_JOB';
BEGIN
-- Drop existing jobs if they exist
FOR rec IN (SELECT job_name FROM user_scheduler_jobs WHERE job_name LIKE 'CREATE_TS_%') LOOP
BEGIN
DBMS_SCHEDULER.DROP_JOB(rec.job_name, force => TRUE);
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -27475 THEN
NULL;
ELSE
RAISE;
END IF;
END;
END LOOP;
-- Create individual jobs for large tablespaces (>=200 GB)
FOR rec IN (
SELECT tablespace_name, initial_size_gb
FROM tablespace_control
WHERE initial_size_gb >= 200 AND status IN ('PENDING', 'FAILED')
ORDER BY initial_size_gb DESC, tablespace_name
) LOOP
DBMS_SCHEDULER.CREATE_JOB(
job_name => v_job_prefix || REPLACE(rec.tablespace_name, '_', ''),
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN create_single_tablespace(''' || rec.tablespace_name || ''', ' || rec.initial_size_gb || '); END;',
start_date => SYSTIMESTAMP,
repeat_interval => NULL,
enabled => TRUE,
auto_drop => FALSE,
comments => 'Individual job to create large tablespace ' || rec.tablespace_name
);
DBMS_OUTPUT.PUT_LINE('Individual job ' || v_job_prefix || REPLACE(rec.tablespace_name, '_', '') || ' submitted for large tablespace ' || rec.tablespace_name);
END LOOP;
-- Create single batched job for small tablespaces (<200 GB)
DBMS_SCHEDULER.CREATE_JOB(
job_name => v_small_job_name,
job_type => 'PLSQL_BLOCK',
job_action => q'[
DECLARE
v_processed NUMBER := 0;
v_error_msg VARCHAR2(4000);
v_current_batch_id NUMBER := 0;
BEGIN
FOR batch IN (
SELECT DISTINCT batch_id
FROM tablespace_control
WHERE batch_id IS NOT NULL
ORDER BY batch_id
) LOOP
v_current_batch_id := batch.batch_id;
FOR rec IN (
SELECT tablespace_name, initial_size_gb, retry_count, batch_id
FROM tablespace_control
WHERE status IN ('PENDING', 'FAILED')
AND batch_id = v_current_batch_id
ORDER BY tablespace_name
) LOOP
create_single_tablespace(rec.tablespace_name, rec.initial_size_gb, rec.batch_id);
v_processed := v_processed + 1;
IF MOD(v_processed, 5) = 0 THEN
DBMS_LOCK.SLEEP(180); -- Pause 3 minutes between batches for small tablespaces
END IF;
END LOOP;
END LOOP;
END;
]',
start_date => SYSTIMESTAMP,
repeat_interval => NULL,
enabled => TRUE,
auto_drop => FALSE,
comments => 'Batched job to create small tablespaces in groups of 5'
);
DBMS_OUTPUT.PUT_LINE('Batched job ' || v_small_job_name || ' submitted for small tablespaces.');
EXCEPTION
WHEN OTHERS THEN
v_error_msg := 'Error submitting jobs: ' || SQLERRM;
DBMS_OUTPUT.PUT_LINE('[ERROR] ' || v_error_msg);
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, error_message)
VALUES (''N/A'', 0, ''SUBMIT_JOBS'', ''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;
/
-- =============================================
-- Description: Script to stop all running scheduler jobs
-- =============================================
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_%') 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
-- =============================================
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_%') 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;
/
No comments:
Post a Comment