Re: Defrag/Reorg Tool

From: Andrew Kerber <andrew.kerber_at_gmail.com>
Date: Thu, 12 Feb 2009 17:49:15 -0600
Message-ID: <ad3aa4c90902121549y749276e2o4e19989b85122420_at_mail.gmail.com>

Datapump will move tables from one tablespace to another.

At one time I used a tool called live reorg from quest, but it had way too many problems. We finally ended up just generating the scripts ourselves. I can probably find the sql scripts we used, but there is nothing particularly special about them.

On Thu, Feb 12, 2009 at 5:32 PM, Bobak, Mark <Mark.Bobak_at_proquest.com>wrote:

> 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_at_proquest.com <mark.bobak_at_il.proquest.com>
> www.proquest.com
> www.csa.com
>
> *ProQuest...*Start here.
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *David Barbour
> *Sent:* Thursday, February 12, 2009 6:08 PM
> *To:* sharmakdeep_oracle_at_yahoo.com
> *Cc:* dannorris_at_dannorris.com; 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_at_yahoo.com> 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_at_dannorris.com>
> *To:* "sharmakdeep_oracle_at_yahoo.com" <sharmakdeep_oracle_at_yahoo.com>
> *Cc:* Oracle L <oracle-l_at_freelists.org>
> *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_at_yahoo.com>
> wrote:
>
> I wanted to get recommendations on tools for online reorg/defrag of
> multi-terrabyte Oracle database.
>
> Thanks in advance for your feedback.
>
> -Deepak
>
>
>
>
>
>
>

-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Feb 12 2009 - 17:49:15 CST