9 Steps to Identify and Remove Table Fragmentation

In Oracle schema there are tables which has huge difference in actual size (size from User_segments) and expected size from user_tables (Num_rows*avg_row_length (in bytes)). This all is due to fragmentation in the table or stats for table are not updated into user_tables.

Steps to Check and Remove Fragmentation

1. Gather table states:

To check exact difference in table actual size (user_segments) and stats size (user_tables). The difference between these value will report actual fragmentation to DBA. So, We have to have updated stats on the table stored in user_tables. Check LAST_ANALYZED value for table in user_tables. If this value is recent you can skip this step. Other wise i would suggest to gather table stats to get updated stats.

exec dbms_stats.gather_table_stats('schema_name','table_name');

2. Check for Fragmentation in table:

Below query will show the total size of table with fragmentation, expected without fragmentation and how much % of size we can reclaim after removing table fragmentation. Database Administrator has to provide table_name and schema_name as input to this query.

select owner,table_name,round((blocks*8),2)||'kb' "Fragmented size", round((num_rows*avg_row_len/1024),2)||'kb' "Actual size", round((blocks*8),2)-round((num_rows*avg_row_len/1024),2)||'kb',
((round((blocks*8),2)-round((num_rows*avg_row_len/1024),2))/round((blocks*8),2))*100 -10 "reclaimable space % " from dba_tables where table_name =’ table_Name’ AND OWNER LIKE 'schema_name';

Note: This query fetch data from user_tables, so the accuracy of result depends on user_table stats.

If you find reclaimable space % value more than 20% then we can expect fragmentation in the table. Suppose, DBA find 50% reclaimable space by above query, So he can proceed for removing fragmentation.

Ways to Remove Fragmentation:

There are two ways to remove Table fragmentation

a. Export and import the table:- (difficult to implement in production environment)
b. Move table in to different or same tablespace:- (Depends upon the free space available in the tablespace)

Here I am following 2b option by keeping table availability in mind.

3. Collect status of all the indexes on the table:

We will record Index status at one place, So that we get back them after completion of this exercise, 

 select index_name,status from user_indexes where table_name like 'table_name';

status may be valid or unusable.

4. Move table in to new tablespace:

In this step we will move fragmented table from one tablespace to another tablespace to reclaim fragmented space. Find Current size of you table from user_segments and check if any other tablespace has same free space available. So, that we can move this table to new tablespace. Before this DBA has to enable row movement for table.

alter table table_name enable row movement;
alter table table_name move tablespace new_tablespace_name;

5. Move table in to old tablespace:

Now, get back table to old tablespaces using below command

alter table table_name move tablespace old_tablespace_name;

If we have as free space available as of table size in same tablespace which contain the table. Then we can replace step 4 and 5 by

alter table table_name enable row movement;
alter table table_name move ;

6. Rebuild all indexes:

We need to rebuild all the indexes on the table because of move command all the index goes into unusable state.

select index_name from user_indexes where table_name like 'table_name';

Use this command for each index.

alter index index_name rebuild online;

7. Cross Check Index Status:

select index_name,status from user_indexes where table_name like 'table_name';

Here, value in status field must be valid.

8. Check Table size:

Now again check table size using and DBA will find reduced size of the table.

select table_name,bytes/(1024*1024*1024) from user_table where table_name='table_name';

If, Remote DBA will again execute query in #2, He will find same result because stats of the table is still old. So He has to collect table status. You can also observe same amount of extra free space in current tablespase which was reclaimed after removing table fragmentation.

9. Gather table states:

exec dbms_stats.gather_table_stats('schema_name','table_name');

Please share you feed back in comments section, if this post helps you.


  1. Excellent blοg here! Also your web site lοаԁs up fast!
    Whаt hoѕt aгe yοu using?
    Cаn I get your affilіate lіnk
    tο your host? Ι wiѕh my
    site loadeԁ uр аѕ fаst аs
    уours lol

    Here is my web page - Warehouse Clearance

    1. Friend, I am using Google blogger.com for this blog which is a free blog hosting site. Thanks for you comment.

  2. HI , Can u please let me know .. is fragmentaion removed by Rman cloning.. as RMAN takes backup for only used blocks. Kindly help me to know this in detail. Though i think it wont be helpful to remove fragmentation..

    1. Chetan, What will you do with RMAN for removing a single table fragmentation ?

      Either you can go with above discussed approach or take expdp dump of table and restore it back...

  3. In the query to check reclaimable space%, can you tell me why you're deducting 10 from the actual value ?

  4. Removing Fragmentation in Oracle 11g EXADATA

  5. Hi Friends,

    I have one very silly question for experts please bear with me for that. I would like to know do we explicitly remove fragmentation from tables in oracle 11g EXADATA or it happens automatically ?

    Will appreciate your feedback!

    Deepak Hardasani

    1. Hi Deepak,

      Sorry for late reply. You need to explicitly remove it from Exadata as well.