Re: Single, Locally Managed system managed tablespace

From: Frank van Bortel <frank.van.bortel_at_gmail.com>
Date: Sat, 20 Jan 2007 11:09:13 +0100
Message-ID: <eospo6$mot$1@news1.zwoll1.ov.home.nl>

DA Morgan schreef:

> Number.One.Mike_at_gmail.com wrote:

>> For the evoloution of our product we are considering moving to a single
>> system managed tablespace. The application is a very straight forward
>> OLTP system, while it has a lot of tables none of them are big (half a
>> million on a couple). There are two notable exceptions to this, where
>> Documents and notes are stored, these (especially the document one) can
>> get very big (but not always). At the moment we are going to go for a
>> single Tablespace but advise that if they plan to hold lots of
>> documents or lots of adhoc notes then they may wish to move these
>> tables to a seperate tablespace.
>>
>> Can anyone give reasons why this would not be a sensible thing to do?
>> Would you bulk if given this advice when implementing a new OLTP
>> system? Do people still like Data / Index / LOB on seperate TS's for
>> administration reasons?
>>
>> Thanks in advance,
>>
>> Mike.

> 
> For administrative purposes, as you indicate, I would separate the
> segment types. And it would be my instinct to not mix LOB segments
> with others even if I used a single tablespace for both tables and
> indexes.

As Daniel already indicated - LOB segments are (probably) better off in a tablespace of their own, when getting large.

All the rest is for maintenance reasons only - you can (most likely) rebuild indexes, so loosing all indexes when the index tablespace datafiles are gone is not a disruption of business.

However, why would one choose a system managed tablespace? I assume you mean a dictionary managed tablespace (the dictionary is in the system tablespace), as opposed to a locally managed tablespace.

Go for a locally managed tablespace, with fixed blocksize. If on 10GR2, let oracle do the managing (segment space auto)

-- 
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

Received on Sat Jan 20 2007 - 04:09:13 CST