Re: Fragmented tablespace

If you do actually need to reorg, you should try to make changes that reduce the need for future reorgs. Like: All extents should be power of 2 times 64K (eg 64K 128K 256K 512K 1M 2M 4M ...).

Peter Sharman wrote:

> Neil
>
> Are you **sure** you don't work for Platinum? ;)
>
> There are a number of tools that provide mechanisms for doing what the original
> poster asked for, some of them from Oracle, some from third party vendors like
> Platinum, Compuware etc. All of them are basically means of automating a
> reload. I know of no tool that is capable of doing reloads without making the
> table unavailable for at least some time. However, you'd really need to look
> at WHY the reload is necessary. There was a whole thread on this not so long
> ago, so I'm not going to rehash the arguments there. It may be worthwhile to
> search back through these messages to see if it's actually necessary to perform
> the reload in the first place.
>
> HTH.
>
> Pete
>
> Neil Cudd wrote:
>
> > I'd be surprised. De-fragmentation is the act of unloading the blocks and
> > then reloading them in larger contiguous extents. You must drop the object
> > to reload it.
> >
> > As for tools. I'm working on a db at the moment which is 162GB and grows at
> > a rate
> > of 1gb per week. For reorgs we have a tool frrom platinum called TSREORG.
> >
> > This has a fast unload option and since we use it on an 8 way Unix server
> > we unload and reload using 6 threads. The last big reorg I did was 29gb of
> > data tablespace and it managed it in just under 9hrs. That includes all the
> > index rebuilds.
> >
> > honestly, I DONT work for platinum, but it's a damn good product.
> >
> > Des Collins wrote in message <36E53576.34E8_at_bt.removeme.com>...
> > >Dear All,
> > >
> > >Is there a way of de-fragmenting a large tablespace (+2g) without
> > >re-creating it. Are there any tools out there that lets you do this.
> > >
> > >Any help / suggestions appreciated
> > >
> > >Thanks
> > >--
> > >Des Collins
> > >UK
> > >
> > >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> > > These are my views and NOT those of my empoyer.
> > >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> --
>
> Regards
>
> Pete
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Peter Sharman Email: psharman_at_us.oracle.com
> WISE Course Development Manager Phone: +1.650.607.0109 (int'l)
> Worldwide Internal Services Education (650)607 0109 (local)
> San Francisco
>
> SQL> select standard_disclaimer, witty_remark
> 2 from company_requirements;
>
> Opinions are mine and do not necessarily reflect those of Oracle
> Corporation
>
> "Controlling application developers is like herding cats."
> Kevin Loney, ORACLE DBA Handbook
> "Oh no it's not! It's much harder than that!"
> Bruce Pihlamae, long term ORACLE DBA
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> ------------------------------------------------------------------------
>
> Peter Sharman <psharman_at_us.oracle.com>
> Course Development Manager
> Oracle Corporation
> Worldwide Internal Services Education
>
> Peter Sharman
> Course Development Manager 
> Oracle Corporation
> Worldwide Internal Services Education
> 500 Oracle Parkway M/S OPL-B1024 
> Redwood Shores
> California
> 94065
> USA
> SQL> select standard_disclaimer, witty_remark 2 from company_requirements; Opinions are mine and do not necessarily reflect those of Oracle Corporation "Controlling application developers is like herding cats." Kevin Loney, ORACLE DBA Handbook "Oh no it's not! It's much harder than that!" Bruce Pihlamae, long term ORACLE DBA
> Additional Information:
> Last Name Sharman
> First Name Peter
> Version 2.1
Received on Tue Mar 09 1999 - 12:56:23 CST