SET SERVEROUTPUT ON;
-- Step 1: Drop and recreate tables
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE tablespace_control';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -942 THEN NULL;
ELSE RAISE;
END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE tablespace_log';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -942 THEN NULL;
ELSE RAISE;
END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE q'[
CREATE TABLE tablespace_control (
tablespace_name VARCHAR2(30) PRIMARY KEY,
initial_size_gb NUMBER NOT NULL,
batch_id NUMBER, -- Added for batch processing
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('Created tablespace_control');
END;
/
BEGIN
EXECUTE IMMEDIATE q'[
CREATE TABLE tablespace_log (
log_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
tablespace_name VARCHAR2(30),
batch_id NUMBER, -- Added for batch tracking
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('Created tablespace_log');
END;
/
-- Step 2: Populate tablespace_control with batch assignments
BEGIN
INSERT INTO tablespace_control (tablespace_name, initial_size_gb, batch_id)
VALUES ('TS_REVANTH', 6268, 1);
INSERT INTO tablespace_control (tablespace_name, initial_size_gb, batch_id)
VALUES ('TABLESPACE2', 3000, 1);
INSERT INTO tablespace_control (tablespace_name, initial_size_gb, batch_id)
VALUES ('TABLESPACE3', 2500, 1);
-- Add remaining 61 tablespaces with batch_id (5 per batch, last batch may have fewer)
-- Example loop for testing (replace with your actual data):
/*
FOR i IN 4..64 LOOP
INSERT INTO tablespace_control (tablespace_name, initial_size_gb, batch_id)
VALUES ('TABLESPACE' || i, CASE WHEN MOD(i, 2) = 0 THEN 3000 ELSE 2000 END, CEIL(i/5));
END LOOP;
/
COMMIT;
DBMS_OUTPUT.PUT_LINE('Populated tablespace_control with batch assignments');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error populating tablespace_control: ' || SQLERRM);
RAISE;
END;
/
-- Step 3: Create multiple scheduler jobs for batch processing
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; -- Tablespaces per batch
v_job_name VARCHAR2(30);
v_sql VARCHAR2(1000);
v_batch_count NUMBER;
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
-- Get number of batches
SELECT CEIL(COUNT() / v_batch_size) INTO v_batch_count
FROM tablespace_control;
-- Create a job for each batch
FOR i IN 1..v_batch_count LOOP
v_job_name := USER || '.CREATE_TS_BATCH_' || i;
-- 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 job for batch i
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_error_msg VARCHAR2(4000);
BEGIN
FOR rec IN (
SELECT tablespace_name, initial_size_gb, retry_count
FROM tablespace_control
WHERE batch_id = ]' || i || q'[ AND status IN ('PENDING', 'FAILED')
ORDER BY tablespace_name
) LOOP
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, ]' || i || q'[, 'VALIDATE', 'FAILED', v_error_msg);
COMMIT;
CONTINUE;
END IF;
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, ]' || i || q'[, 'SKIP_EXISTS', 'COMPLETED');
COMMIT;
CONTINUE;
END IF;
END;
UPDATE tablespace_control SET status = 'RUNNING', last_attempt = SYSTIMESTAMP
WHERE tablespace_name = rec.tablespace_name;
COMMIT;
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, ]' || i || 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, ]' || i || 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, ]' || i || q'[, 'AUTO_RETRY', 'SCHEDULED');
COMMIT;
END IF;
END;
END LOOP;
END;
]',
start_date => SYSTIMESTAMP,
repeat_interval => NULL,
enabled => TRUE,
auto_drop => FALSE,
comments => 'Create bigfile tablespaces for batch ' || i
);
DBMS_OUTPUT.PUT_LINE('Submitted job ' || v_job_name || ' for batch ' || i);
END LOOP;
DBMS_OUTPUT.PUT_LINE('All batch jobs submitted. Monitor with DBA_SCHEDULER_JOBS and tablespace_log.');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error submitting jobs: ' || SQLERRM);
END;
/
No comments:
Post a Comment