This post intends to provide an SQL script to find tables that are fragmented (i.e. Data is much lower than High Water Mark) so that we can target those segments (tables) for recreation.
pre-requisites
Requires dba privileges as the script is to be run as the owner SYS or SYSTEM. It Will not work on Compressed tables, and may return negative numbers. Do an Analyze with compute statistics on all tables present in the Users schema.
The SQL script
The script is as shown below:
REM This is an example SQL*Plus Script to find tables fragmentated below high water mark
set heading off verify off echo off
REM The below queries gives information about the size of the table with respect to the high water mark
REM note that BLOCKS*8192 is BLOCKS times the block size: 8192. Substitute your DB blocksize.
PROMPT Please enter the schema name
SELECT TABLE_NAME,BLOCKS*8192/1024/1024 MB FROM DBA_TABLES WHERE owner='&OWNER';
REM The below queries gives the actual size in MB used by the table in terms of data.
REM You can use the difference of the two sql statements specified above to get the table which
REM has fragmentation below high water mark prompt Enter name(s) of the schema for which you want to find the fragmented object.
PROMPT Please enter the schema name
SELECT TABLE_NAME , (BLOCKS *8192 / 1024/1024 ) - (NUM_ROWS*AVG_ROW_LEN/1024/1024)
"Data lower than HWM in MB" FROM DBA_TABLES WHERE UPPER(owner) =UPPER('&OWNER') order by 2 desc;
How to run the script
1. Connect as user SYS or SYSTEM.
2. Run Analyze on all the tables present in the schema for which you want to find the fragmented table.
SQL> Analyze table [table_name] compute statistics;
3. Execute the script, the script will prompt for Schema name.
Note: You must Run the two statements one by one to be able to supply the substitution variable for the owner.