SET SERVEROUTPUT ON;
-- Step 1: Create tables if they don't exist
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('Created tablespace_control');
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -955 THEN -- Table already exists
DBMS_OUTPUT.PUT_LINE('Table tablespace_control already exists, skipping creation');
ELSE
RAISE;
END IF;
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,
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');
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -955 THEN -- Table already exists
DBMS_OUTPUT.PUT_LINE('Table tablespace_log already exists, skipping creation');
ELSE
RAISE;
END IF;
END;
/
-- Step 2: Populate tablespace_control with new batch assignments (conditional to avoid duplicates)
BEGIN
-- Insert new tablespaces if not exists (example for batch_id 6-8)
MERGE INTO tablespace_control t
USING (SELECT 'TS_REVANTH_6' AS tablespace_name, 6268 AS initial_size_gb, 6 AS batch_id FROM DUAL) s
ON (t.tablespace_name = s.tablespace_name)
WHEN NOT MATCHED THEN
INSERT (tablespace_name, initial_size_gb, batch_id)
VALUES (s.tablespace_name, s.initial_size_gb, s.batch_id);
MERGE INTO tablespace_control t
USING (SELECT 'TS_REVANTH_7' AS tablespace_name, 6268 AS initial_size_gb, 7 AS batch_id FROM DUAL) s
ON (t.tablespace_name = s.tablespace_name)
WHEN NOT MATCHED THEN
INSERT (tablespace_name, initial_size_gb, batch_id)
VALUES (s.tablespace_name, s.initial_size_gb, s.batch_id);
MERGE INTO tablespace_control t
USING (SELECT 'TS_REVANTH_8' AS tablespace_name, 6268 AS initial_size_gb, 8 AS batch_id FROM DUAL) s
ON (t.tablespace_name = s.tablespace_name)
WHEN NOT MATCHED THEN
INSERT (tablespace_name, initial_size_gb, batch_id)
VALUES (s.tablespace_name, s.initial_size_gb, s.batch_id);
COMMIT;
DBMS_OUTPUT.PUT_LINE('Populated tablespace_control with new batch assignments (6-8, skipped existing)');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error populating tablespace_control: ' || SQLERRM);
RAISE;
END;
/
-- Step 3: Create scheduler jobs for new batch_ids (6-8) with parallelism degree of 16
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_job_name VARCHAR2(30);
v_sql VARCHAR2(1000);
v_created_count NUMBER := 0;
v_min_new_batch_id NUMBER := 6; -- Start from batch_id 6 to skip 1-5
v_max_new_batch_id NUMBER := 8; -- Cap at batch_id 8 to avoid spill over
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
-- Debug: List existing jobs
DBMS_OUTPUT.PUT_LINE('Existing scheduler jobs:');
FOR job_rec IN (
SELECT JOB_NAME, STATE
FROM DBA_SCHEDULER_JOBS
WHERE JOB_NAME LIKE UPPER(USER) || '.CREATE_TS_BATCH_%'
ORDER BY JOB_NAME
) LOOP
DBMS_OUTPUT.PUT_LINE('Job: ' || job_rec.JOB_NAME || ', State: ' || job_rec.STATE);
END LOOP;
FOR rec IN (
SELECT DISTINCT batch_id
FROM tablespace_control
WHERE batch_id IS NOT NULL
AND batch_id >= v_min_new_batch_id
AND batch_id <= v_max_new_batch_id
AND NOT EXISTS (
SELECT 1
FROM DBA_SCHEDULER_JOBS
WHERE UPPER(JOB_NAME) = UPPER(USER) || '.CREATE_TS_BATCH_' || batch_id
)
ORDER BY batch_id
) LOOP
v_job_name := UPPER(USER) || '.CREATE_TS_BATCH_' || rec.batch_id;
DBMS_OUTPUT.PUT_LINE('Creating job for batch_id: ' || rec.batch_id);
-- Create job for batch rec.batch_id with parallelism
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
-- Enable parallel DDL and set parallelism degree to 16
EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DDL';
EXECUTE IMMEDIATE 'ALTER SESSION SET PARALLEL_FORCE_LOCAL = TRUE';
EXECUTE IMMEDIATE 'ALTER SESSION SET PARALLEL_MIN_SERVERS = 16';
EXECUTE IMMEDIATE 'ALTER SESSION SET PARALLEL_MAX_SERVERS = 16';
FOR rec IN (
SELECT tablespace_name, initial_size_gb, retry_count
FROM tablespace_control
WHERE batch_id = ]' || rec.batch_id || 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, ]' || rec.batch_id || q'[, 'VALIDATE', 'FAILED', v_error_msg);
COMMIT;
CONTINUE;
END IF;
DECLARE
v_exists NUMBER;
BEGIN
SELECT COUNT(*) INTO v_exists FROM DBA_TABLESPACES WHERE UPPER(tablespace_name) = UPPER(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, ]' || rec.batch_id || 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, ]' || 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, ]' || 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, ]' || rec.batch_id || 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 ' || rec.batch_id || ' with parallelism 16'
);
-- Set parallel attributes for the job
DBMS_SCHEDULER.SET_ATTRIBUTE(v_job_name, 'parallel_instances', 16);
DBMS_OUTPUT.PUT_LINE('Submitted job ' || v_job_name || ' for batch ' || rec.batch_id || ' (enabled: TRUE, parallelism: 16)');
v_created_count := v_created_count + 1;
END LOOP;
IF v_created_count = 0 THEN
DBMS_OUTPUT.PUT_LINE('No new batch jobs needed; all required batch_ids (between ' || v_min_new_batch_id || ' and ' || v_max_new_batch_id || ') have jobs.');
ELSE
DBMS_OUTPUT.PUT_LINE(v_created_count || ' new batch jobs submitted and enabled for parallel execution with degree 16. Monitor with DBA_SCHEDULER_JOBS and tablespace_log.');
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error submitting jobs: ' || SQLERRM);
RAISE;
END;
/
-- Step 1: Create tables if they don't exist
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('Created tablespace_control');
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -955 THEN -- Table already exists
DBMS_OUTPUT.PUT_LINE('Table tablespace_control already exists, skipping creation');
ELSE
RAISE;
END IF;
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,
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');
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -955 THEN -- Table already exists
DBMS_OUTPUT.PUT_LINE('Table tablespace_log already exists, skipping creation');
ELSE
RAISE;
END IF;
END;
/
-- Step 2: Populate tablespace_control with new batch assignments (conditional to avoid duplicates)
BEGIN
-- Insert new tablespaces if not exists (example for batch_id 6-8)
MERGE INTO tablespace_control t
USING (SELECT 'TS_REVANTH_6' AS tablespace_name, 6268 AS initial_size_gb, 6 AS batch_id FROM DUAL) s
ON (t.tablespace_name = s.tablespace_name)
WHEN NOT MATCHED THEN
INSERT (tablespace_name, initial_size_gb, batch_id)
VALUES (s.tablespace_name, s.initial_size_gb, s.batch_id);
MERGE INTO tablespace_control t
USING (SELECT 'TS_REVANTH_7' AS tablespace_name, 6268 AS initial_size_gb, 7 AS batch_id FROM DUAL) s
ON (t.tablespace_name = s.tablespace_name)
WHEN NOT MATCHED THEN
INSERT (tablespace_name, initial_size_gb, batch_id)
VALUES (s.tablespace_name, s.initial_size_gb, s.batch_id);
MERGE INTO tablespace_control t
USING (SELECT 'TS_REVANTH_8' AS tablespace_name, 6268 AS initial_size_gb, 8 AS batch_id FROM DUAL) s
ON (t.tablespace_name = s.tablespace_name)
WHEN NOT MATCHED THEN
INSERT (tablespace_name, initial_size_gb, batch_id)
VALUES (s.tablespace_name, s.initial_size_gb, s.batch_id);
COMMIT;
DBMS_OUTPUT.PUT_LINE('Populated tablespace_control with new batch assignments (6-8, skipped existing)');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error populating tablespace_control: ' || SQLERRM);
RAISE;
END;
/
-- Step 3: Create scheduler jobs for new batch_ids (6-8) with parallelism degree of 16
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_job_name VARCHAR2(30);
v_sql VARCHAR2(1000);
v_created_count NUMBER := 0;
v_min_new_batch_id NUMBER := 6; -- Start from batch_id 6 to skip 1-5
v_max_new_batch_id NUMBER := 8; -- Cap at batch_id 8 to avoid spill over
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
-- Debug: List existing jobs
DBMS_OUTPUT.PUT_LINE('Existing scheduler jobs:');
FOR job_rec IN (
SELECT JOB_NAME, STATE
FROM DBA_SCHEDULER_JOBS
WHERE JOB_NAME LIKE UPPER(USER) || '.CREATE_TS_BATCH_%'
ORDER BY JOB_NAME
) LOOP
DBMS_OUTPUT.PUT_LINE('Job: ' || job_rec.JOB_NAME || ', State: ' || job_rec.STATE);
END LOOP;
FOR rec IN (
SELECT DISTINCT batch_id
FROM tablespace_control
WHERE batch_id IS NOT NULL
AND batch_id >= v_min_new_batch_id
AND batch_id <= v_max_new_batch_id
AND NOT EXISTS (
SELECT 1
FROM DBA_SCHEDULER_JOBS
WHERE UPPER(JOB_NAME) = UPPER(USER) || '.CREATE_TS_BATCH_' || batch_id
)
ORDER BY batch_id
) LOOP
v_job_name := UPPER(USER) || '.CREATE_TS_BATCH_' || rec.batch_id;
DBMS_OUTPUT.PUT_LINE('Creating job for batch_id: ' || rec.batch_id);
-- Create job for batch rec.batch_id with parallelism
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
-- Enable parallel DDL and set parallelism degree to 16
EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DDL';
EXECUTE IMMEDIATE 'ALTER SESSION SET PARALLEL_FORCE_LOCAL = TRUE';
EXECUTE IMMEDIATE 'ALTER SESSION SET PARALLEL_MIN_SERVERS = 16';
EXECUTE IMMEDIATE 'ALTER SESSION SET PARALLEL_MAX_SERVERS = 16';
FOR rec IN (
SELECT tablespace_name, initial_size_gb, retry_count
FROM tablespace_control
WHERE batch_id = ]' || rec.batch_id || 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, ]' || rec.batch_id || q'[, 'VALIDATE', 'FAILED', v_error_msg);
COMMIT;
CONTINUE;
END IF;
DECLARE
v_exists NUMBER;
BEGIN
SELECT COUNT(*) INTO v_exists FROM DBA_TABLESPACES WHERE UPPER(tablespace_name) = UPPER(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, ]' || rec.batch_id || 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, ]' || 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, ]' || 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, ]' || rec.batch_id || 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 ' || rec.batch_id || ' with parallelism 16'
);
-- Set parallel attributes for the job
DBMS_SCHEDULER.SET_ATTRIBUTE(v_job_name, 'parallel_instances', 16);
DBMS_OUTPUT.PUT_LINE('Submitted job ' || v_job_name || ' for batch ' || rec.batch_id || ' (enabled: TRUE, parallelism: 16)');
v_created_count := v_created_count + 1;
END LOOP;
IF v_created_count = 0 THEN
DBMS_OUTPUT.PUT_LINE('No new batch jobs needed; all required batch_ids (between ' || v_min_new_batch_id || ' and ' || v_max_new_batch_id || ') have jobs.');
ELSE
DBMS_OUTPUT.PUT_LINE(v_created_count || ' new batch jobs submitted and enabled for parallel execution with degree 16. Monitor with DBA_SCHEDULER_JOBS and tablespace_log.');
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error submitting jobs: ' || SQLERRM);
RAISE;
END;
/
No comments:
Post a Comment