This is very important and also be careful in changing the values especially tablespace_name, storage and batch_id remember it is very important.
MERGE INTO tablespace_control t
USING (SELECT 'TS_REVANTH_6' AS tablespace_name, 6268 AS initial_size_gb, 6 AS batch_id FROM DUAL)
USING (SELECT 'TS_REVANTH_6' AS tablespace_name, 6268 AS initial_size_gb, 6 AS batch_id FROM DUAL)
/*
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
v_max_new_batch_id NUMBER := 8; -- Cap at batch_id 8 to avoid spill over
*/
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 only for new batch_ids within min and max range without existing jobs
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
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 = ]' || 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
);
DBMS_OUTPUT.PUT_LINE('Submitted job ' || v_job_name || ' for batch ' || rec.batch_id || ' (enabled: TRUE)');
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. 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 only for new batch_ids within min and max range without existing jobs
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
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 = ]' || 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
);
DBMS_OUTPUT.PUT_LINE('Submitted job ' || v_job_name || ' for batch ' || rec.batch_id || ' (enabled: TRUE)');
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. Monitor with DBA_SCHEDULER_JOBS and tablespace_log.');
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error submitting jobs: ' || SQLERRM);
RAISE;
END;
/
=======================================================
Summary for Notes
MERGE Inserts:Efficiently inserts new tablespaces into tablespace_control without duplicating existing records.Uses MERGE to check for existing tablespace_name before inserting, ensuring idempotency.
- Critical for scripts that rerun to avoid errors and maintain data consistency.
- Example: Inserts TS_REVANTH_6 with batch_id = 6 only if it doesn’t exist.
- v_min_new_batch_id NUMBER := 6;:
- Filters job creation to batch_ids >= 6, skipping existing jobs for 1–5.
- Prevents ORA-27477 by avoiding recreation of existing jobs.
- Allows focused processing of new batch_ids (6–8) in parallel with existing jobs.
- Adjustable for future runs to accommodate additional batch_ids.
How It Works:
- Filter Condition: The batch_id >= v_min_new_batch_id clause restricts the job creation loop to only process batch_ids 6 and above (e.g., 6, 7, 8).
- Combined with NOT EXISTS: Further ensures jobs are only created for batch_ids without existing scheduler jobs in DBA_SCHEDULER_JOBS.
- Dynamic Control: Setting v_min_new_batch_id to 6 reflects your requirement to start processing from batch_id = 6 (after jobs for 1–5 are already created or running).
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 only for new batch_ids (>= 6) without existing jobs
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
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 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
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 = ]' || 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
);
DBMS_OUTPUT.PUT_LINE('Submitted job ' || v_job_name || ' for batch ' || rec.batch_id || ' (enabled: TRUE)');
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 (>= ' || v_min_new_batch_id || ') have jobs.');
ELSE
DBMS_OUTPUT.PUT_LINE(v_created_count || ' new batch jobs submitted and enabled for parallel execution. Monitor with DBA_SCHEDULER_JOBS and tablespace_log.');
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error submitting jobs: ' || SQLERRM);
RAISE;
END;
/
================================================================
Individual job and working fine:
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: Insert new tablespace with next batch_id
DECLARE
v_next_batch_id NUMBER;
BEGIN
-- Get the next available batch_id
SELECT NVL(MAX(batch_id), 0) + 1 INTO v_next_batch_id
FROM tablespace_control;
-- Insert new tablespace with next batch_id
INSERT INTO tablespace_control (tablespace_name, initial_size_gb, batch_id)
VALUES ('TS_REVANTH_5', 6268, v_next_batch_id);
COMMIT;
DBMS_OUTPUT.PUT_LINE('Inserted new tablespace with batch_id ' || v_next_batch_id);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error inserting into tablespace_control: ' || SQLERRM);
RAISE;
END;
/
-- Step 3: Create scheduler job only for the new batch_id
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_new_batch_id NUMBER;
v_job_exists NUMBER;
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
-- Get the new batch_id (highest)
SELECT MAX(batch_id) INTO v_new_batch_id
FROM tablespace_control;
IF v_new_batch_id IS NULL THEN
DBMS_OUTPUT.PUT_LINE('No batches found in tablespace_control.');
RETURN;
END IF;
v_job_name := USER || '.CREATE_TS_BATCH_' || v_new_batch_id;
-- Check if job already exists
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 the new 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';
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 = ]' || v_new_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, ]' || v_new_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 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, ]' || v_new_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, ]' || v_new_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, ]' || v_new_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, ]' || v_new_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 ' || v_new_batch_id
);
DBMS_OUTPUT.PUT_LINE('Submitted job ' || v_job_name || ' for batch ' || v_new_batch_id || ' (enabled: TRUE)');
ELSE
DBMS_OUTPUT.PUT_LINE('Job ' || v_job_name || ' already exists, skipping creation');
END IF;
DBMS_OUTPUT.PUT_LINE('New batch job creation complete. Monitor with DBA_SCHEDULER_JOBS and tablespace_log.');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error submitting job: ' || SQLERRM);
END;
/
===============================================================
submits individual job with all batch as individual runs: - working fine
=================================================================
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 individual batch assignments
BEGIN
-- Insert tablespace with unique batch_id for each
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 ('TS_REVANTH_2', 6268, 2);
INSERT INTO tablespace_control (tablespace_name, initial_size_gb, batch_id)
VALUES ('TABLESPACE3', 2500, 3);
-- Add more tablespaces with incrementing batch_id as needed
COMMIT;
DBMS_OUTPUT.PUT_LINE('Populated tablespace_control with individual batch assignments');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error populating tablespace_control: ' || SQLERRM);
RAISE;
END;
/
-- Step 3: Create scheduler jobs for each batch (all enabled for parallel execution)
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_batch_count NUMBER;
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
-- Get number of batches based on MAX(batch_id)
SELECT NVL(MAX(batch_id), 0) INTO v_batch_count
FROM tablespace_control;
IF v_batch_count = 0 THEN
DBMS_OUTPUT.PUT_LINE('No batches found in tablespace_control.');
RETURN;
END IF;
-- 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 || ' (enabled: TRUE)');
END LOOP;
DBMS_OUTPUT.PUT_LINE('All batch jobs submitted and enabled for parallel execution. Monitor with DBA_SCHEDULER_JOBS and tablespace_log.');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error submitting jobs: ' || SQLERRM);
END;
/
BEGIN
DBMS_SCHEDULER.STOP_JOB(UPPER(USER) || '.CREATE_TS_BATCH_6', force => TRUE);
DBMS_SCHEDULER.STOP_JOB(UPPER(USER) || '.CREATE_TS_BATCH_7', force => TRUE);
DBMS_SCHEDULER.STOP_JOB(UPPER(USER) || '.CREATE_TS_BATCH_8', force => TRUE);
DBMS_SCHEDULER.DROP_JOB(UPPER(USER) || '.CREATE_TS_BATCH_6', force => TRUE);
DBMS_SCHEDULER.DROP_JOB(UPPER(USER) || '.CREATE_TS_BATCH_7', force => TRUE);
DBMS_SCHEDULER.DROP_JOB(UPPER(USER) || '.CREATE_TS_BATCH_8', force => TRUE);
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -27475 THEN NULL; -- Job does not exist
ELSE RAISE;
END IF;
END;
/
DELETE FROM tablespace_control WHERE batch_id IN (6, 7, 8);
COMMIT;
SELECT * FROM tablespace_control WHERE batch_id IN (6, 7, 8); -- Should return no rows
No comments:
Post a Comment