Re: Defrag/Reorg Tool

  • From: Deepak Sharma <sharmakdeep_oracle@xxxxxxxxx>
  • To: David Barbour <david.barbour1@xxxxxxxxx>
  • Date: Thu, 12 Feb 2009 15:14:40 -0800 (PST)

We are on 10.2.03, using LMT with ASSM. The allocated DB size is 65TB and 
actually used is 50TB.

There's a lot of manual time involved currently doing a manual reorg, like 
moving objects to a brand-new tablespace, and then once the old one is empty, 
drop it. That's why we are looking for a tool, that could do it with some, but 
small intervention.

-Thanks




________________________________
From: David Barbour <david.barbour1@xxxxxxxxx>
To: sharmakdeep_oracle@xxxxxxxxx
Cc: dannorris@xxxxxxxxxxxxx; Oracle L <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, February 12, 2009 5:07:45 PM
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> 
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>
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: