AWR Analysis

SET LINES 260
SET PAGES 200
COLUMN sql_profile FORMAT A45
COLUMN sql_plan_baseline FORMAT A45
COLUMN sql_patch FORMAT A45
COLUMN module FORMAT A35
SELECT inst_id,
       sql_id,
       child_number,
       plan_hash_value,
       sql_profile,
       sql_plan_baseline,
       sql_patch,
       module,
       parsing_schema_name,
       executions,
       last_active_time
FROM   gv$sql
WHERE  sql_id = '7r1gn0tpbwjjj'

ORDER BY last_active_time DESC;

DEL

batch

Drop

 

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;

OLTP Advanced Compression Diff

Quick copy-paste ready blocks for your script SQL

text

-- Modern recommended version (2025–2026 standard)
v_common_clause CONSTANT VARCHAR2(500) := 
    'AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING DEFAULT NOLOGGING '
 || 'NO INMEMORY ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE '
 || 'BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON '
 || 'ROW STORE COMPRESS ADVANCED';

SQL

text

-- Slightly shorter + old-school compatible version
v_common_clause CONSTANT VARCHAR2(400) := 
    'AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED NOLOGGING ONLINE '
 || 'EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO '
 || 'COMPRESS FOR OLTP';

what does this bothdo?

Both versions do almost exactly the same thing — they define the storage and compression options that will be applied when your script creates new bigfile tablespaces.
The only real difference is syntax style and how modern/future-proof the command is.
Here’s a clear comparison:

































































Aspect
Modern version (ROW STORE COMPRESS ADVANCED)
Old-school version (COMPRESS FOR OLTP)
Which one is better in 2026?
Official Oracle syntax
Current / recommended (since ~12.1, strongly preferred in 19c/21c/23ai)
Old syntax (still 100% working)
Modern
Compression type
Advanced Row Compression (aka OLTP compression)
Advanced Row Compression (same thing)
Same functionality
License required
Advanced Compression Option
Advanced Compression Option
Same
Typical compression ratio
2× – 4× (sometimes more)
2× – 4× (exactly the same)
Same
DML overhead (insert/update)
Low to moderate
Low to moderate
Same
Future-proof
Yes — this is the syntax Oracle pushes now
Still supported, but considered legacy
Modern wins
Readability / clarity
Very clear what kind of compression it is
Less explicit (OLTP sounds like only for OLTP)
Modern wins
Length
Longer
Shorter
Old-school wins
Risk of deprecation
Very low
Low (but possible in far future)
Modern wins

Summary – What each one actually does
Both create tablespaces with these properties:

Bigfile tablespace
Starts with 1 GB autoextend chunks
Can grow without limit (MAXSIZE UNLIMITED)
Uses advanced row compression (good balance between compression + acceptable DML performance)
Local extent management + autoallocate (Oracle manages extent sizes automatically)
8 KB block size
Automatic segment space management (ASSM)
Flashback enabled
Online creation
No In-Memory column store
Logging / Nologging setting (you have different preferences in each version)

Recommendation for January 2026

SQL
-- ★ This is the best choice right now ★
v_common_clause CONSTANT VARCHAR2(500) := 
    'AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED '
 || 'NOLOGGING ONLINE '                           -- ← faster creation, common choice
 || 'NO INMEMORY '
 || 'EXTENT MANAGEMENT LOCAL AUTOALLOCATE '
 || 'BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO '
 || 'FLASHBACK ON '
 || 'ROW STORE COMPRESS ADVANCED';

Why this one?

Uses current Oracle-recommended syntax
Very clear what compression you're getting
Least chance of any future surprise/warning
Everyone who reads your code in 2026–2030 will immediately understand it

The old COMPRESS FOR OLTP still works perfectly fine today — but it's like still writing VARCHAR2 instead of VARCHAR — it works, but it's not the modern way anymore.
Pick whichever style your team is more comfortable with — functionally they are identical.



Here is a bullet-proof, modern, production-grade version of the tablespace creation clause (2025–2026 best practices).

Recommended final version – most conservative & safe

SQL
v_common_clause CONSTANT VARCHAR2(400) := 
    'AUTOEXTEND ON NEXT 1024M MAXSIZE UNLIMITED '
 || 'NOLOGGING '
 || 'ONLINE '
 || 'NO INMEMORY '
 || 'EXTENT MANAGEMENT LOCAL AUTOALLOCATE '
 || 'BLOCKSIZE 8192 '
 || 'SEGMENT SPACE MANAGEMENT AUTO '
 || 'FLASHBACK ON '
 || 'ROW STORE COMPRESS ADVANCED';

Why these exact settings are considered "bullet-proof" today








































































Setting
Value used
Why this is safer / better
Risk if changed / omitted
AUTOEXTEND NEXT
1024M (instead of 1G)
1G = 1024M, but written as 1024M is more explicit
Very low
MAXSIZE
UNLIMITED
Standard for bigfile tablespaces
Almost none
NOLOGGING
Yes
Much faster initial creation (especially 6 TB+)
Redo generation during creation
ONLINE
Yes
Tablespace is usable immediately
Required anyway
NO INMEMORY
Explicit
Prevents accidental In-Memory population
Very useful in mixed environments
EXTENT MANAGEMENT
LOCAL AUTOALLOCATE
Most flexible, Oracle manages extent sizes best
Very safe choice
BLOCKSIZE
8192 (instead of 8K)
More explicit, zero chance of confusion
Practically none
SEGMENT SPACE MANAGEMENT
AUTO
ASSM – the only reasonable choice since ~11g
None
FLASHBACK ON
Yes
Allows flashback operations on the tablespace
Important for recovery scenarios
ROW STORE COMPRESS ADVANCED
Yes
Best balance compression vs DML performance
Requires Advanced Compression license

Even more conservative / extra-safe version (if you want maximum clarity)

SQL
v_common_clause CONSTANT VARCHAR2(500) := 
    'DATAFILE SIZE 10G '                                -- ← explicit initial size (recommended)
 || 'AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED '
 || 'NOLOGGING '
 || 'ONLINE '
 || 'NO INMEMORY '
 || 'EXTENT MANAGEMENT LOCAL '
 || 'AUTOALLOCATE '
 || 'BLOCKSIZE 8192 '
 || 'SEGMENT SPACE MANAGEMENT AUTO '
 || 'FLASHBACK ON '
 || 'ROW STORE COMPRESS ADVANCED';

Quick checklist before you go live with this

text
☑ Advanced Compression license is enabled (DBA_FEATURE_USAGE_STATISTICS)
☑ You are creating BIGFILE tablespace
☑ Your database is >= 12.1 (preferably 19c+)
☑ You are okay with NOLOGGING during initial creation
☑ Your storage can handle ~6TB+ per tablespace (since initial_size_gb = 6268)
☑ You tested insert/update performance with compression in your environment
☑ You have monitoring for tablespace usage (especially autoextend events)

Most bullet-proof pattern used in large environments (2025–2026)

SQL
-- The one most careful DBAs are actually using right now
v_common_clause CONSTANT VARCHAR2(512) := 
    'BIGFILE '                                          -- explicit
 || 'DATAFILE SIZE 10G '                                -- safe starting size
 || 'AUTOEXTEND ON NEXT 1024M MAXSIZE UNLIMITED '
 || 'NOLOGGING ONLINE '
 || 'NO INMEMORY '
 || 'EXTENT MANAGEMENT LOCAL AUTOALLOCATE '
 || 'BLOCKSIZE 8192 '
 || 'SEGMENT SPACE MANAGEMENT AUTO '
 || 'FLASHBACK ON '
 || 'ROW STORE COMPRESS ADVANCED';

Pick whichever version feels most comfortable to you and your team — all of them are very safe when used with BIGFILE.

Good luck Revanth — may your tablespaces be fast, compressed, and never run out of space! 🚀