This forum is currently locked. You can't register or post questions at this time. (read more)

Detect Tablespace Fragmentation

All posts relating to Oracle database administration.

Moderator: Tim...

peterx
Senior Member
Posts: 208
Joined: Wed Feb 09, 2011 7:07 am

Detect Tablespace Fragmentation

Postby peterx » Thu Jan 31, 2013 2:39 am

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Detect Tablespace Fragmentation

Postby Tim... » Thu Jan 31, 2013 7:42 am

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

peterx
Senior Member
Posts: 208
Joined: Wed Feb 09, 2011 7:07 am

Re: Detect Tablespace Fragmentation

Postby peterx » Fri Feb 01, 2013 6:32 am

ok got it,

very thanks.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Detect Tablespace Fragmentation

Postby Tim... » Fri Feb 01, 2013 8:25 am

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 9 guests