The scripts below are explicitly designed with Heavy Parallelism using DBMS_SCHEDULER.
Tablespace Drops: All 8 tablespaces are dropped simultaneously (8 concurrent threads).
Object Cleanup: All 7 schemas are scrubbed simultaneously (7 concurrent threads).
Here is your Consolidated, Parallel Destruction Runbook.
Parallel Destruction Runbook
| Step | Script | Action | Parallelism |
| 1 | 01_parallel_init.sql | Config & Safety. Sets up control tables and defines the batches. | N/A |
| 2 | 02_parallel_drop.sql | The Nuke. Launches 8 background jobs to drop tablespaces at the same time. | 8x Threads |
| 3 | 03_wait_blocker.sql | Traffic Cop. Pauses your terminal until all Drop jobs are 100% finished. | N/A |
| 4 | 04_parallel_sweep.sql | The Sweeper. Launches 7 background jobs to clean schemas at the same time. | 7x Threads |
| 5 | 05_monitor.sql | Verification. Checks status and confirms 0 objects remain. | N/A |
SCRIPT 01: Config, Safety & Initialization
Filename: 01_parallel_init.sql
SQL
SET SERVEROUTPUT ON;
DECLARE
-- [SAFETY VALVE] SET TO TRUE TO ENABLE DESTRUCTION
v_i_have_backups BOOLEAN := FALSE;
v_fallback_ts VARCHAR2(30);
BEGIN
DBMS_OUTPUT.PUT_LINE('=== STEP 1: PARALLEL INIT & SAFETY ===');
IF NOT v_i_have_backups THEN
RAISE_APPLICATION_ERROR(-20000, 'STOP! You must edit Script 01 and set v_i_have_backups := TRUE.');
END IF;
-- Detect Fallback TS
BEGIN
SELECT property_value INTO v_fallback_ts
FROM database_properties WHERE property_name = 'DEFAULT_PERMANENT_TABLESPACE';
EXCEPTION WHEN OTHERS THEN v_fallback_ts := 'USERS'; END;
-- Cleanup Old Controls
BEGIN EXECUTE IMMEDIATE 'DROP TABLE tablespace_control PURGE'; EXCEPTION WHEN OTHERS THEN NULL; END;
BEGIN EXECUTE IMMEDIATE 'DROP TABLE tablespace_log PURGE'; EXCEPTION WHEN OTHERS THEN NULL; END;
BEGIN EXECUTE IMMEDIATE 'DROP TABLE tablespace_config PURGE'; EXCEPTION WHEN OTHERS THEN NULL; END;
-- Config & Control Tables
EXECUTE IMMEDIATE 'CREATE TABLE tablespace_config (config_key VARCHAR2(50) PRIMARY KEY, config_value VARCHAR2(500))';
EXECUTE IMMEDIATE 'INSERT INTO tablespace_config VALUES (''FALLBACK_TS'', :1)' USING v_fallback_ts;
EXECUTE IMMEDIATE q'[
CREATE TABLE tablespace_control (
item_name VARCHAR2(50) PRIMARY KEY,
item_type VARCHAR2(20),
batch_id NUMBER,
status VARCHAR2(20) DEFAULT 'PENDING',
error_msg VARCHAR2(4000),
CONSTRAINT chk_safe_items CHECK (
UPPER(item_name) NOT IN ('SYSTEM','SYSAUX','UNDOTBS1','TEMP','USERS')
)
)
]';
EXECUTE IMMEDIATE q'[
CREATE TABLE tablespace_log (
log_id NUMBER GENERATED ALWAYS AS IDENTITY,
log_time TIMESTAMP DEFAULT SYSTIMESTAMP,
item_name VARCHAR2(50),
action VARCHAR2(30),
status VARCHAR2(20),
message VARCHAR2(4000)
)
]';
-- LOAD PARALLEL BATCHES
INSERT ALL
-- Batch A: 8 Tablespaces (Will run on 8 threads)
INTO tablespace_control (item_name, item_type, batch_id) VALUES ('TS_REVANTH_CORE', 'TABLESPACE', 1)
INTO tablespace_control (item_name, item_type, batch_id) VALUES ('TS_REVANTH_INDEX', 'TABLESPACE', 2)
INTO tablespace_control (item_name, item_type, batch_id) VALUES ('TS_REVANTH_AUDIT', 'TABLESPACE', 3)
INTO tablespace_control (item_name, item_type, batch_id) VALUES ('TS_REVANTH_HISTORY', 'TABLESPACE', 4)
INTO tablespace_control (item_name, item_type, batch_id) VALUES ('TS_REVANTH_REPORTING', 'TABLESPACE', 5)
INTO tablespace_control (item_name, item_type, batch_id) VALUES ('TS_REVANTH_ARCHIVE', 'TABLESPACE', 6)
INTO tablespace_control (item_name, item_type, batch_id) VALUES ('TS_REVANTH_LARGE', 'TABLESPACE', 7)
INTO tablespace_control (item_name, item_type, batch_id) VALUES ('TS_REVANTH_RESERVE', 'TABLESPACE', 8)
-- Batch B: 7 Schemas (Will run on 7 threads)
INTO tablespace_control (item_name, item_type, batch_id) VALUES ('SCHEMA_APP1', 'SCHEMA', 101)
INTO tablespace_control (item_name, item_type, batch_id) VALUES ('SCHEMA_APP2', 'SCHEMA', 102)
INTO tablespace_control (item_name, item_type, batch_id) VALUES ('SCHEMA_DATA', 'SCHEMA', 103)
INTO tablespace_control (item_name, item_type, batch_id) VALUES ('SCHEMA_REPORT', 'SCHEMA', 104)
INTO tablespace_control (item_name, item_type, batch_id) VALUES ('SCHEMA_AUDIT', 'SCHEMA', 105)
INTO tablespace_control (item_name, item_type, batch_id) VALUES ('SCHEMA_INTEGRATION', 'SCHEMA', 106)
INTO tablespace_control (item_name, item_type, batch_id) VALUES ('SCHEMA_ARCHIVE', 'SCHEMA', 107)
SELECT * FROM dual;
COMMIT;
-- Prepare Environment (Kill Sessions)
FOR r IN (SELECT item_name FROM tablespace_control WHERE item_type = 'SCHEMA') LOOP
BEGIN
EXECUTE IMMEDIATE 'ALTER USER ' || r.item_name || ' ACCOUNT LOCK';
FOR s IN (SELECT sid, serial# FROM v$session WHERE username = r.item_name) LOOP
EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || s.sid || ',' || s.serial# || ''' IMMEDIATE';
END LOOP;
EXCEPTION WHEN OTHERS THEN NULL; END;
END LOOP;
DBMS_OUTPUT.PUT_LINE('>> Parallel Batches Configured. Ready to Drop.');
END;
/
SCRIPT 02: Launch Parallel Tablespace Drops
Filename: 02_parallel_drop.sql
SQL
SET SERVEROUTPUT ON;
DECLARE
v_fallback_ts VARCHAR2(50);
v_jobs_count NUMBER := 0;
BEGIN
DBMS_OUTPUT.PUT_LINE('=== STEP 2: LAUNCHING PARALLEL DROPS ===');
SELECT config_value INTO v_fallback_ts FROM tablespace_config WHERE config_key = 'FALLBACK_TS';
-- Loop through control table and spawn a job for EACH tablespace immediately
FOR rec IN (SELECT * FROM tablespace_control WHERE item_type = 'TABLESPACE' ORDER BY batch_id) LOOP
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'JOB_DROP_' || rec.batch_id,
job_type => 'PLSQL_BLOCK',
job_action => q'[
DECLARE
v_ts VARCHAR2(50) := ']' || rec.item_name || q'[';
v_safe VARCHAR2(50) := ']' || v_fallback_ts || q'[';
v_cnt NUMBER;
BEGIN
-- 1. Evacuate Users
FOR u IN (SELECT username FROM dba_users WHERE default_tablespace = v_ts) LOOP
EXECUTE IMMEDIATE 'ALTER USER ' || u.username || ' DEFAULT TABLESPACE ' || v_safe;
END LOOP;
-- 2. Drop (The Heavy IO Operation)
SELECT count(*) INTO v_cnt FROM dba_tablespaces WHERE tablespace_name = v_ts;
IF v_cnt > 0 THEN
EXECUTE IMMEDIATE 'DROP TABLESPACE ' || v_ts || ' INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS';
INSERT INTO tablespace_log (item_name, action, status, message) VALUES (v_ts, 'DROP_TS', 'SUCCESS', 'Dropped via Parallel Job');
ELSE
INSERT INTO tablespace_log (item_name, action, status, message) VALUES (v_ts, 'DROP_TS', 'SKIPPED', 'Not Found');
END IF;
UPDATE tablespace_control SET status = 'COMPLETED' WHERE item_name = v_ts;
COMMIT;
EXCEPTION WHEN OTHERS THEN
INSERT INTO tablespace_log (item_name, action, status, message) VALUES (v_ts, 'DROP_TS', 'FAILED', SQLERRM);
UPDATE tablespace_control SET status = 'FAILED', error_msg = SQLERRM WHERE item_name = v_ts;
COMMIT;
END;
]',
enabled => TRUE,
auto_drop => TRUE
);
v_jobs_count := v_jobs_count + 1;
DBMS_OUTPUT.PUT_LINE('>> Spawned Thread #' || v_jobs_count || ' for: ' || rec.item_name);
END LOOP;
DBMS_OUTPUT.PUT_LINE('>> All ' || v_jobs_count || ' threads running. Proceed to Wait Script.');
END;
/
SCRIPT 03: The Blocker (Wait for Completion)
Filename: 03_wait_blocker.sql
SQL
SET SERVEROUTPUT ON;
DECLARE
v_active_jobs NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE('=== WAITING FOR PARALLEL JOBS TO FINISH ===');
LOOP
SELECT COUNT(*) INTO v_active_jobs
FROM dba_scheduler_jobs
WHERE job_name LIKE 'JOB_%';
IF v_active_jobs = 0 THEN
EXIT;
END IF;
DBMS_OUTPUT.PUT_LINE('... ' || v_active_jobs || ' threads still active ...');
-- Sleep 10s (Universal compatible approach)
BEGIN
EXECUTE IMMEDIATE 'BEGIN DBMS_SESSION.SLEEP(10); END;';
EXCEPTION WHEN OTHERS THEN
NULL; -- Busy wait for older versions
END;
END LOOP;
DBMS_OUTPUT.PUT_LINE('>> ALL THREADS FINISHED.');
END;
/
SCRIPT 04: Launch Parallel Object Sweepers
Filename: 04_parallel_sweep.sql
SQL
SET SERVEROUTPUT ON;
DECLARE
v_jobs_count NUMBER := 0;
BEGIN
DBMS_OUTPUT.PUT_LINE('=== STEP 4: LAUNCHING PARALLEL SWEEPERS ===');
FOR rec IN (SELECT * FROM tablespace_control WHERE item_type = 'SCHEMA') LOOP
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'JOB_SWEEP_' || rec.batch_id,
job_type => 'PLSQL_BLOCK',
job_action => q'[
DECLARE
v_owner VARCHAR2(50) := ']' || rec.item_name || q'[';
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET RECYCLEBIN = OFF';
-- A. Parallel Schema Cleanup
FOR obj IN (
SELECT object_name, object_type FROM dba_objects WHERE owner = v_owner
AND object_type NOT LIKE 'SYSTEM%' AND object_type NOT LIKE 'LOB%' AND object_name NOT LIKE 'BIN$%'
ORDER BY CASE object_type
WHEN 'SYNONYM' THEN 1 WHEN 'VIEW' THEN 2 WHEN 'SEQUENCE' THEN 3
WHEN 'PROCEDURE' THEN 4 WHEN 'PACKAGE' THEN 5 WHEN 'TABLE' THEN 6
ELSE 7 END ASC
) LOOP
BEGIN
IF obj.object_type = 'TABLE' THEN
EXECUTE IMMEDIATE 'DROP TABLE "'||v_owner||'"."'||obj.object_name||'" CASCADE CONSTRAINTS PURGE';
ELSE
EXECUTE IMMEDIATE 'DROP '||obj.object_type||' "'||v_owner||'"."'||obj.object_name||'"' ||
CASE WHEN obj.object_type = 'TYPE' THEN ' FORCE' ELSE '' END;
END IF;
EXCEPTION WHEN OTHERS THEN NULL; END;
END LOOP;
-- B. Parallel Public Synonym Cleanup
FOR p IN (SELECT synonym_name FROM dba_synonyms WHERE owner = 'PUBLIC' AND table_owner = v_owner) LOOP
BEGIN
EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM "' || p.synonym_name || '"';
EXCEPTION WHEN OTHERS THEN NULL; END;
END LOOP;
INSERT INTO tablespace_log (item_name, action, status, message) VALUES (v_owner, 'SWEEP', 'SUCCESS', 'Cleaned via Parallel Job');
UPDATE tablespace_control SET status = 'COMPLETED' WHERE item_name = v_owner;
COMMIT;
END;
]',
enabled => TRUE,
auto_drop => TRUE
);
v_jobs_count := v_jobs_count + 1;
DBMS_OUTPUT.PUT_LINE('>> Spawned Thread #' || v_jobs_count || ' for: ' || rec.item_name);
END LOOP;
END;
/
SCRIPT 05: Status Monitor
Filename: 05_monitor.sql
SQL
SET LINESIZE 200 PAGESIZE 100;
COL item_name FORMAT A30
COL action FORMAT A15
COL status FORMAT A15
COL message FORMAT A60
COL log_time FORMAT A20
PROMPT === FINAL JOB STATUS ===
SELECT job_name, state, run_duration FROM dba_scheduler_jobs WHERE job_name LIKE 'JOB_%';
PROMPT === CONTROL STATUS ===
SELECT item_name, item_type, status, error_msg FROM tablespace_control ORDER BY batch_id;
PROMPT === OBJECT COUNT (Target: 0) ===
SELECT owner, count(*) as remaining_objects
FROM dba_objects
WHERE owner IN (SELECT item_name FROM tablespace_control WHERE item_type = 'SCHEMA')
GROUP BY owner;
No comments:
Post a Comment