RE: Locally managed tablespaces

Yup, it's 64K. Here's an excerpt from Steve Adams' website:

"locally managed tablespaces have a 64K bitmap after the datafile header block."

Check it out at: http://www.ixora.com.au/tips/creation/datafiles.htm

Steve Orr

-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Linda Wang Sent: Tuesday, June 20, 2000 10:33 AM
To: Multiple recipients of list ORACLE-L Subject: RE: Locally managed tablespaces

Steve,
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

                  8

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

  Overhead

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

-----Original Message-----
Sent: Monday, June 19, 2000 5:45 PM
To: Multiple recipients of list ORACLE-L

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';

  Overhead

         1

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

         4

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.
HTH,
Steve

-----Original Message-----
Sent: Monday, June 19, 2000 1:22 PM
To: steve_at_arzoo.com; ORACLE-L_at_fatcity.com Cc: mad012000_at_hotmail.com

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.
Regards,

Madhavan

>From: "Steve Orr" <sorr_at_arzoo.com>
>Reply-To: <steve_at_arzoo.com>
>To: <ORACLE-L_at_fatcity.com>
>CC: <mad012000_at_hotmail.com>
>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
>block
>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: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Madhavan
>Amruthur
>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
>a
>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.
>Regards,
>Madhavan
>IBM Corporation
>
>
> >From: Jared Still <jkstill_at_bcbso.com>
> >To: Madhavan Amruthur <mad012000_at_hotmail.com>
> >CC: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> >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
  INET: sorr_at_arzoo.com

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: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

--
Author: Linda Wang
  INET: lwang_at_messagemedia.com

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: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in