Script to find fragmentation in Oracle tablespaces

By issuing following script you will extract fragmentation in tablespaces cum TABLESPACE NAME, FREE CHUNKS & LARGEST CHUNK of that tablespace:

Syntax:

 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;

***********************************************************************

Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.

29 thoughts on “Script to find fragmentation in Oracle tablespaces”

    • Thank you manisha for your reply.
      Script perfectly working in 11g, Which database version/edition do you use? Show me error log…

  1. Can you explain me FREE_CHUNKS and LARGEST_CHUNKS, FRAGMENTATION_INDEX. Actually what it refers to?

    • Thank you Venkatesh for writing.
      FREE_CHUNKS: Free extents available
      LARGEST_CHUNK: Largest extent used
      FRAGMENTATION_INDEX: Amount of free space available. 100-means no fragmentation.

      • Thank You, for my tablespace i see some of the tablespaces having FRAGMENTATION_INDEX to 1.13, Means is there high fragmentation and other tablespace is having 4.45. How can i proceed.

        • Venkatesh,
          Thank you fro writing!

          YES, there is high 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.

          • Really Thanks a lot for extremely faster reply as well precautions regarding undo, system as well as sysaux. In our database there are two tablespaces data and index both having FRAGMENTATION_INDEX value as 1.13 and 4.45 respectively. So i’m going to proceed to remove fragmentation in the tablespaces.

            Once again thanks a lot for the reply.

  2. How long will it take to de-fragment of a tablespace of size 4.5 TB with COALESCE or is there any better solution.
    Please let me know because my tablespace is badly fragmented.

    Thanks and regards,
    Bhaskar

    • Thanks Bhaskar for writing!!

      Surely, It will take long time to de-fragment the tablespace with size 4.5 TB.
      We can’t calculate the estimate time of mentioned activity but still its depend on your de-fragmentation method you are using.
      Instead of COALESCE on tablespace level you can go with table level, So your activity shall be divided into small chunks.
      You can also consider table level SHRINKING. [ alter table Test_Table shrink space; ]

  3. Hi Im Toño, I have a question,
    If the number in FRAGMENTATION_INDEX is small, I have to consider that the tablespace is in fragmentatio?

  4. Hi friends,

    could you please help in understanding how much is the free space

    TABLESPACE_NAME FREE_CHUNKS LARGEST_CHUNK FRAGMENTATION_INDEX
    —————————— ———– ————- ——————-
    ABDCSD01 4918 13092.86 7.56
    MSPNDF01 1877 43.01 .61

    will come out of above data

  5. Hi,

    In my case , i ahve output below, could you pl. explain if USERS using large fragmentation ..Is it need defrag..If so let me know steps to do it. Thanks
    ===============
    TABLESPACE_NAME FREE_CHUNKS LARGEST_CHUNK FRAGMENTATION_INDEX
    —————————— ———– ————- ——————-
    USERS 69511 4063.23 .86
    SYSAUX 735 183.3 14.36
    UNDOTBS2 111 2593.79 16.23
    UNDOTBS1 100 4063.23 14.02
    UNDOTBS3 83 819.2 15.79
    UXSTAT 59 237.57 11.81
    UXCONF 4 3649.54 61.47
    SYSTEM 3 562.18 57.51
    UXTEMP 2 4063.23 74.03
    UNDOTBS4 1 202.5 100

    10 rows selected.

  6. Hi Jethwa, your script is very helpeful, but I have a question: How to de-fragment the tablespace? I need to de-fragment the tables? How to do that?

  7. TABLESPACE_NAME FREE_CHUNKS LARGEST_CHUNK FRAGMENTATION_INDEX
    —————————— ———– ————- ——————-
    DATAMART 4879 660.48 2.92
    DYNAMIC_CONTENTS 1627 8.19 1.7
    CAMPAIGN 1505 143.36 3.4
    SANDBOX 630 458.75 3.51
    SYSTEM 221 7542.01 12.47
    UNDOTBS01 76 4063.23 11.92
    SYSAUX 49 94.14 35
    EPIPHANY 6 2259.97 31.96
    COGNOS 2 70.66 84.05
    PLATFORM 2 56.32 83.95
    NEW_BEST_ACTION 1 101.38 100

    So for me I need to defragment DATAMART,DYANMIC_CONTETNS,CAMPAIGN and SANDBOX since they are below 30%?

    When I issue

    alter tablespace CAMPAIGN shrink space;

    I get

    ORA-12916: cannot shrink permanent or dictionary managed tablespace

    Why?

    • Thank you Manan for writing!
      You are trying to shrink a permanent tablespace or a dictionary managed tablespace. You can check the tablespace type with below mentioned SQL and issue the statement only on locally managed temporary tablespaces.
      SQL> select tablespace_name, contents, EXTENT_MANAGEMENT from dba_tablespaces;

  8. Hi Jignesh,
    output of the query is as follows,will you told how to proceed further i.e. which tablespace I have to defrag

    TABLESPACE_NAME FREE_CHUNKS LARGEST_CHUNK FRAGMENTATION_INDEX
    —————————— ———– ————- ——————-
    UNDOTBS1 13 313.22 51.75
    SYSTEM 2 7.17 81.24
    SYSAUX 1 29.76 100
    USERS 1 3.78 100
    USERS_TEST 1 4.1 100

    Confused to proceed further,waiting for your reply.

  9. TABLESPACE_NAME FREE_CHUNKS LARGEST_CHUNK FRAGMENTATION_INDEX
    —————————— ———– ————- ——————-
    HISTORY 819 4063.23 1.26
    UNDOTBS1 753 122.88 3.28
    IDX_HISTORY 225 2216.96 15.35
    SYSAUX 22 1249.28 43.84
    SYSTEM 10 1129.47 55.89
    MASTER 3 2358.27 56.66
    IDX_MASTER 2 1522.69 84.07
    IDX_PARAM 2 2914.3 84.08
    IDX_TXN 2 1027.07 84.08
    MBMASTER 2 92.16 84.03

    Can u help me to de-frag

    CLOB data type has been used

  10. Hi jignesh,
    here is my output
    TABLESPACE_NAME=TRANSACTION.
    FREE_CHUNKS=165
    LARGEST_CHUNK=4063.23
    FRAGMENTATION_INDEX=4.1
    Means is there high fragmentation ?

    Thanks

  11. I got the below result, What is the solution ?

    TABLESPACE_NAME FREE_CHUNKS LARGEST_CHUNK FRAGMENTATION_INDEX
    —————————— ———– ————- ——————-
    PD910I 2657 1817.6 11.29
    PD910T 2076 922.62 8.29
    SYSAUX 456 541.5 6.42
    PRODCTLT 221 764.93 25.28
    MIGRATEVS 126 531.46 28.98
    UNDOTBS2 19 3984.38 40.02
    UNDOTBS1 13 1559.55 51.75

Leave a Reply to Jignesh JethwaCancel reply