Re: Q: Reorg an SAP tablespace

Vince Laurent wrote:
> So you are saying if I started with a 64K extent
>
> create tablespace PSAPODSD
> datafile ‘c:\oradata\blahblah.dbf’ size 2G
> extent management local
> autoallocate;
>
> it would take into account all the available chunks and actually use smaller chunks when trying to allocate for a lager table?

Yes. If you are trying to allocate a 1MB extent and all of your free space is only 64K chunks, then the extent will be broken up into 16 smaller pieces, each fitting into one of the smaller chunks of free space.

> Won't I end up in the same situation I am now with the developers always adding/removing data and therefore changing the sizing
> requirements?

You will not. The problem with DMT's is that there is no control of the size of the chunks of free space. In LMT's with AUTOALLOCATE, all chunks of free space are a multiple of 64K. So you can have lots of small chunks of free space, but they will always be usable.

> This sounds like it might be the way to go.

I think so. Personally, I always use AUTOALLOCATE with my LMT's unless I have a very specific reason to use UNIFORM, which in my environment has become the exception not the rule. I can't even tell you the last time I even thought about what an "appropriate" extent size is. I create my tablespaces with AUTOALLOCATE and forget about them.

HTH,
Brian

-- 
===================================================================

Brian Peasland
oracle_dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown