Re: Defrag/Reorg Tool

  • From: Deepak Sharma <sharmakdeep_oracle@xxxxxxxxx>
  • To: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>, "dannorris@xxxxxxxxxxxxx" <dannorris@xxxxxxxxxxxxx>
  • Date: Thu, 12 Feb 2009 21:55:17 -0800 (PST)

Mark,

Thanks for your suggestions. We have been using exactly what you have 
mentioned, dbms_redefinition, for instance. But, just due to the size of the 
database and number of objects in it, we have been always chasing a moving 
target, thus I have started to look into any tools out there. We are using LMT 
on a 10.2.0.3 DB of 65TB allocated size currently (consumed ~50TB). 

I've came across Live-reorg from Quest, as suggested in one of the replies. I'm 
starting to look into some other ones if they would do the job, such as 
shareplex, some tools from Golden gate, space manager etc. We may end up doing 
what we have been doing manually finally tho.

-Deepak





________________________________
From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
To: "sharmakdeep_oracle@xxxxxxxxx" <sharmakdeep_oracle@xxxxxxxxx>; 
"dannorris@xxxxxxxxxxxxx" <dannorris@xxxxxxxxxxxxx>
Cc: Oracle L <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, February 12, 2009 4:51:47 PM
Subject: RE: Defrag/Reorg Tool

 
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 look at DBMS_REDEFINITION.
 
Either way, it shouldnʼt be too hard to script it.
 
-Mark
 
--
Mark J. Bobak
Senior Database Administrator, System & Product Technologies
ProQuest
789 E. Eisenhower, Parkway, P.O. Box 1346
Ann Arbor MI 48106-1346
+1.734.997.4059  or +1.800.521.0600 x 4059
mark.bobak@xxxxxxxxxxxx
www.proquest.com
www.csa.com

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.


HTH,
Deepak
 

________________________________
 
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. 
 
Dan

Sent from my iPhone

On Feb 12, 2009, at 3:00 PM, Deepak Sharma <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.

-Deepak


      

Other related posts: