To analyze fragmentation in an Oracle database and output results in CSV format, you can use the following SQL queries. These target different types of fragmentation: table-level, tablespace-level, and LOBs. After each query, I’ve included a method to spool the output in proper CSV formatting.
---
🔸 1. Table-Level Fragmentation (Row Chaining / Space Wastage)
SET LINESIZE 1000
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF
SET TERMOUT OFF
SET ECHO OFF
SET COLSEP ','
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 (Free Space vs Used)
SET LINESIZE 1000
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF
SET TERMOUT OFF
SET ECHO OFF
SET COLSEP ','
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 (Identify non-secure/uncompressed LOBs)
SET LINESIZE 1000
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF
SET TERMOUT OFF
SET ECHO OFF
SET COLSEP ','
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 Detail (Fragmented Extents)
SET LINESIZE 1000
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF
SET TERMOUT OFF
SET ECHO OFF
SET COLSEP ','
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
---
✅ Notes for Usage:
Execute these queries using SQL*Plus or any CLI that supports SPOOL.
You can change SPOOL <filename.csv> to match your target directory.
Run as a DBA user or one with access to DBA_* views.
---
Would you like me to convert this into a shell script or SQL script that runs all together and bundles the output?
0 comments:
Post a Comment