The Oracle Table Fragmentation Script is a powerful tool to analyze and address fragmentation issues in your database tables. This guide will explain the script and show you how to use it to maintain optimal performance.
Table of Contents
Compute statistics for the table:
Before running your fragmentation query, run the following to gather statistics for the table.
ANALYZE TABLE SCOTT.EMPLOYEE COMPUTE STATISTICS;
Table Fragmentation in Oracle query:
Here’s the Oracle Table Fragmentation Script that helps you identify fragmentation issues:
col table_name for a20
col total_size for a15
col actual_size for a15
set lines 1000
select table_name,avg_row_len,round(((blocks*16/1024)),2)||'MB' "TOTAL_SIZE", round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",
round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE", (round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2)/round(((blocks*16/1024)),2))*100 "percentage" from all_tables WHERE table_name='&TABLE_NAME';
This SQL script checks the fragmentation of a specific table in the Oracle database. It calculates the total size, actual size, fragmented space, and oracle table fragmentation percentage, providing an overview of how much space is wasted.
Sample Output:
This output shows that the EMPLOYEE table is 100% fragmented, which means you could optimize it for better performance.
Analyzing Results from the Fragmentation Script:
avg_row_len: The average length of a row in the specified table.
Total Size: Total space allocated to the table.
Actual Size: Actual data size of the table.
Fragmented Space: How much space is wasted due to fragmentation.
Fragmentation Percentage: Ratio of fragmented space to total size.
For further information, consider visiting the following resources: Oracle Database Performance Tuning Guide
If you’re interested in more Oracle optimization tips, be sure to read our post on Oracle session details.
