Re: Locally managed tablespaces

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/06/20
Message-ID: <8io24u$bop$1@nnrp1.deja.com>#1/1

In article <JEDIOKIOMDNAIBAA_at_my-deja.com>,   mad5698_at_my-Deja.com wrote:
> Hi all,
> We created our tablespaces as locally managed and we have about 4
 tablespaces that are 40G. According to
> note 111666.1 on Metalink one extent from each datafile is used for
 storage management and we have 200M
> uniform extent size which means we lose 4G over a 40G tablespace with
 20 data files. We have 4 tablespaces
> with 40G and that means 16G. We also have other tablespaces 20G.
>
> Is there a better way to do it other than converting it into a dict
 managed tbs with uniform extent size?
>

the note on metalink is incorrect. the test case they used was a bad example. They created a datafile that was exactly 10m in size and tried to use uniform sizing of 5m. The problem is we need 64k for the bitmap -- after we took 64k out of the datafile there was room for only 1 5meg extent. the other 4.99 meg extent could not be used and is not reported as free space (its DEAD space).

They should have used 10m+64k as the file size.

So, their example:

SVRMGR> create tablespace
locally_managed

     2> datafile '/server0/oradata/v816/lm01.dbf' size 10 m

     3> extent management local uniform size 5 m;
Statement
processed.

SVRMGR> select bytes from dba_free_space where

     2> TABLESPACE_NAME
= 'LOCALLY_MANAGED'; BYTES

---

   5242880

shows the worst case -- an entire extent seems to be used. If you simply change it to:

ops$tkyte_at_DEV8I.WORLD> create tablespace locally_managed   2 datafile '/tmp/lm.dbf' size 10304k -- 10m + 64k   3 extent management local uniform size 5m;

Tablespace created.

ops$tkyte_at_DEV8I.WORLD> select bytes from dba_free_space

where                                 tablespace_name

= 'LOCALLY_MANAGED';      BYTES

---

  10485760

ops$tkyte_at_DEV8I.WORLD>

You get all of the space. I've sent the author of that note an email asking them to update it to reflect the correct procedure for locally managed tablespaces with uniform extent sizing -- to allocate an additional 64k / datafile.... It should be updated in a short while.

> Thanks for your help in advance.
> Regards,
> ---
> Madhavan
> IBM Corporation
> Integrated Technology Services
>
> --== Sent via Deja.com http://www.deja.com/ ==--
> Before you buy.
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Tue Jun 20 2000 - 00:00:00 CDT