RE: Defrag/Reorg Tool
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
- To: "david.barbour1@xxxxxxxxx" <david.barbour1@xxxxxxxxx>, "sharmakdeep_oracle@xxxxxxxxx" <sharmakdeep_oracle@xxxxxxxxx>
- Date: Thu, 12 Feb 2009 18:32:31 -0500
Good point, David. I forgot to mention shrink space....
--
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<mailto:mark.bobak@xxxxxxxxxxxxxxx>
www.proquest.com<http://www.proquest.com>
www.csa.com<http://www.csa.com>
ProQuest...Start here.
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of David Barbour
Sent: Thursday, February 12, 2009 6:08 PM
To: sharmakdeep_oracle@xxxxxxxxx
Cc: dannorris@xxxxxxxxxxxxx; Oracle L
Subject: Re: Defrag/Reorg Tool
Okay. What version of Oracle are you on? Are you using locally managed
tablespaces and ASSM? Check out the command 'alter table shrink space' (in
conjunction with alter table enable row movement ). You need to identify which
tables are using those blocks, shrink them, then resize the datafiles. Please
nbe careful and understand what might happen if in fact you need to invoke row
movement. If you've got long raw data, you'll probably have to use exp/imp to
reorg the tables.
On Thu, Feb 12, 2009 at 5:29 PM, Deepak Sharma
<sharmakdeep_oracle@xxxxxxxxx<mailto:sharmakdeep_oracle@xxxxxxxxx>> wrote:
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<mailto:dannorris@xxxxxxxxxxxxx>>
To: "sharmakdeep_oracle@xxxxxxxxx<mailto:sharmakdeep_oracle@xxxxxxxxx>"
<sharmakdeep_oracle@xxxxxxxxx<mailto:sharmakdeep_oracle@xxxxxxxxx>>
Cc: Oracle L <oracle-l@xxxxxxxxxxxxx<mailto: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<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.
-Deepak
Other related posts: