SET SERVEROUTPUT ON;
DECLARE
TYPE t_tablespace IS RECORD (
tablespace_name VARCHAR2(30),
initial_size_gb NUMBER,
batch_id NUMBER
);
TYPE t_tablespace_array IS TABLE OF t_tablespace;
v_tablespaces t_tablespace_array := t_tablespace_array();
TYPE t_create_stmt_array IS TABLE OF VARCHAR2(1000);
v_create_statements t_create_stmt_array := t_create_stmt_array(
'CREATE BIGFILE TABLESPACE TS_DATA_01 DATAFILE SIZE 50G 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',
'CREATE BIGFILE TABLESPACE TS_DATA_02 DATAFILE SIZE 75G 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',
'CREATE BIGFILE TABLESPACE TS_DATA_03 DATAFILE SIZE 250G 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',
'CREATE BIGFILE TABLESPACE TS_DATA_04 DATAFILE SIZE 200G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING DEFAULT NO INMEMORY ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON',
'CREATE BIGFILE TABLESPACE TS_DATA_05 DATAFILE SIZE 400G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING DEFAULT NO INMEMORY ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON',
'CREATE BIGFILE TABLESPACE TS_DATA_06 DATAFILE SIZE 450G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING DEFAULT NO INMEMORY ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON',
'CREATE BIGFILE TABLESPACE TS_DATA_07 DATAFILE SIZE 100G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING DEFAULT NO INMEMORY ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON',
'CREATE BIGFILE TABLESPACE TS_DATA_08 DATAFILE SIZE 150G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING DEFAULT NO INMEMORY ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON',
'CREATE BIGFILE TABLESPACE TS_DATA_09 DATAFILE SIZE 175G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING DEFAULT NO INMEMORY ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON',
'CREATE BIGFILE TABLESPACE TS_DATA_10 DATAFILE SIZE 225G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING DEFAULT NO INMEMORY ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON',
'CREATE BIGFILE TABLESPACE TS_DATA_11 DATAFILE SIZE 300G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING DEFAULT NO INMEMORY ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON',
'CREATE BIGFILE TABLESPACE TS_DATA_12 DATAFILE SIZE 350G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING DEFAULT NO INMEMORY ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON',
'CREATE BIGFILE TABLESPACE DATA_01 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 PARALLEL 16',
'CREATE BIGFILE TABLESPACE IDX_01 DATAFILE SIZE 3084G 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',
'CREATE BIGFILE TABLESPACE D_DATA_01 DATAFILE SIZE 1800G 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',
'CREATE BIGFILE TABLESPACE D_IDX_01 DATAFILE SIZE 1685G 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'
-- Add remaining 48 CREATE statements
);
v_row_count NUMBER := 0;
v_insert_count NUMBER := 0;
v_exists NUMBER;
v_error_msg VARCHAR2(4000);
v_batch_id NUMBER;
v_prev_batch_id NUMBER := 0;
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
DBMS_OUTPUT.PUT_LINE('[INFO] Populating tablespace_control from CREATE statements');
-- Step 1: Parse CREATE statements
v_tablespaces.EXTEND(v_create_statements.COUNT);
FOR i IN 1..v_create_statements.COUNT LOOP
v_tablespaces(i).tablespace_name := REGEXP_SUBSTR(v_create_statements(i), 'TABLESPACE\s+(\w+)', 1, 1, NULL, 1);
v_tablespaces(i).initial_size_gb := REGEXP_SUBSTR(v_create_statements(i), 'SIZE\s+(\d+)', 1, 1, NULL, 1);
v_tablespaces(i).batch_id := CASE
WHEN i <= 12 THEN i
ELSE CEIL((i - 12) / 64) + 12
END;
END LOOP;
-- Step 2: Process tablespaces
FOR i IN 1..v_tablespaces.COUNT LOOP
IF v_tablespaces(i).tablespace_name IS NULL OR v_tablespaces(i).initial_size_gb IS NULL THEN
DBMS_OUTPUT.PUT_LINE('-- Skipped: Invalid CREATE statement at index ' || i);
CONTINUE;
END IF;
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('-- Skipped: ' || v_tablespaces(i).tablespace_name || ' (' || v_error_msg || ')');
BEGIN
INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, error_message)
VALUES (v_tablespaces(i).tablespace_name, 0, 'VALIDATE', 'FAILED', v_error_msg);
COMMIT;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
CONTINUE;
END IF;
SELECT COUNT(*) INTO v_exists
FROM tablespace_control
WHERE tablespace_name = v_tablespaces(i).tablespace_name;
IF v_exists = 0 THEN
v_row_count := v_row_count + 1;
v_insert_count := v_insert_count + 1;
v_batch_id := v_tablespaces(i).batch_id;
IF v_batch_id != v_prev_batch_id THEN
DBMS_OUTPUT.PUT_LINE('-- Batch ' || v_batch_id || ' (batch_id = ' || v_batch_id || ')');
v_prev_batch_id := v_batch_id;
END IF;
BEGIN
INSERT INTO tablespace_control (tablespace_name, initial_size_gb, batch_id, status, created_by)
VALUES (v_tablespaces(i).tablespace_name, v_tablespaces(i).initial_size_gb, v_batch_id, 'PENDING', USER);
DBMS_OUTPUT.PUT_LINE(
'INSERT INTO tablespace_control (tablespace_name, initial_size_gb, batch_id, status, created_by) VALUES (''' ||
v_tablespaces(i).tablespace_name || ''', ' || v_tablespaces(i).initial_size_gb || ', ' || v_batch_id || ', ''PENDING'', USER);'
);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE('-- Skipped: ' || v_tablespaces(i).tablespace_name || ' (already in tablespace_control due to race condition)');
WHEN OTHERS THEN
v_error_msg := SQLERRM;
DBMS_OUTPUT.PUT_LINE('-- Skipped: ' || v_tablespaces(i).tablespace_name || ' (error: ' || v_error_msg || ')');
BEGIN
INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, error_message)
VALUES (v_tablespaces(i).tablespace_name, v_batch_id, 'INSERT', 'FAILED', v_error_msg);
COMMIT;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
END;
ELSE
DBMS_OUTPUT.PUT_LINE('-- Skipped: ' || v_tablespaces(i).tablespace_name || ' (already in tablespace_control)');
END IF;
END LOOP;
IF v_insert_count > 0 THEN
COMMIT;
DBMS_OUTPUT.PUT_LINE('[SUCCESS] Total INSERTs Committed: ' || v_insert_count);
ELSE
DBMS_OUTPUT.PUT_LINE('[INFO] No INSERTs Generated');
END IF;
EXCEPTION
WHEN OTHERS THEN
v_error_msg := SQLERRM;
DBMS_OUTPUT.PUT_LINE('[ERROR] Error during script execution: ' || v_error_msg);
BEGIN
INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, error_message)
VALUES ('N/A', 0, 'INSERT_GENERATION', 'FAILED', v_error_msg);
COMMIT;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
ROLLBACK;
END;
/
===========
working:
SET SERVEROUTPUT ON;
DECLARE
TYPE t_tablespace IS RECORD (
tablespace_name VARCHAR2(30),
initial_size_gb NUMBER,
batch_id NUMBER
);
TYPE t_tablespace_array IS TABLE OF t_tablespace;
v_tablespaces t_tablespace_array := t_tablespace_array();
TYPE t_create_stmt_array IS TABLE OF VARCHAR2(1000);
v_create_statements t_create_stmt_array := t_create_stmt_array(
'CREATE BIGFILE TABLESPACE TS_DATA_01 DATAFILE SIZE 50G 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',
'CREATE BIGFILE TABLESPACE TS_DATA_02 DATAFILE SIZE 75G 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',
'CREATE BIGFILE TABLESPACE TS_DATA_03 DATAFILE SIZE 250G 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',
'CREATE BIGFILE TABLESPACE TS_DATA_04 DATAFILE SIZE 200G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING DEFAULT NO INMEMORY ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON',
'CREATE BIGFILE TABLESPACE TS_DATA_05 DATAFILE SIZE 400G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING DEFAULT NO INMEMORY ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON',
'CREATE BIGFILE TABLESPACE TS_DATA_06 DATAFILE SIZE 450G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING DEFAULT NO INMEMORY ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON',
'CREATE BIGFILE TABLESPACE DATA_01 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 PARALLEL 16',
'CREATE BIGFILE TABLESPACE IDX_01 DATAFILE SIZE 3084G 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',
'CREATE BIGFILE TABLESPACE D_DATA_01 DATAFILE SIZE 1800G 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',
'CREATE BIGFILE TABLESPACE D_IDX_01 DATAFILE SIZE 1685G 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'
-- Add remaining 54 CREATE statements
);
v_row_count NUMBER := 0;
v_insert_count NUMBER := 0;
v_exists NUMBER;
v_error_msg VARCHAR2(4000);
v_batch_id NUMBER;
v_prev_batch_id NUMBER := 0;
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
DBMS_OUTPUT.PUT_LINE('[INFO] Populating tablespace_control from CREATE statements');
-- Step 1: Parse CREATE statements
v_tablespaces.EXTEND(v_create_statements.COUNT);
FOR i IN 1..v_create_statements.COUNT LOOP
v_tablespaces(i).tablespace_name := REGEXP_SUBSTR(v_create_statements(i), 'TABLESPACE\s+(\w+)', 1, 1, NULL, 1);
v_tablespaces(i).initial_size_gb := REGEXP_SUBSTR(v_create_statements(i), 'SIZE\s+(\d+)', 1, 1, NULL, 1);
v_tablespaces(i).batch_id := CASE
WHEN v_tablespaces(i).tablespace_name = 'DATA_01' THEN 14
WHEN v_tablespaces(i).tablespace_name = 'IDX_01' THEN 15
WHEN v_tablespaces(i).tablespace_name = 'D_DATA_01' THEN 16
WHEN v_tablespaces(i).tablespace_name = 'D_IDX_01' THEN 17
WHEN v_tablespaces(i).tablespace_name = 'TS_DATA_01' THEN 18
WHEN v_tablespaces(i).tablespace_name = 'TS_DATA_02' THEN 19
WHEN v_tablespaces(i).tablespace_name = 'TS_DATA_03' THEN 20
ELSE CEIL((i - 7) / 5) + 2
END;
END LOOP;
-- Step 2: Process tablespaces
FOR i IN 1..v_tablespaces.COUNT LOOP
IF v_tablespaces(i).tablespace_name IS NULL OR v_tablespaces(i).initial_size_gb IS NULL THEN
DBMS_OUTPUT.PUT_LINE('-- Skipped: Invalid CREATE statement at index ' || i);
CONTINUE;
END IF;
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('-- Skipped: ' || v_tablespaces(i).tablespace_name || ' (' || v_error_msg || ')');
BEGIN
INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, error_message)
VALUES (v_tablespaces(i).tablespace_name, 0, 'VALIDATE', 'FAILED', v_error_msg);
COMMIT;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
CONTINUE;
END IF;
SELECT COUNT(*) INTO v_exists
FROM tablespace_control
WHERE tablespace_name = v_tablespaces(i).tablespace_name;
IF v_exists = 0 THEN
v_row_count := v_row_count + 1;
v_insert_count := v_insert_count + 1;
v_batch_id := v_tablespaces(i).batch_id;
IF v_batch_id != v_prev_batch_id THEN
DBMS_OUTPUT.PUT_LINE('-- Batch ' || v_batch_id || ' (batch_id = ' || v_batch_id || ')');
v_prev_batch_id := v_batch_id;
END IF;
BEGIN
INSERT INTO tablespace_control (tablespace_name, initial_size_gb, batch_id, status, created_by)
VALUES (v_tablespaces(i).tablespace_name, v_tablespaces(i).initial_size_gb, v_batch_id, 'PENDING', USER);
DBMS_OUTPUT.PUT_LINE(
'INSERT INTO tablespace_control (tablespace_name, initial_size_gb, batch_id, status, created_by) VALUES (''' ||
v_tablespaces(i).tablespace_name || ''', ' || v_tablespaces(i).initial_size_gb || ', ' || v_batch_id || ', ''PENDING'', USER);'
);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE('-- Skipped: ' || v_tablespaces(i).tablespace_name || ' (already in tablespace_control due to race condition)');
WHEN OTHERS THEN
v_error_msg := SQLERRM;
DBMS_OUTPUT.PUT_LINE('-- Skipped: ' || v_tablespaces(i).tablespace_name || ' (error: ' || v_error_msg || ')');
BEGIN
INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, error_message)
VALUES (v_tablespaces(i).tablespace_name, v_batch_id, 'INSERT', 'FAILED', v_error_msg);
COMMIT;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
END;
ELSE
DBMS_OUTPUT.PUT_LINE('-- Skipped: ' || v_tablespaces(i).tablespace_name || ' (already in tablespace_control)');
END IF;
END LOOP;
IF v_insert_count > 0 THEN
COMMIT;
DBMS_OUTPUT.PUT_LINE('[SUCCESS] Total INSERTs Committed: ' || v_insert_count);
ELSE
DBMS_OUTPUT.PUT_LINE('[INFO] No INSERTs Generated');
END IF;
EXCEPTION
WHEN OTHERS THEN
v_error_msg := SQLERRM;
DBMS_OUTPUT.PUT_LINE('[ERROR] Error during script execution: ' || v_error_msg);
BEGIN
INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, error_message)
VALUES ('N/A', 0, 'INSERT_GENERATION', 'FAILED', v_error_msg);
COMMIT;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
ROLLBACK;
END;
/
==================================================================
Execute create_multiple_tablespaces_batch.sql (version a607d9ff-336b-4cb3-a2e8-d8f7a3ee94eb).
SET SERVEROUTPUT ON;
DECLARE
TYPE string_array IS TABLE OF VARCHAR2(30);
v_include_list string_array := string_array('TS_REVANTH', 'TABLESPACE2', 'TABLESPACE3', 'TS_DATA_04', 'TS_DATA_05', 'TS_DATA_06', 'TS_DATA_07' /* Add your 64 tablespaces */);
v_exclude_list string_array := string_array('SYSTEM', 'SYSAUX', 'UNDOTBS1', 'TEMP', 'USERS');
v_row_count NUMBER := 0;
v_insert_count NUMBER := 0;
v_exists NUMBER;
v_error_msg VARCHAR2(4000);
v_tablespace_name VARCHAR2(30);
v_size_gb NUMBER;
v_batch_id NUMBER;
v_prev_batch_id NUMBER := 0;
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
DBMS_OUTPUT.PUT_LINE('=== Generating and Inserting into tablespace_control ===');
FOR i IN 1..v_include_list.COUNT LOOP
v_tablespace_name := v_include_list(i);
IF NOT REGEXP_LIKE(v_tablespace_name, '^[A-Z][A-Z0-9_]{1,29}$') THEN
v_error_msg := 'Invalid tablespace name: ' || v_tablespace_name;
DBMS_OUTPUT.PUT_LINE('-- Skipped: ' || v_tablespace_name || ' (' || v_error_msg || ')');
BEGIN
INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, error_message)
VALUES (v_tablespace_name, 0, 'VALIDATE', 'FAILED', v_error_msg);
COMMIT;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
CONTINUE;
END IF;
SELECT COUNT(*) INTO v_exists
FROM dba_tablespaces
WHERE tablespace_name = v_tablespace_name;
IF v_exists = 1 THEN
SELECT COUNT(*) INTO v_exists
FROM tablespace_control
WHERE tablespace_name = v_tablespace_name;
IF v_exists = 0 THEN
v_row_count := v_row_count + 1;
v_insert_count := v_insert_count + 1;
-- Assign batch_id: 5 tablespaces per batch
v_batch_id := CEIL(v_row_count / 5);
-- Print batch comment if batch_id changes
IF v_batch_id != v_prev_batch_id THEN
DBMS_OUTPUT.PUT_LINE('-- Batch ' || v_batch_id || ' (batch_id = ' || v_batch_id || ')');
v_prev_batch_id := v_batch_id;
END IF;
SELECT ROUND(SUM(d.bytes) / 1024 / 1024 / 1024, 2)
INTO v_size_gb
FROM dba_data_files d
WHERE d.tablespace_name = v_tablespace_name;
BEGIN
INSERT INTO tablespace_control (tablespace_name, initial_size_gb, batch_id)
VALUES (v_tablespace_name, v_size_gb, v_batch_id);
DBMS_OUTPUT.PUT_LINE(
'INSERT INTO tablespace_control (tablespace_name, initial_size_gb, batch_id) VALUES (''' ||
v_tablespace_name || ''', ' || v_size_gb || ', ' || v_batch_id || ');'
);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE('-- Skipped: ' || v_tablespace_name || ' (already in tablespace_control due to race condition)');
WHEN OTHERS THEN
v_error_msg := SQLERRM;
DBMS_OUTPUT.PUT_LINE('-- Skipped: ' || v_tablespace_name || ' (error: ' || v_error_msg || ')');
BEGIN
INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, error_message)
VALUES (v_tablespace_name, v_batch_id, 'INSERT', 'FAILED', v_error_msg);
COMMIT;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
END;
ELSE
DBMS_OUTPUT.PUT_LINE('-- Skipped: ' || v_tablespace_name || ' (already in tablespace_control)');
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('-- Skipped: ' || v_tablespace_name || ' (does not exist in database)');
END IF;
END LOOP;
IF v_insert_count > 0 THEN
DBMS_OUTPUT.PUT_LINE('=== Total INSERTs Completed: ' || v_insert_count || ' ===');
DBMS_OUTPUT.PUT_LINE('-- To commit these changes, run the following statement:');
DBMS_OUTPUT.PUT_LINE('COMMIT;');
ELSE
DBMS_OUTPUT.PUT_LINE('=== No INSERTs Generated ===');
END IF;
EXCEPTION
WHEN OTHERS THEN
v_error_msg := SQLERRM;
DBMS_OUTPUT.PUT_LINE('Error during script execution: ' || v_error_msg);
BEGIN
INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, error_message)
VALUES ('N/A', 0, 'INSERT_GENERATION', 'FAILED', v_error_msg);
COMMIT;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
ROLLBACK;
END;
/
===================================================================
DECLARE
-- Define include and exclude lists
TYPE string_array IS TABLE OF VARCHAR2(30);
v_include_list string_array := string_array('TS_REVANTH', 'TABLESPACE2', 'TABLESPACE3', 'TS_DATA_04', 'TS_DATA_05', 'TS_DATA_06', 'TS_DATA_07' /* Add your 64 tablespaces */);
v_exclude_list string_array := string_array('SYSTEM', 'SYSAUX', 'UNDOTBS1', 'TEMP', 'USERS');
v_row_count NUMBER := 0;
v_insert_count NUMBER := 0;
v_exists NUMBER;
v_error_msg VARCHAR2(4000);
v_tablespace_name VARCHAR2(30);
v_size_gb NUMBER;
v_batch_id NUMBER;
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
DBMS_OUTPUT.PUT_LINE('=== Generating and Inserting into tablespace_control ===');
-- Process include list
FOR i IN 1..v_include_list.COUNT LOOP
v_tablespace_name := v_include_list(i);
-- Validate tablespace name
IF NOT REGEXP_LIKE(v_tablespace_name, '^[A-Z][A-Z0-9_]{1,29}$') THEN
v_error_msg := 'Invalid tablespace name: ' || v_tablespace_name;
DBMS_OUTPUT.PUT_LINE('-- Skipped: ' || v_tablespace_name || ' (' || v_error_msg || ')');
-- Log to tablespace_log if it exists
BEGIN
INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, error_message)
VALUES (v_tablespace_name, 0, 'VALIDATE', 'FAILED', v_error_msg);
COMMIT;
EXCEPTION
WHEN OTHERS THEN NULL; -- Ignore if tablespace_log doesn't exist
END;
CONTINUE;
END IF;
-- Check if tablespace exists in database
SELECT COUNT(*) INTO v_exists
FROM dba_tablespaces
WHERE tablespace_name = v_tablespace_name;
IF v_exists = 1 THEN
-- Check if already in tablespace_control
SELECT COUNT(*) INTO v_exists
FROM tablespace_control
WHERE tablespace_name = v_tablespace_name;
IF v_exists = 0 THEN
v_row_count := v_row_count + 1;
v_insert_count := v_insert_count + 1;
-- Assign batch_id (4 for batch 1, 6 for batch 2, 5 thereafter)
v_batch_id := CASE
WHEN v_row_count <= 4 THEN 1
WHEN v_row_count <= 10 THEN 2
ELSE CEIL((v_row_count-10)/5)+2
END;
-- Get size from DBA_TABLESPACES
SELECT ROUND(SUM(d.bytes) / 1024 / 1024 / 1024, 2)
INTO v_size_gb
FROM dba_data_files d
WHERE d.tablespace_name = v_tablespace_name;
-- Generate and execute INSERT
BEGIN
INSERT INTO tablespace_control (tablespace_name, initial_size_gb, batch_id)
VALUES (v_tablespace_name, v_size_gb, v_batch_id);
DBMS_OUTPUT.PUT_LINE(
'INSERT INTO tablespace_control (tablespace_name, initial_size_gb, batch_id) VALUES (''' ||
v_tablespace_name || ''', ' || v_size_gb || ', ' || v_batch_id || ');'
);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE('-- Skipped: ' || v_tablespace_name || ' (already in tablespace_control due to race condition)');
WHEN OTHERS THEN
v_error_msg := SQLERRM;
DBMS_OUTPUT.PUT_LINE('-- Skipped: ' || v_tablespace_name || ' (error: ' || v_error_msg || ')');
-- Log to tablespace_log
BEGIN
INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, error_message)
VALUES (v_tablespace_name, v_batch_id, 'INSERT', 'FAILED', v_error_msg);
COMMIT;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
END;
ELSE
DBMS_OUTPUT.PUT_LINE('-- Skipped: ' || v_tablespace_name || ' (already in tablespace_control)');
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('-- Skipped: ' || v_tablespace_name || ' (does not exist in database)');
END IF;
END LOOP;
-- Output results
IF v_insert_count > 0 THEN
DBMS_OUTPUT.PUT_LINE('=== Total INSERTs Completed: ' || v_insert_count || ' ===');
DBMS_OUTPUT.PUT_LINE('-- To commit these changes, run the following statement:');
DBMS_OUTPUT.PUT_LINE('COMMIT;');
ELSE
DBMS_OUTPUT.PUT_LINE('=== No INSERTs Generated ===');
END IF;
EXCEPTION
WHEN OTHERS THEN
v_error_msg := SQLERRM;
DBMS_OUTPUT.PUT_LINE('Error during script execution: ' || v_error_msg);
-- Log to tablespace_log if it exists
BEGIN
INSERT INTO tablespace_log (tablespace_name, batch_id, action, status, error_message)
VALUES ('N/A', 0, 'INSERT_GENERATION', 'FAILED', v_error_msg);
COMMIT;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
ROLLBACK;
END;
/
No comments:
Post a Comment