RE: Locally Managed Tablespaces

  • From: "David Kurtz" <info@xxxxxxxxxxxxxxx>
  • To: <niall.litchfield@xxxxxxxxx>, <Nagarajan.Subbiah@xxxxxxxx>
  • Date: Fri, 21 Jan 2005 00:19:09 +0100

I think also it can depend on how much control you have over tablespace
layout.  If you have total discretion over which table or index goes into
which tablespace, then there is no reason not to use uniform extents.
However, if you are dealing with a packaged application there may be other

For example, PeopleSoft dynamically generates the DDL to build database
objects in the Application Designer, and this includes the tablespace name.
So it can be a lot of work to create a new tablespace model, and have it
maintained from within the proprietory PeopleSfot tools, and then there is
more work on maintaining it because when PeopleSoft delivers patches or
upgrades, table and index definitions are delivered with the default
PeopleSoft tablespace.  So the question to ask is whether the advantages of
uniform extent sizing, over autoallocation outweigh the administrative
overhead of maintaining a different tablespace?

You may get some small free space fragments in an autoallocate LMT, but much
less than a dictionary managed table.  But that doesn't really matter.  The
advantange of LMTs is not the space saving, but the reduction in overhead of
maintaining the central dictionary by using a bitmap in the tablespace, and
reduction in ST enqueue contention.  You still get those advantages with

On some systems I have partitioned some of the largest tables, putting each
range partition into a new and separate tablespace.  Because these are _new_
tablespaces that I have created, and I have taken the decision to control
the objects that go into them, thses have a uniform extent sizing and the
rest at autoallocate.

David Kurtz
Go-Faster Consultancy Ltd.
tel: +44 (0)7771 760660
fax: +44 (0)7092 348865
Book: PeopleSoft for the Oracle DBA:
PeopleSoft DBA Forum:

> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Niall Litchfield
> Sent: 20 January 2005 21:30
> To: Nagarajan.Subbiah@xxxxxxxx
> Cc: rlsmith@xxxxxxx; oracle-l@xxxxxxxxxxxxx
> Subject: Re: Locally Managed Tablespaces
> On Thu, 20 Jan 2005 15:12:25 -0500, Subbiah, Nagarajan
> <Nagarajan.Subbiah@xxxxxxxx> wrote:
> > Is the Uniform allocation has any advantageous than SYSTEM
> allocation type?
> > Does the SYSTEM allocation type has any issues?
> >
> > Raja.
> With uniform allocation you cannot get free space fragmentation
> problems, with system managed you can (but are unlikely to).
> With uniform allocation if you guess^H^H^H^H calculate the segment
> size wrongly you can end up with very large or very small numbers of
> extents.
> I prefer and have long advocated uniform extent sizing, others have
> advocated a system policy.
> --
> Niall Litchfield
> Oracle DBA
> --
> //


Other related posts: