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.
thsi script is not working
Thank you manisha for your reply.
Script perfectly working in 11g, Which database version/edition do you use? Show me error log…
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.
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; ]
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?
YES!
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
Mentioned script will show you the fragmentation available in your tablespace. Not free space.
Tablespace ABDCSD01 having 7.56% fragmented and MSPNDF01 having .61%
Below 30% is considered to be highly fragmented, please de-fragment it.
Dear sir,
Please explain what is fragmentation?
Dear Venu,
Thank you for writing. I hope this link will help you to understand everything
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.
Yes.
Below 30% is considered to be highly fragmented, please de-fragment it ASAP.
Thanks ..Could you let me know the steps for de-fragemenatation please
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?
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;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.
Thank you Vijay for writing.
Below 30% is considered to be highly fragmented, in your case there is no problem.
Stay Tune. 🙂
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
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
Yes Rajesh, you are right.
after performing defragmentaion process the FRAGMENTATION_INDEX=2.94
i m not getting what is this
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