RE: Defrag/Reorg Tool

  • From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
  • To: "sharmakdeep_oracle@xxxxxxxxx" <sharmakdeep_oracle@xxxxxxxxx>, "dannorris@xxxxxxxxxxxxx" <dannorris@xxxxxxxxxxxxx>
  • Date: Thu, 12 Feb 2009 17:51:47 -0500

You didn't mention database version, if you're using LMTs, autoallocate or 
uniform extents, and whether or how much downtime you can tolerate for this 
clean up.)

However, if you identify those segments that are taking up space at the 'end' 
of the datafiles, and move those (alter table move, alter index rebuild), that 
ought to be enough to solve your problems, as long as you don't run into 
problems w/ the holes being smaller than the requested extent size of the 
objects you're moving.  If you can't tolerate any downtime, you may want to 

Either way, it shouldn't be too hard to script it.


Mark J. Bobak
Senior Database Administrator, System & Product Technologies
789 E. Eisenhower, Parkway, P.O. Box 1346
Ann Arbor MI 48106-1346
+1.734.997.4059  or +1.800.521.0600 x 4059

ProQuest...Start here.

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Deepak Sharma
Sent: Thursday, February 12, 2009 5:29 PM
To: dannorris@xxxxxxxxxxxxx
Cc: Oracle L
Subject: Re: Defrag/Reorg Tool

I should clarify the need for this tool.

At some point we over-allocated the tablespaces. So, we have large datafiles, 
but with lot of space not being used (holes) currently. We want to reclaim this 
space, such as by shrinking (resize) the datafiles. But, since there are one or 
blocks at the end of the datafile in a lot of cases, the shrink doesn't work 
very well. If instead there is a tool that can move the blocks that are at the 
end of the datafile(s) to somewhere in the beginning of the free space, the 
resize could work.


From: Dan Norris <dannorris@xxxxxxxxxxxxx>
To: "sharmakdeep_oracle@xxxxxxxxx" <sharmakdeep_oracle@xxxxxxxxx>
Cc: Oracle L <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, February 12, 2009 4:13:17 PM
Subject: Re: Defrag/Reorg Tool
I suggest you review a presentation by Tom Kyte on reorganizing objects that he 
gave yesterday at RMOUG. I expect it should be on his website somewhere.

Short version: you probably don't need to do anything.


Sent from my iPhone

On Feb 12, 2009, at 3:00 PM, Deepak Sharma 
<sharmakdeep_oracle@xxxxxxxxx<mailto:sharmakdeep_oracle@xxxxxxxxx>> wrote:
I wanted to get recommendations on tools for online reorg/defrag of 
multi-terrabyte Oracle database.

Thanks in advance for your feedback.


Other related posts: