Oracle – Checking Fragmentation in Database

Share via:

Post Views: 0

Fragmented_Tables_List in HTML FORMAT :

col spoolname new_value spoolname
select ‘Fragmented_tables’||to_char(sysdate, ‘YYYYMONDD’)||’.html’ spoolname from dual;
spool ‘&spoolname’
set pagesize 200
set markup html on
WITH STAT_SPACE AS(
SELECT OWNER, TABLE_NAME, NUM_ROWS, AVG_ROW_LEN, NUM_ROWS*AVG_ROW_LEN/1024/1024/1024 tbl_STAT_SPACE
FROM DBA_TABLES
WHERE OWNER=’SCOTT’),
SEG_SPACE AS (
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BLOCKS, BYTES/1024/1024/1024 SEGSIZE_GB
FROM DBA_SEGMENTS
WHERE 1=1
AND OWNER=’SCOTT’
AND SEGMENT_TYPE=’TABLE’)
SELECT sesp.tablespace_name, StSp.TABLE_NAME, sesp.blocks, StSp.NUM_ROWS, StSp.AVG_ROW_LEN, StSp.TBL_STAT_SPACE,
sesp.SEGSIZE_GB, abs(STSP.TBL_STAT_SPACE – sesp.SEGSIZE_GB) claim_gb
FROM STAT_SPACE STSP, SEG_SPACE SESP
WHERE abs(STSP.TBL_STAT_SPACE – sesp.SEGSIZE_GB) > 1
AND STSP.TABLE_NAME = SESP.SEGMENT_NAME
AND STSP.OWNER = SESP.OWNER
ORDER BY CLAIM_GB desc;
spool off
exit

Fragmented_Tables_List in SQLPLUS For Specific Schema :

BREAK ON REPORT
BREAK ON TABLESPACE_NAME SKIP 1 ON REPORT
COMPUTE SUM LABEL TOTAL OF SEGSIZE_GB TBL_STAT_SPACE CLAIM_GB ON tablespace_name
COL TABLESPACE_NAME FORMAT A30 HEADING “Tablespace Name”
COL TABLE_NAME FORMAT A30 HEADING “Table Name”
COL BLOCKS FORMAT 999,999,999,999 HEADING “Alloc|DB|Blocks”
COL AVG_ROW_LEN FORMAT 9999 HEADING “Avg|Row|Length”
COL NUM_ROWS FORMAT 999,999,999,999 HEADING “Num Rows”
COL TBL_STAT_SPACE FORMAT 99,999.9999 HEADING “Space Calc|Statistics|in GB”
COL SEGSIZE_GB FORMAT 99,999.9999 HEADING “Space Calc|Allocated|in GB”
col claim_gb format 99,999.9999 heading “Claimable|Space|in GB”
set pagesize 300

WITH STAT_SPACE AS(
SELECT OWNER, TABLE_NAME, NUM_ROWS, AVG_ROW_LEN, NUM_ROWS*AVG_ROW_LEN/1024/1024/1024 tbl_STAT_SPACE
FROM DBA_TABLES
WHERE OWNER=’&OWNER’),
SEG_SPACE AS (
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BLOCKS, BYTES/1024/1024/1024 SEGSIZE_GB
FROM DBA_SEGMENTS
WHERE 1=1
AND OWNER=’&OWNER’
AND SEGMENT_TYPE=’TABLE’)
SELECT sesp.tablespace_name, StSp.TABLE_NAME, sesp.blocks, StSp.NUM_ROWS, StSp.AVG_ROW_LEN, StSp.TBL_STAT_SPACE,
sesp.SEGSIZE_GB, abs(STSP.TBL_STAT_SPACE – sesp.SEGSIZE_GB) claim_gb
FROM STAT_SPACE STSP, SEG_SPACE SESP
WHERE abs(STSP.TBL_STAT_SPACE – sesp.SEGSIZE_GB) > 1
AND STSP.TABLE_NAME = SESP.SEGMENT_NAME
AND STSP.OWNER = SESP.OWNER
ORDER BY TABLESPACE_NAME asc, CLAIM_GB desc;

Fragmented_Tables_List for complete DB:

set pagesize 300
set linesize 300

WITH STAT_SPACE AS(
SELECT OWNER, TABLE_NAME, NUM_ROWS, AVG_ROW_LEN, NUM_ROWS*AVG_ROW_LEN/1024/1024/1024 tbl_STAT_SPACE
FROM DBA_TABLES
),
SEG_SPACE AS (
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BLOCKS, BYTES/1024/1024/1024 SEGSIZE_GB
FROM DBA_SEGMENTS
WHERE 1=1
AND SEGMENT_TYPE=’TABLE’)
SELECT sesp.tablespace_name, StSp.TABLE_NAME, sesp.blocks, StSp.NUM_ROWS, StSp.AVG_ROW_LEN,
StSp.TBL_STAT_SPACE, sesp.SEGSIZE_GB, abs(STSP.TBL_STAT_SPACE – sesp.SEGSIZE_GB) claim_gb
FROM STAT_SPACE STSP, SEG_SPACE SESP
WHERE abs(STSP.TBL_STAT_SPACE – sesp.SEGSIZE_GB) > 1
AND STSP.TABLE_NAME = SESP.SEGMENT_NAME
AND STSP.OWNER = SESP.OWNER
ORDER BY TABLESPACE_NAME asc, CLAIM_GB desc;

Checking Fragmentation for Particular Table :

WITH STAT_SPACE AS(
SELECT OWNER, TABLE_NAME, NUM_ROWS, AVG_ROW_LEN, NUM_ROWS*AVG_ROW_LEN/1024/1024/1024 tbl_STAT_SPACE
FROM DBA_TABLES
),
SEG_SPACE AS (
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BLOCKS, BYTES/1024/1024/1024 SEGSIZE_GB
FROM DBA_SEGMENTS
WHERE 1=1
AND SEGMENT_TYPE=’TABLE’)
SELECT sesp.tablespace_name, StSp.TABLE_NAME, sesp.blocks, StSp.NUM_ROWS, StSp.AVG_ROW_LEN,
StSp.TBL_STAT_SPACE, sesp.SEGSIZE_GB, abs(STSP.TBL_STAT_SPACE – sesp.SEGSIZE_GB) claim_gb
FROM STAT_SPACE STSP, SEG_SPACE SESP
WHERE abs(STSP.TBL_STAT_SPACE – sesp.SEGSIZE_GB) > 0
AND STSP.TABLE_NAME = SESP.SEGMENT_NAME
AND STSP.OWNER = SESP.OWNER
AND STSP.TABLE_NAME=’EMP’
ORDER BY TABLESPACE_NAME asc, CLAIM_GB desc;

TABLESPACE Fragmentation :

—- Below 30% is considered to be highly fragmented, please de-fragment it.
—- 100% means no fragmentation
—- If that tablespace belongs to undo tablespace then drop and recreate it.
—- In case of sysaux OR system add new datafiles.
—- In case of user tablespaces, take precaution to de-fragment it.
—- Before Deframenting discuss with Team and take proper action
set linesize 300
set pagesize 300
SELECT
tablespace_name,
count(*) free_chunks,
decode(round((max(bytes) / 1024000),2),
null,0,
round((max(bytes) / 1024000),2)) largest_chunk,
nvl(round(sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)) )),2),0) fragmentation_index
FROM
sys.dba_free_space
group by
tablespace_name
order by 2 desc, 1;

BLOCKS SIZE :

set linesize 300
select table_name,round((blocks*8),2)||’ KB’ “Blocks Size”,(round((blocks*8),2))/1024/1024 ||’ GB’ “Blocks Size”
from dba_tables
where owner=’&OWNER’ and table_name = upper(‘&table_name’);

ROWS SIZE :

set linesize 300
select table_name,round((num_rows*avg_row_len/1024),2)||’ KB’ “Rows Size”,(round((num_rows*avg_row_len/1024),2))/1024/1024||’ GB’ “Rows Size”
from dba_tables
where owner=’&OWNER’ and table_name = upper(‘&table_name’);

Share via:

Note: Please test scripts in Non Prod before trying in Production.