Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Locally managed tablespaces

RE: Locally managed tablespaces

From: Linda Wang <>
Date: Tue, 20 Jun 2000 10:10:48 -0600
Message-Id: <>

We have 8.1.5 with db_block_size= 8K and all of tablespaces locally managed. I got these:
SQL> select min(header_block)-1 from dba_segments where tablespace_name ='MF_DATA_1'; MIN(HEADER_BLOCK)-1


SQL> select min(header_block)-1 "Overhead" from dba_segments where tablespace_name='SYSTEM';


So, I guess the overhead is 64K not 4 * db_block_size. -Linda

-----Original Message-----
From: []On Behalf Of Steve Orr Sent: Monday, June 19, 2000 5:45 PM
To: Multiple recipients of list ORACLE-L Subject: RE: Locally managed tablespaces

Hi Madhaven,

No. We didn't go locally managed until after 8.1.6. But I did encounter your same symptoms when I had only added one db block for overhead not knowing that I needed 4 with Veritas. When I extended the datafile for the overhead I was able to get the expected behavior. To double check the overhead requirement on a tablespace/datafile (with objects already created in it) you could do something like:

select min(header_block)-1 from dba_segments where tablespace_name = <'WHATEVER'> ;

My result was 4 and my db_block_size is 16K so I sized my datafile to be 1GB+64K or 1,048,640K. My extents were 131,072K (or 128M) so everything fit perfectly: (131,072 * 8) + 64 = 1,048,640.

If 8.1.5 is taking up an entire extent just to store a bitmap I'd say it was roach-class bug and Oracle needs to apply some insecticide...

WHOAH... I just discovered something on another 8.1.6 database which is not using Veritas. The datafile overhead for dictionary managed is 1 DB block but the datafile overhead for locally managed appears to be 4 DB blocks!!!!!!!!! I determined this from the following queries:

SQL> select min(header_block)-1 "Overhead" from dba_segments where tablespace_n 2 ame ='SYSTEM';



SQL> select min(header_block)-1 "Overhead" from dba_segments   2* where tablespace_name ='RMAN_REPOSITORY' SQL> /   Overhead


SOooo... check it out. Run the above query on your tablespace. Then try allowing 4 DB blocks for overhead on locally managed with uniform extents.

Let us know what happens on 8.1.5.

-----Original Message-----
Sent: Monday, June 19, 2000 1:22 PM
To:; Cc:

Hi Steve,
Thanks for the reply.
That is the behaviour that we should have seen and we are on a filesystem and we did create a test tablespace with the overhead of a db block and it still exhibits same behaviour. Did u see this behaviour by any chance on a 8.1.5 database?

Thanks for your help.


>From: "Steve Orr" <>
>Reply-To: <>
>To: <>
>CC: <>
>Subject: RE: Locally managed tablespaces
>Date: Mon, 19 Jun 2000 12:19:19 -0700
>This is curious because I don't have this problem. Is this a bug on earlier
>versions or a platform difference? We're running O8.1.6 on Solaris 2.6 with
>Veritas. I've got 1GB datafiles which can hold 8 extents of 128M. Actually
>the size of the datafiles are 1GB + 64K for the 4 db block overhead needed
>for Veritas (my blocks are 16K). Are you taking into account the db blocks
>needed for datafile overhead? (Normal=1, raw=2, Veritas=4...)
>Give this a try: Create a test tablespace of 1GB plus the necessary db
>overhead. Then create a test table with an initial extent of 512M. Then try
>to allocate another extent to see if it fits. If not extend the datafile by
>another 2 db blocks and try it again. Let us know what happens. Good luck.
>Steve Orr
>-----Original Message-----
>From: []On Behalf Of Madhavan
>Sent: Monday, June 19, 2000 12:11 PM
>To: Multiple recipients of list ORACLE-L
>Subject: Re: Locally managed tablespaces
>Hi Jared,
>Thanks for the mail.
>Yes, larger datafiles is an option but we wanted to create 2G datafiles as
>standard here and about extent sizes we have created tablespaces based on
>small, medium and large and the large tablespaces are going to host 3
>partitions of data which is about 55G.
>If we have 10M extents that would mean 100*15 = 1500 extents for a segment
>and a total of 4500 extents.
>But is this normal behaviour for locally managed tablespaces that an extent
>from each datafile is grabbed?
>Thanks for your help.
>IBM Corporation
> >From: Jared Still <>
> >To: Madhavan Amruthur <>
> >CC: Multiple recipients of list ORACLE-L <>
> >Subject: Re: Locally managed tablespaces
> >Date: Mon, 19 Jun 2000 10:03:25 -0700 (PDT)
> >
> >
> >Use smaller extent sizes and larger data files.
> >
> >You don't mention how many objects are in the tablespaces.
> >
> >4m extents would give you about 10,000 extents. Not
> >excessive if spread over a few objects.
> >
> >Jared
> >
> >On Mon, 19 Jun 2000, Madhavan Amruthur wrote:
> >
> > > Hi,
> > > 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?
> > >
> > > Thanks for your help in advance.
> > > Regards,
> > >
> > > Madhavan
> > > IBM Corporation
> > > Integrated Technology Services
> > >

Author: Steve Orr

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
Received on Tue Jun 20 2000 - 11:10:48 CDT

Original text of this message