RE: Locally managed tablespaces

Hi Linda,
That is correct.
Its 64K overhead for locally managed tablespaces with uniform extent allocation. You can also refer to that on Steve Adams website at http://www.ixora.com.au/tips/creation/datafiles.htm

Hope this helps.
Regards,
Madhavan

>From: "Linda Wang" <lwang_at_messagemedia.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: RE: Locally managed tablespaces
>Date: Tue, 20 Jun 2000 09:33:12 -0800
>
>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
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
Received on Tue Jun 20 2000 - 12:35:18 CDT