High Fragmentation in Tablespace

All posts relating to Oracle database administration.

Moderator: Tim...

High Fragmentation in Tablespace

Postby sivakumarocp » Tue Jun 18, 2013 10:09 am

Hi Tim,

A tablespace from one of our production database has huge fragmentation, nearly 100G out of 140G.
This tablespace has both index and table segments.

Code: Select all
Tablespace Name                 Allocated       Used       Free      Space
                                  (in MB)    (in MB)    (in MB)   Avail(%)
------------------------------ ---------- ---------- ---------- ----------
MDM_DAT                        140288 42054.3125 98233.6875      70.02

                                                                     SMALLEST
Tablespace Name                                                          SIZE CURRENT     POSS
                FILE_ID FILE_NAME                                       POSS.    SIZE  SAVINGS
--------------- ------- -------------------------------------------- -------- -------- --------
MDM_DAT          19      /dm1/oradata/data01/DM1P1/MDM_DAT02.dbf      23,952   24,576      624
MDM_DAT          26      /dm1/oradata/data01/DM1P1/MDM_DAT05.dbf      20,041   20,480      439
MDM_DAT          29    /dm1/oradata/data01/DM1P1/MDM_DAT06.dbf      20,163   20,480      317
MDM_DAT          21      /dm1/oradata/data01/DM1P1/MDM_DAT04.dbf      24,386   24,576      190
MDM_DAT          20    /dm1/oradata/data01/DM1P1/MDM_DAT03.dbf      24,451   24,576      125
MDM_DAT          31    /dm1/oradata/data01/DM1P1/MDM_DAT07.dbf      1,024    1,024        0
MDM_DAT          16    /dm1/oradata/data01/DM1P1/MDM_DAT01.dbf      24,576   24,576       0


Below is the segment count for this tablespace.

Code: Select all
SEGMENT_TYPE         COUNT(*)
------------------ ----------
INDEX                      17
TABLE                      50


Please let me know what is the best way to reclaim nearly 100G space from this tablespace without down time.
Regards
Sivakumar.A
www.sivakumardba.com
sivakumarocp
Advisor
 
Posts: 256
Joined: Sat Jun 27, 2009 3:53 am
Location: India

Re: High Fragmentation in Tablespace

Postby Tim... » Tue Jun 18, 2013 2:38 pm

Hi.

There is no method that gives no downtime. You can decide what method you prefer by checking out this article.

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

The closest you might get is manually moving all the tables using Online Table Redefinitions.

http://www.oracle-base.com/articles/9i/ ... definition

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

Re: High Fragmentation in Tablespace

Postby sivakumarocp » Tue Jun 18, 2013 3:48 pm

Hi Tim,

My database is running on 10.2.0.4, so I am not sure which option to go for,

1. SHRINK SPACE COMPACT
2. DBMS_REDEFINITION.

which of the above two method would be the best solution to perform online table re-org.
Regards
Sivakumar.A
www.sivakumardba.com
sivakumarocp
Advisor
 
Posts: 256
Joined: Sat Jun 27, 2009 3:53 am
Location: India

Re: High Fragmentation in Tablespace

Postby Tim... » Tue Jun 18, 2013 5:11 pm

Hi.

If you read the article you will know that SHRINK is not really a solution for this as there is no guarantee the data will be placed in blocks at the start of the datafile. You may end up with compacted blocks at the end of the datafile, and no way to shrink it.

So out of these, the OTR is the best option, but it will not guarantee zero downtime.

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


Return to Oracle Database Administration

Who is online

Users browsing this forum: No registered users and 2 guests