Tablespace Allocation Type & Extent Management
I seem to be confused about the relationship between tablespace management and allocation methods. What is the USER allocation_type (as reported by DBA_TABLESPACES)? I thought this corresponded only to dictionary-managed tablespaces--I see it associated with locally-managed tablespaces as well. Also, I thought that PCTINCREASE was only applicable to dictionary-managed tablespaces. Again, I am seeing a PCT_INCREASE=50 for some locally-managed tablespaces in one of my databases. How is this possible? Based on everything I have read (docs & metalink) about LMTs vs. Dictionary, I am perplexed by the results of the following query I ran: 1 select extent_management, allocation_type, pct_increase, count(*) 2 from dba_tablespaces 3 group by extent_management, allocation_type, pct_increase; EXTENT_MAN ALLOCATIO PCT_INCREASE COUNT(*) -- - -- DICTIONARY USER 0 4 DICTIONARY USER50 3 LOCAL SYSTEM 1 LOCAL UNIFORM 0 1 LOCAL USER50283 <--- As always, your feedback is ALWAYS appreciated. -w __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Tablespace Allocation Type & Extent Management
Title: RE: Tablespace Allocation Type & Extent Management > -Original Message- > From: Walter K [mailto:[EMAIL PROTECTED]] > > I seem to be confused about the relationship between > tablespace management and allocation methods. > > What is the USER allocation_type (as reported by > DBA_TABLESPACES)? I thought this corresponded only to > dictionary-managed tablespaces--I see it associated > with locally-managed tablespaces as well. > > Also, I thought that PCTINCREASE was only applicable > to dictionary-managed tablespaces. Again, I am seeing > a PCT_INCREASE=50 for some locally-managed tablespaces > in one of my databases. How is this possible? > > Based on everything I have read (docs & metalink) > about LMTs vs. Dictionary, I am perplexed by the > results of the following query I ran: > > 1 select extent_management, allocation_type, > pct_increase, count(*) > 2 from dba_tablespaces > 3 group by extent_management, allocation_type, > pct_increase; > > EXTENT_MAN ALLOCATIO PCT_INCREASE COUNT(*) > -- - -- > DICTIONARY USER 0 4 > DICTIONARY USER 50 3 > LOCAL SYSTEM 1 > LOCAL UNIFORM 0 1 > LOCAL USER 50 283 <--- > How did you create that tablespace? I have a test database (Oracle 8.1.6 on Windows 2000) where I have tried to create a tablespace of every possible kind, and I don't have a tablespace with EXTENT_MANAGEMENT = 'LOCAL' and ALLOCATION_TYPE = 'USER'. What's your secret? -- Jacques R. Kilchoer (949) 754-8816 Quest Software, Inc. 8001 Irvine Center Drive Irvine, California 92618 U.S.A. http://www.quest.com
Re: Tablespace Allocation Type & Extent Management
When you convert from dictionary to local, then you will see the USER allocation type - because all of the segments that already exist in the tablespace. You also need to be careful when converting that you have a reasonable size for common denominator of all extents. If you had (for example) some 128k and some 136k extents, then the local tspace conversion would need it bitmaps based on 8k - not probably what you had in mind. Cheers Connor --- Walter K <[EMAIL PROTECTED]> wrote: > I seem to be confused about the relationship between > tablespace management and allocation methods. > > What is the USER allocation_type (as reported by > DBA_TABLESPACES)? I thought this corresponded only > to > dictionary-managed tablespaces--I see it associated > with locally-managed tablespaces as well. > > Also, I thought that PCTINCREASE was only applicable > to dictionary-managed tablespaces. Again, I am > seeing > a PCT_INCREASE=50 for some locally-managed > tablespaces > in one of my databases. How is this possible? > > Based on everything I have read (docs & metalink) > about LMTs vs. Dictionary, I am perplexed by the > results of the following query I ran: > > 1 select extent_management, allocation_type, > pct_increase, count(*) > 2 from dba_tablespaces > 3 group by extent_management, allocation_type, > pct_increase; > > EXTENT_MAN ALLOCATIO PCT_INCREASE COUNT(*) > -- - -- > DICTIONARY USER 0 4 > DICTIONARY USER50 3 > LOCAL SYSTEM 1 > LOCAL UNIFORM 0 1 > LOCAL USER50283 <--- > > > As always, your feedback is ALWAYS appreciated. > -w > > __ > Do You Yahoo!? > Get personalized email addresses from Yahoo! Mail - > only $35 > a year! http://personal.mail.yahoo.com/ > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Walter K > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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). = Connor McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk) "Some days you're the pigeon, some days you're the statue" Do You Yahoo!? Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk or your free @yahoo.ie address at http://mail.yahoo.ie -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Tablespace Allocation Type & Extent Management
How does one convert a tablespace from dictionary to local? I've wanted to do the reverse but I never saw anything in the docs that said this was an option. Are you saying then, that this is probably what happened in my case and now the tablespace is performing extent management based on 8k extents? I'm confused. Thanks again. -w --- Connor McDonald <[EMAIL PROTECTED]> wrote: > When you convert from dictionary to local, then you > will see the USER allocation type - because all of > the > segments that already exist in the tablespace. > > You also need to be careful when converting that you > have a reasonable size for common denominator of all > extents. If you had (for example) some 128k and > some > 136k extents, then the local tspace conversion would > need it bitmaps based on 8k - not probably what you > had in mind. > > Cheers > Connor > > --- Walter K <[EMAIL PROTECTED]> wrote: > I seem > to > be confused about the relationship between > > tablespace management and allocation methods. > > > > What is the USER allocation_type (as reported by > > DBA_TABLESPACES)? I thought this corresponded only > > to > > dictionary-managed tablespaces--I see it > associated > > with locally-managed tablespaces as well. > > > > Also, I thought that PCTINCREASE was only > applicable > > to dictionary-managed tablespaces. Again, I am > > seeing > > a PCT_INCREASE=50 for some locally-managed > > tablespaces > > in one of my databases. How is this possible? > > > > Based on everything I have read (docs & metalink) > > about LMTs vs. Dictionary, I am perplexed by the > > results of the following query I ran: > > > > 1 select extent_management, allocation_type, > > pct_increase, count(*) > > 2 from dba_tablespaces > > 3 group by extent_management, allocation_type, > > pct_increase; > > > > EXTENT_MAN ALLOCATIO PCT_INCREASE COUNT(*) > > -- - -- > > DICTIONARY USER 0 4 > > DICTIONARY USER50 3 > > LOCAL SYSTEM 1 > > LOCAL UNIFORM 0 1 > > LOCAL USER50283 <--- > > > > > > As always, your feedback is ALWAYS appreciated. > > -w > > > > __ > > Do You Yahoo!? > > Get personalized email addresses from Yahoo! Mail > - > > only $35 > > a year! http://personal.mail.yahoo.com/ > > -- > > Please see the official ORACLE-L FAQ: > > http://www.orafaq.com > > -- > > Author: Walter K > > INET: [EMAIL PROTECTED] > > > > 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: [EMAIL PROTECTED] (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). > > > = > Connor McDonald > http://www.oracledba.co.uk (mirrored at > http://www.oradba.freeserve.co.uk) > > "Some days you're the pigeon, some days you're the > statue" > > > Do You Yahoo!? > Get your free @yahoo.co.uk address at > http://mail.yahoo.co.uk > or your free @yahoo.ie address at > http://mail.yahoo.ie > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: =?iso-8859-1?q?Connor=20McDonald?= > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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). __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list y
Re: Tablespace Allocation Type & Extent Management
(From 8.1.6), dbms_space_admin can convert back and forth between the two... When you are converting from dictionary to local, then oracle needs to be able map the existing content into a bitmap header where each bit maps to a consistent size. Thus if you're entire tablespace contained (say) 50 x 100M extents, then the bitmap could contain 50 bits each representing 100M. If the tablespace contained 10x10M, 10x5M extents, then each bit would represent 5m and the extents that are 10M would occupy 2 bits. So Oracle uses the greatest common denominator of all extent sizes in the tablespace to work out what each bit should represent. So if you had 10x10m extents, and 5x128k extents, and 2x16k extents, the each bit will represent 16k, and thus each 10m extent will be represented by 640bits (each one mapping to 16k), so in reality oracle is viewing this as a table of 640x16k extents.. hth connor --- Walter K <[EMAIL PROTECTED]> wrote: > How does one convert a tablespace from dictionary to > local? I've wanted to do the reverse but I never saw > anything in the docs that said this was an option. > > Are you saying then, that this is probably what > happened in my case and now the tablespace is > performing extent management based on 8k extents? > I'm > confused. > > Thanks again. > -w > --- Connor McDonald <[EMAIL PROTECTED]> wrote: > > When you convert from dictionary to local, then > you > > will see the USER allocation type - because all of > > the > > segments that already exist in the tablespace. > > > > You also need to be careful when converting that > you > > have a reasonable size for common denominator of > all > > extents. If you had (for example) some 128k and > > some > > 136k extents, then the local tspace conversion > would > > need it bitmaps based on 8k - not probably what > you > > had in mind. > > > > Cheers > > Connor > > > > --- Walter K <[EMAIL PROTECTED]> wrote: > I > seem > > to > > be confused about the relationship between > > > tablespace management and allocation methods. > > > > > > What is the USER allocation_type (as reported by > > > DBA_TABLESPACES)? I thought this corresponded > only > > > to > > > dictionary-managed tablespaces--I see it > > associated > > > with locally-managed tablespaces as well. > > > > > > Also, I thought that PCTINCREASE was only > > applicable > > > to dictionary-managed tablespaces. Again, I am > > > seeing > > > a PCT_INCREASE=50 for some locally-managed > > > tablespaces > > > in one of my databases. How is this possible? > > > > > > Based on everything I have read (docs & > metalink) > > > about LMTs vs. Dictionary, I am perplexed by the > > > results of the following query I ran: > > > > > > 1 select extent_management, allocation_type, > > > pct_increase, count(*) > > > 2 from dba_tablespaces > > > 3 group by extent_management, > allocation_type, > > > pct_increase; > > > > > > EXTENT_MAN ALLOCATIO PCT_INCREASE COUNT(*) > > > -- - -- > > > DICTIONARY USER 0 4 > > > DICTIONARY USER50 3 > > > LOCAL SYSTEM 1 > > > LOCAL UNIFORM 0 1 > > > LOCAL USER50283 > <--- > > > > > > > > > As always, your feedback is ALWAYS appreciated. > > > -w > > > > > > > __ > > > Do You Yahoo!? > > > Get personalized email addresses from Yahoo! > Mail > > - > > > only $35 > > > a year! http://personal.mail.yahoo.com/ > > > -- > > > Please see the official ORACLE-L FAQ: > > > http://www.orafaq.com > > > -- > > > Author: Walter K > > > INET: [EMAIL PROTECTED] > > > > > > 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: [EMAIL PROTECTED] (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). > > > > > > = > > Connor McDonald > > http://www.oracledba.co.uk (mirrored at > > http://www.oradba.freeserve.co.uk) > > > > "Some days you're the pigeon, some days you're the > > statue" > > > > > > > Do You Yahoo!? > > Get your free @yahoo.co.uk address at > > http://mail.yahoo.co.uk > > or your free @yahoo.ie address at > > http://mail.yahoo.ie > > -- > > Please see the official ORACLE-L FAQ: > > http://www.orafaq.com > > -- > > Author: =?iso-8859-1?q?Connor=20McDonald?= > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- (858) 538-5
