JAVA J2EE 4 U

 Below is a standalone SQL*Plus script that runs all fragmentation queries together, spooling each result into a properly formatted CSV file. This script can be executed directly in SQLPlus or any terminal that supports SQLPlus input.

---

📄 Filename: fragmentation_analysis.sql

-- Fragmentation Analysis SQL Script

-- Spools multiple CSV reports from Oracle DBA views

SET LINESIZE 1000

SET PAGESIZE 0

SET FEEDBACK OFF

SET VERIFY OFF

SET ECHO OFF

SET TRIMSPOOL ON

SET TERMOUT OFF

SET COLSEP ','

-- Change to desired directory (optional)

-- DEFINE OUT_DIR='/tmp'

-- 1. Table-Level Fragmentation

SPOOL table_fragmentation.csv

SELECT 

    OWNER, 

    TABLE_NAME, 

    NUM_ROWS, 

    BLOCKS, 

    EMPTY_BLOCKS, 

    AVG_SPACE, 

    CHAIN_CNT, 

    ROUND((BLOCKS - EMPTY_BLOCKS) * 100 / BLOCKS, 2) AS USED_BLOCK_PCT

FROM 

    DBA_TABLES

WHERE 

    OWNER NOT IN ('SYS', 'SYSTEM')

    AND BLOCKS > 0

ORDER BY 

    USED_BLOCK_PCT ASC;

SPOOL OFF

-- 2. Tablespace Fragmentation Summary

SPOOL tablespace_fragmentation.csv

SELECT 

    df.TABLESPACE_NAME,

    df.TOTAL_MB,

    fs.FREE_MB,

    ROUND((fs.FREE_MB/df.TOTAL_MB)*100, 2) AS FREE_PCT,

    ROUND(((df.TOTAL_MB - fs.FREE_MB)/df.TOTAL_MB)*100, 2) AS USED_PCT

FROM

    (SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 AS TOTAL_MB FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) df,

    (SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 AS FREE_MB FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) fs

WHERE 

    df.TABLESPACE_NAME = fs.TABLESPACE_NAME

ORDER BY 

    USED_PCT DESC;

SPOOL OFF

-- 3. LOB Fragmentation Details

SPOOL lob_fragmentation.csv

SELECT 

    OWNER,

    TABLE_NAME,

    COLUMN_NAME,

    SEGMENT_NAME,

    SECUREFILE,

    IN_ROW,

    CHUNK,

    RETENTION,

    DEDUPLICATE,

    COMPRESSION

FROM 

    DBA_LOBS

WHERE 

    SECUREFILE = 'NO'

    OR COMPRESSION = 'NONE'

ORDER BY 

    OWNER, TABLE_NAME;

SPOOL OFF

-- 4. Free Space Fragmentation (by Extents)

SPOOL free_space_frag_detail.csv

SELECT 

    TABLESPACE_NAME,

    COUNT(*) AS EXTENT_COUNT,

    MAX(BYTES)/1024/1024 AS MAX_EXTENT_MB,

    MIN(BYTES)/1024/1024 AS MIN_EXTENT_MB,

    ROUND(AVG(BYTES)/1024/1024, 2) AS AVG_EXTENT_MB

FROM 

    DBA_FREE_SPACE

GROUP BY 

    TABLESPACE_NAME

ORDER BY 

    AVG_EXTENT_MB;

SPOOL OFF

-- Done

PROMPT All fragmentation CSVs are generated successfully.

EXIT;

---

✅ How to Run

sqlplus -s sys/password@ORCL as sysdba @fragmentation_analysis.sql

All output files will be created in the current working directory unless a directory is defined using SPOOL /path/to/dir/file.csv.

---

📂 Output Files

table_fragmentation.csv

tablespace_fragmentation.csv

lob_fragmentation.csv

free_space_frag_detail.csv

---

Let me know if you'd like to:

Combine all outputs into a single CSV file

Add a timestamp to each file

Schedule this via DBMS_SCHEDULER inside the database