Detect Tablespace Fragmentation

[peterx]

hi Tim,

Got a question about Tablespace Fragmentation,

I found a doc Script to Detect Tablespace Fragmentation [ID 1020182.6]
https://support.oracle.com/epmos/faces/ui/km/DocumentDisplay.jspx?_afrLoop=676022724619426&id=1020182.6&_afrWindowMode=0&_adf.ctrl-state=17tpv2cpvy_223

it can output a report like this:

Code: Select all

...
...
...
TABLESPACE NAME                CONTIGUOUS BYTES
------------------------------ ----------------
UNDOTBS2_B                          459,276,288
UNDOTBS2_B                            9,437,184
UNDOTBS2_B                            1,048,576
UNDOTBS2_B                              851,968
UNDOTBS2_B                              655,360
UNDOTBS2_B                               65,536
UNDOTBS2_B                               65,536
UNDOTBS2_B                               65,536

                            Contiguous Extents Report


TABLESPACE NAME                CONTIGUOUS BYTES
------------------------------ ----------------
USERS                             2,925,199,360

100 rows selected.


                            Contiguous Extents Report


TABLESPACE_NAME                # OF EXTENTS TOTAL (MB)
------------------------------ ------------ ----------
SYSAUX                                   61   674.3125
SYSTEM                                    2    261.875
TMS_IDX_10MB                              3       6141
TMS_IDX_1MB                               2       2046
TMS_JNL_10MB                              3       6141
TMS_JNL_1MB                               2       2046
TMS_LOB_10MB                              5      10235
TMS_TBL_10MB                              3       6141
TMS_TBL_1MB                               2       2046
UNDOTBS1_B                                8      484.5
UNDOTBS2_B                                8    449.625
USERS                                     1  2789.6875

12 rows selected.


Table dropped.

SQL>


I want to know is:
1. How to tell us the tablespace is fragmented ?

2. In my case:
my SYSAUX tablespace "Space Used" is 1.3G displayed in EM, the report said TOTAL = 674.3125M (This mean 50% fragmented?)

my USERS tablespace "Space Used" is 1.3M displayed in EM, the report said TOTAL = 2789.6875, seems this is the allocated size.
I do want to know how to show the fragmented factor ?

thanks.

[Tim...]

Hi.

Fragmentation is a difficult topic to discuss because it means different things to different people. If you are just talking about gaps in your datafiles, then there is nothing wrong with that. It will not affect anything. The only time you need to be concerned about this is if you need to reclaim that space.

http://www.oracle-base.com/articles/mis ... -space.php

The easiest way to see if you have an issue is to use the tablespace map in EM. If you have large gaps of empty space and you need to recover the space, then the article shows how you can do it. If the empty space is not a problem, then ignore it. It will get used up as tables and indexes grow.

Cheers

Tim...

[peterx]

ok got it,

very thanks.

[Tim...]