Re: locally managed tablespaces TEMP or RBS

From: Howard J. Rogers <howardjr_at_www.com>
Date: Fri, 12 Oct 2001 07:41:52 +1000
Message-ID: <3bc613fb@news.iprimus.com.au>

There's a discussion of dictionary managed versus locally managed on my website, but that really isn't the issue here.

Proper temporary tablespace (ie, tablespace which has had the keyword 'temporary' specified during its creation) never fragments, because fragmentation occurs when extents are dropped, and extents within proper temporary tablespace are never dropped, just re-used by new sorts. And that's true for dictionary managed as well as locally managed temporary tablespaces.

(Actually, extents do get dropped -at database shutdown, they are ALL dropped, so on subsequent startup, the entire tablespace is empty -which still means fragmentation is not happening).

So, either your temporary tablespace is not proper temporary tablespace, or you are misconstruing what fragmentation is. Whichever it is, you need to sort that out before thinking that going locally managed is, in and of itself, going to help you.

Regards
HJR

Oracle Resources: http//www.geocities.com/howardjr2000

"Rob Williamson" <robw_at_physics.umd.edu> wrote in message news:3BC5C199.D1B06033_at_physics.umd.edu...
> I was considering playing arround with locally managed tablespaces
> for Temp and RBS and maybe Index tablespaces in a new database I am
> designing.
> I use 8.1.5 ( no patches yet ). Does anyone have any advise/ horror
> stories or
> things to consider. My DB is small .5 G or less and I will not have too
> many transactions
> so there is no real need for a faster system but I have noticed
> fragmentation problems
> with my current Temp tablespace and thought this was Oracles latest
> cure.
> Any thoughts?
> Thanks
> Rob
Received on Thu Oct 11 2001 - 16:41:52 CDT