CREATE OR REPLACE PROCEDURE create_tablespace_batch(p_batch_id IN NUMBER) AS
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 PARALLEL 16';
v_max_retries CONSTANT NUMBER := 3;
v_batch_size CONSTANT NUMBER := 3;
v_processed NUMBER := 0;
v_error_msg VARCHAR2(4000);
v_sql VARCHAR2(1000);
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
DBMS_OUTPUT.PUT_LINE('[INFO] Starting batch ' || p_batch_id || ' processing');
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
-- 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, batch_id, action, status, error_message)
VALUES (rec.tablespace_name, p_batch_id, 'VALIDATE', 'FAILED', v_error_msg);
COMMIT;
CONTINUE;
END IF;
-- Check if tablespace already 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, batch_id, action, status)
VALUES (rec.tablespace_name, p_batch_id, 'SKIP_EXISTS', 'COMPLETED');
COMMIT;
CONTINUE;
END IF;
END;
-- Update status to RUNNING
UPDATE tablespace_control
SET status = 'RUNNING', start_time = COALESCE(start_time, SYSTIMESTAMP), 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, batch_id, action, status)
VALUES (rec.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 = rec.tablespace_name;
INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, error_message)
VALUES (rec.tablespace_name, p_batch_id, 'CREATE', 'FAILED', v_error_msg);
COMMIT;
IF rec.retry_count + 1 < v_max_retries THEN
INSERT INTO tablespace_log (tablespace_name, batch_id, action, status)
VALUES (rec.tablespace_name, p_batch_id, 'AUTO_RETRY', 'SCHEDULED');
COMMIT;
END IF;
END;
v_processed := v_processed + 1;
IF MOD(v_processed, v_batch_size) = 0 THEN
DBMS_LOCK.SLEEP(180); -- Pause after processing v_batch_size tablespaces
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('[SUCCESS] Batch ' || p_batch_id || ' completed');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('[ERROR] Batch ' || p_batch_id || ' failed: ' || SQLERRM);
INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, error_message)
VALUES ('N/A', p_batch_id, 'BATCH_PROCESSING', 'FAILED', SQLERRM);
COMMIT;
END;
/
Updated Table Setup Script
INSERT INTO tablespace_control (tablespace_name, initial_size_gb, batch_id) VALUES ('DATA_01', 6268, 14);
INSERT INTO tablespace_control (tablespace_name, initial_size_gb, batch_id) VALUES ('IDX_01', 3084, 15);
INSERT INTO tablespace_control (tablespace_name, initial_size_gb, batch_id) VALUES ('D_DATA_01', 1800, 16);
INSERT INTO tablespace_control (tablespace_name, initial_size_gb, batch_id) VALUES ('D_IDX_01', 1685, 17);
COMMIT;
Issue: If these tablespaces (DATA_01, IDX_01, D_DATA_01, D_IDX_01) already exist in tablespace_control (in batches 1 and 5), these INSERTs will fail with ORA-00001: unique constraint violated due to the primary key on tablespace_name. Since you’ve confirmed they’re already in the table, these INSERTs are redundant and should be skipped.
UPDATE tablespace_control SET batch_id = 15 WHERE tablespace_name = 'IDX_01';
UPDATE tablespace_control SET batch_id = 16 WHERE tablespace_name = 'D_DATA_01';
UPDATE tablespace_control SET batch_id = 17 WHERE tablespace_name = 'D_IDX_01';
COMMIT;
SELECT batch_id, COUNT(*) AS tablespace_count, LISTAGG(tablespace_name, ', ') WITHIN GROUP (ORDER BY tablespace_name) AS tablespaces
FROM tablespace_control
GROUP BY batch_id
ORDER BY batch_id;
SELECT batch_id, tablespace_name, status, error_message
FROM tablespace_log
WHERE batch_id IN (14, 15, 16, 17) AND status = 'FAILED';
UPDATE tablespace_control
SET status = 'PENDING', retry_count = 0
WHERE batch_id IN (14, 15, 16, 17) AND status = 'FAILED';
COMMIT;
SELECT tablespace_name, bigfile, ROUND(bytes/1024/1024/1024, 2) AS size_gb
FROM dba_tablespaces
WHERE tablespace_name IN ('DATA_01', 'IDX_01', 'D_DATA_01', 'D_IDX_01')
ORDER BY tablespace_name;
SELECT batch_id, tablespace_name, action, status, error_message,
TO_CHAR(log_time, 'YYYY-MM-DD HH24:MI:SS.FF6') AS log_time
FROM tablespace_log
WHERE batch_id IN (14, 15, 16, 17)
ORDER BY batch_id, log_time DESC;
SELECT job_name, status, start_date, run_duration, additional_info
FROM DBA_SCHEDULER_JOB_RUN_DETAILS
WHERE job_name LIKE 'CREATE_TS_BATCH_%' AND job_name IN ('CREATE_TS_BATCH_14', 'CREATE_TS_BATCH_15', 'CREATE_TS_BATCH_16', 'CREATE_TS_BATCH_17');
SET SERVEROUTPUT ON;
DECLARE
v_tablespace_control_exists NUMBER;
v_tablespace_log_exists NUMBER;
v_privilege_count NUMBER;
v_error_msg VARCHAR2(4000);
v_tables_created NUMBER;
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 with start_time
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,
start_time TIMESTAMP, -- Tracks initial creation start time
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);
-- Log to DBMS_OUTPUT only, as tablespace_log doesn't exist yet
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;
/
Updated Scheduler Script
Modify create_multiple_tablespaces_batch.sql to set start_time when marking status = 'RUNNING':
SET SERVEROUTPUT ON;
SET SERVEROUTPUT ON;
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 PARALLEL 16';
v_max_retries CONSTANT NUMBER := 3;
v_batch_size CONSTANT NUMBER := 3;
v_max_concurrent_jobs CONSTANT NUMBER := 4;
v_sql VARCHAR2(1000);
v_job_name VARCHAR2(30);
v_job_exists NUMBER;
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
DBMS_OUTPUT.PUT_LINE('[INFO] Starting batch tablespace creation setup');
-- Step 1: Create a job for each distinct batch_id not already scheduled
FOR batch_rec IN (
SELECT DISTINCT batch_id
FROM tablespace_control
WHERE batch_id IN (
SELECT batch_id
FROM tablespace_control
MINUS
SELECT TO_NUMBER(REGEXP_SUBSTR(job_name, 'CREATE_TS_BATCH_([0-9]+)', 1, 1, NULL, 1)) AS batch_id
FROM DBA_SCHEDULER_JOBS
WHERE job_name LIKE 'CREATE_TS_BATCH_%'
AND REGEXP_LIKE(job_name, 'CREATE_TS_BATCH_[0-9]+') -- Ensure valid numeric suffix
)
ORDER BY batch_id
) LOOP
v_job_name := USER || '.CREATE_TS_BATCH_' || batch_rec.batch_id;
-- Verify job doesn't already exist
SELECT COUNT(*) INTO v_job_exists
FROM DBA_SCHEDULER_JOBS
WHERE job_name = v_job_name;
IF v_job_exists = 0 THEN
-- Create job for batch_id
DBMS_SCHEDULER.CREATE_JOB(
job_name => v_job_name,
job_type => 'PLSQL_BLOCK',
job_action => q'[
DECLARE
v_sql VARCHAR2(1000);
v_common_clause CONSTANT VARCHAR2(500) := 'AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING DEFAULT NO INMEMORY ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON PARALLEL 16';
v_max_retries CONSTANT NUMBER := 3;
v_batch_size CONSTANT NUMBER := 3;
v_processed NUMBER := 0;
v_error_msg VARCHAR2(4000);
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
DBMS_OUTPUT.PUT_LINE('[INFO] Starting batch ' || ]' || batch_rec.batch_id || q'[ processing');
FOR rec IN (
SELECT tablespace_name, initial_size_gb, retry_count
FROM tablespace_control
WHERE batch_id = ]' || batch_rec.batch_id || q'[ AND 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, batch_id, action, status, error_message)
VALUES (rec.tablespace_name, ]' || batch_rec.batch_id || q'[, 'VALIDATE', 'FAILED', v_error_msg);
COMMIT;
CONTINUE;
END IF;
-- Check if tablespace already 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, batch_id, action, status)
VALUES (rec.tablespace_name, ]' || batch_rec.batch_id || q'[, 'SKIP_EXISTS', 'COMPLETED');
COMMIT;
CONTINUE;
END IF;
END;
-- Update status to RUNNING
UPDATE tablespace_control
SET status = 'RUNNING', start_time = COALESCE(start_time, SYSTIMESTAMP), 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, batch_id, action, status)
VALUES (rec.tablespace_name, ]' || batch_rec.batch_id || q'[, 'CREATE', 'COMPLETED');
COMMIT;
EXCEPTION
WHEN OTHERS THEN
v_error_msg := SQLERRM;
UPDATE tablespace_control
SET status = 'FAILED', retry_count = retry_count + 1, last_attempt = SYSTIMESTAMP
WHERE tablespace_name = rec.tablespace_name;
INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, error_message)
VALUES (rec.tablespace_name, ]' || batch_rec.batch_id || q'[, 'CREATE', 'FAILED', v_error_msg);
COMMIT;
IF rec.retry_count + 1 < v_max_retries THEN
INSERT INTO tablespace_log (tablespace_name, batch_id, action, status)
VALUES (rec.tablespace_name, ]' || batch_rec.batch_id || q'[, 'AUTO_RETRY', 'SCHEDULED');
COMMIT;
END IF;
END;
v_processed := v_processed + 1;
IF MOD(v_processed, v_batch_size) = 0 THEN
DBMS_LOCK.SLEEP(180); -- Pause after processing v_batch_size tablespaces
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('[SUCCESS] Batch ]' || batch_rec.batch_id || q'[ completed');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('[ERROR] Batch ]' || batch_rec.batch_id || q'[ failed: ' || SQLERRM);
END;
]',
start_date => SYSTIMESTAMP + (batch_rec.batch_id - 1) / (24 * 60 * 60 / v_max_concurrent_jobs),
repeat_interval => NULL,
enabled => TRUE,
auto_drop => FALSE,
comments => 'Create bigfile tablespaces for batch ' || batch_rec.batch_id
);
DBMS_OUTPUT.PUT_LINE('[SUCCESS] Submitted job ' || v_job_name || ' for batch ' || batch_rec.batch_id);
ELSE
DBMS_OUTPUT.PUT_LINE('[INFO] Job ' || v_job_name || ' already exists, skipping creation');
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('[SUCCESS] All new batch jobs submitted');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('[ERROR] Error submitting jobs: ' || SQLERRM);
END;
/
generate_tablespace_control_inserts_cte.sql
SET SERVEROUTPUT ON;
WITH input_script AS (
-- Your 64 CREATE statements as a table
SELECT line
FROM (
SELECT 'CREATE BIGFILE TABLESPACE TS_REVANTH DATAFILE SIZE 6268G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING DEFAULT NO INMEMORY ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON' AS line FROM DUAL UNION ALL
SELECT 'CREATE BIGFILE TABLESPACE TABLESPACE2 DATAFILE SIZE 3000G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING DEFAULT NO INMEMORY ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON' FROM DUAL UNION ALL
SELECT 'CREATE BIGFILE TABLESPACE TABLESPACE3 DATAFILE SIZE 2500G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING DEFAULT NO INMEMORY ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON' FROM DUAL UNION ALL
SELECT 'CREATE BIGFILE TABLESPACE TS_DATA_04 DATAFILE SIZE 2000G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING DEFAULT NO INMEMORY ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON' FROM DUAL UNION ALL
SELECT 'CREATE BIGFILE TABLESPACE TS_DATA_05 DATAFILE SIZE 3000G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING DEFAULT NO INMEMORY ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON' FROM DUAL UNION ALL
SELECT 'CREATE BIGFILE TABLESPACE TS_DATA_06 DATAFILE SIZE 2000G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING DEFAULT NO INMEMORY ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON' FROM DUAL UNION ALL
SELECT 'CREATE BIGFILE TABLESPACE TS_DATA_07 DATAFILE SIZE 2500G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING DEFAULT NO INMEMORY ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON' FROM DUAL
-- Add remaining 57 CREATE statements here
)
),
excluded_tablespaces AS (
SELECT 'SYSTEM' AS tablespace_name FROM DUAL UNION ALL
SELECT 'SYSAUX' FROM DUAL UNION ALL
SELECT 'UNDOTBS1' FROM DUAL UNION ALL
SELECT 'TEMP' FROM DUAL UNION ALL
SELECT 'USERS' FROM DUAL
),
parsed_data AS (
SELECT
REGEXP_SUBSTR(line, 'TABLESPACE\s+(\w+)', 1, 1, NULL, 1) AS tablespace_name,
REGEXP_SUBSTR(line, 'SIZE\s+(\d+)', 1, 1, NULL, 1) AS size_gb,
ROWNUM AS row_num
FROM input_script
WHERE REGEXP_SUBSTR(line, 'TABLESPACE\s+(\w+)', 1, 1, NULL, 1) IS NOT NULL
AND REGEXP_SUBSTR(line, 'SIZE\s+(\d+)', 1, 1, NULL, 1) IS NOT NULL
),
tablespace_data AS (
SELECT
p.tablespace_name,
p.size_gb,
CASE
WHEN p.row_num <= 4 THEN 1
WHEN p.row_num <= 10 THEN 2
ELSE CEIL((p.row_num-10)/5)+2
END AS batch_id
FROM parsed_data p
WHERE NOT EXISTS (
SELECT 1 FROM dba_tablespaces t WHERE t.tablespace_name = p.tablespace_name
)
AND p.tablespace_name NOT IN (SELECT tablespace_name FROM excluded_tablespaces)
)
SELECT
'INSERT INTO tablespace_control (tablespace_name, initial_size_gb, batch_id) VALUES (''' ||
tablespace_name || ''', ' || size_gb || ', ' || batch_id || ');' AS insert_statement
FROM tablespace_data
UNION ALL
SELECT '=== Total INSERT Statements Generated: ' || COUNT(*) || ' ===' FROM tablespace_data
UNION ALL
SELECT '-- COMMIT; -- Uncomment to execute INSERTs' FROM DUAL
ORDER BY 1;
Verify Start Times:
SELECT tablespace_name, initial_size_gb, status, retry_count,
TO_CHAR(start_time, 'YYYY-MM-DD HH24:MI:SS.FF6') AS start_time,
TO_CHAR(last_attempt, 'YYYY-MM-DD HH24:MI:SS.FF6') AS last_attempt
FROM tablespace_control
ORDER BY batch_id, tablespace_name;
Monitor Jobs:
SELECT job_name, state, run_count, failure_count
FROM DBA_SCHEDULER_JOBS
WHERE job_name LIKE 'CREATE_TS_BATCH_%';
SELECT log_id, tablespace_name, action, status, error_message, log_time
FROM tablespace_log
WHERE action IN ('CREATE', 'SKIP_EXISTS', 'AUTO_RETRY', 'VALIDATE')
ORDER BY batch_id, log_time DESC;
No comments:
Post a Comment