 | |
max extent size in locally managed tablespace with AUTOALLOCATE
comp.databases.oracle.server comp.databases.oracle.server
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
max extent size in locally managed tablespace with AUTOALLOCATE -
06-13-2008
, 04:31 AM
#2
| |||
| |||
|
|
What is the maximum extent size possible in locally managed tablespaces (LMT) with autoallocate? Let me clarify the question: I am interested in conventional datafiles (not bigfiles). I understand that "strictly speaking" this question does not have an answer as extent allocation in LMT isn't documented by Oracle and can change between versions/platforms/patches, may depend on block size, datafile size, empty space, etc. So I am looking for "informal" answer applicable in real life. In my tests (done with 8K block size) max extent size was 64M, even for 20 GB table. I saw this value mentioned in several discussions, although Jonathan Lewis mentions 256M: http://www.jlcomp.demon.co.uk/faq/lmt_losses.html Why do I need an answer: we have several databases that were created with 20+ filesystems, 50+ tablespaces, many tablespaces between 1GB and 3 GB, and with random distribution of datafiles between filesystems. Now the filesystems are 98% or so full, AUTOEXTEND has been disabled, and I need to monitor next extent size as extent allocation in an LMT can fail even when tablespace has 15% free space. Thanks |
IMHO 'System' LMT's exist only to avoid DBA extra work over personal/
development/nonimportant databases. On a production system you should
follow the 'uniform' way.
HTH.
Cheers.
Carlos.
#3
| |||
| |||
|
|
What is the maximum extent size possible in locally managed tablespaces (LMT) with autoallocate? Let me clarify the question: I am interested in conventional datafiles (not bigfiles). I understand that "strictly speaking" this question does not have an answer as extent allocation in LMT isn't documented by Oracle and can change between versions/platforms/patches, may depend on block size, datafile size, empty space, etc. So I am looking for "informal" answer applicable in real life. In my tests (done with 8K block size) max extent size was 64M, even for 20 GB table. I saw this value mentioned in several discussions, although Jonathan Lewis mentions 256M: http://www.jlcomp.demon.co.uk/faq/lmt_losses.html Why do I need an answer: we have several databases that were created with 20+ filesystems, 50+ tablespaces, many tablespaces between 1GB and 3 GB, and with random distribution of datafiles between filesystems. Now the filesystems are 98% or so full, AUTOEXTEND has been disabled, and I need to monitor next extent size as extent allocation in an LMT can fail even when tablespace has 15% free space. Thanks |
IMHO 'System' LMT's exist only to avoid DBA extra work over personal/
development/nonimportant databases. On a production system you should
follow the 'uniform' way.
HTH.
Cheers.
Carlos.
#4
| |||
| |||
|
|
What is the maximum extent size possible in locally managed tablespaces (LMT) with autoallocate? Let me clarify the question: I am interested in conventional datafiles (not bigfiles). I understand that "strictly speaking" this question does not have an answer as extent allocation in LMT isn't documented by Oracle and can change between versions/platforms/patches, may depend on block size, datafile size, empty space, etc. So I am looking for "informal" answer applicable in real life. In my tests (done with 8K block size) max extent size was 64M, even for 20 GB table. I saw this value mentioned in several discussions, although Jonathan Lewis mentions 256M: http://www.jlcomp.demon.co.uk/faq/lmt_losses.html Why do I need an answer: we have several databases that were created with 20+ filesystems, 50+ tablespaces, many tablespaces between 1GB and 3 GB, and with random distribution of datafiles between filesystems. Now the filesystems are 98% or so full, AUTOEXTEND has been disabled, and I need to monitor next extent size as extent allocation in an LMT can fail even when tablespace has 15% free space. Thanks |
IMHO 'System' LMT's exist only to avoid DBA extra work over personal/
development/nonimportant databases. On a production system you should
follow the 'uniform' way.
HTH.
Cheers.
Carlos.
#5
| |||
| |||
|
|
What is the maximum extent size possible in locally managed tablespaces (LMT) with autoallocate? Let me clarify the question: I am interested in conventional datafiles (not bigfiles). I understand that "strictly speaking" this question does not have an answer as extent allocation in LMT isn't documented by Oracle and can change between versions/platforms/patches, may depend on block size, datafile size, empty space, etc. So I am looking for "informal" answer applicable in real life. In my tests (done with 8K block size) max extent size was 64M, even for 20 GB table. I saw this value mentioned in several discussions, although Jonathan Lewis mentions 256M: http://www.jlcomp.demon.co.uk/faq/lmt_losses.html Why do I need an answer: we have several databases that were created with 20+ filesystems, 50+ tablespaces, many tablespaces between 1GB and 3 GB, and with random distribution of datafiles between filesystems. Now the filesystems are 98% or so full, AUTOEXTEND has been disabled, and I need to monitor next extent size as extent allocation in an LMT can fail even when tablespace has 15% free space. Thanks |
IMHO 'System' LMT's exist only to avoid DBA extra work over personal/
development/nonimportant databases. On a production system you should
follow the 'uniform' way.
HTH.
Cheers.
Carlos.
#6
| |||
| |||
|
|
What is the maximum extent size possible in locally managed tablespaces (LMT) with autoallocate? Let me clarify the question: I am interested in conventional datafiles (not bigfiles). I understand that "strictly speaking" this question does not have an answer as extent allocation in LMT isn't documented by Oracle and can change between versions/platforms/patches, may depend on block size, datafile size, empty space, etc. So I am looking for "informal" answer applicable in real life. In my tests (done with 8K block size) max extent size was 64M, even for 20 GB table. I saw this value mentioned in several discussions, although Jonathan Lewis mentions 256M: http://www.jlcomp.demon.co.uk/faq/lmt_losses.html Why do I need an answer: we have several databases that were created with 20+ filesystems, 50+ tablespaces, many tablespaces between 1GB and 3 GB, and with random distribution of datafiles between filesystems. Now the filesystems are 98% or so full, AUTOEXTEND has been disabled, and I need to monitor next extent size as extent allocation in an LMT can fail even when tablespace has 15% free space. Thanks |
IMHO 'System' LMT's exist only to avoid DBA extra work over personal/
development/nonimportant databases. On a production system you should
follow the 'uniform' way.
HTH.
Cheers.
Carlos.
#7
| |||
| |||
|
|
What is the maximum extent size possible in locally managed tablespaces (LMT) with autoallocate? Let me clarify the question: I am interested in conventional datafiles (not bigfiles). I understand that "strictly speaking" this question does not have an answer as extent allocation in LMT isn't documented by Oracle and can change between versions/platforms/patches, may depend on block size, datafile size, empty space, etc. So I am looking for "informal" answer applicable in real life. In my tests (done with 8K block size) max extent size was 64M, even for 20 GB table. I saw this value mentioned in several discussions, although Jonathan Lewis mentions 256M: http://www.jlcomp.demon.co.uk/faq/lmt_losses.html Why do I need an answer: we have several databases that were created with 20+ filesystems, 50+ tablespaces, many tablespaces between 1GB and 3 GB, and with random distribution of datafiles between filesystems. Now the filesystems are 98% or so full, AUTOEXTEND has been disabled, and I need to monitor next extent size as extent allocation in an LMT can fail even when tablespace has 15% free space. Thanks |
IMHO 'System' LMT's exist only to avoid DBA extra work over personal/
development/nonimportant databases. On a production system you should
follow the 'uniform' way.
HTH.
Cheers.
Carlos.
#8
| |||
| |||
|
|
What is the maximum extent size possible in locally managed tablespaces (LMT) with autoallocate? Let me clarify the question: I am interested in conventional datafiles (not bigfiles). I understand that "strictly speaking" this question does not have an answer as extent allocation in LMT isn't documented by Oracle and can change between versions/platforms/patches, may depend on block size, datafile size, empty space, etc. So I am looking for "informal" answer applicable in real life. In my tests (done with 8K block size) max extent size was 64M, even for 20 GB table. I saw this value mentioned in several discussions, although Jonathan Lewis mentions 256M: http://www.jlcomp.demon.co.uk/faq/lmt_losses.html Why do I need an answer: we have several databases that were created with 20+ filesystems, 50+ tablespaces, many tablespaces between 1GB and 3 GB, and with random distribution of datafiles between filesystems. Now the filesystems are 98% or so full, AUTOEXTEND has been disabled, and I need to monitor next extent size as extent allocation in an LMT can fail even when tablespace has 15% free space. Thanks |
IMHO 'System' LMT's exist only to avoid DBA extra work over personal/
development/nonimportant databases. On a production system you should
follow the 'uniform' way.
HTH.
Cheers.
Carlos.
#9
| |||
| |||
|
|
On 13 jun, 11:31, Vsevolod Afanassiev <vafanass... (AT) yahoo (DOT) com> wrote: What is the maximum extent size possible in locally managed tablespaces (LMT) with autoallocate? Let me clarify the question: I am interested in conventional datafiles (not bigfiles). I understand that "strictly speaking" this question does not have an answer as extent allocation in LMT isn't documented by Oracle and can change between versions/platforms/patches, may depend on block size, datafile size, empty space, etc. So I am looking for "informal" answer applicable in real life. In my tests (done with 8K block size) max extent size was 64M, even for 20 GB table. I saw this value mentioned in several discussions, although Jonathan Lewis mentions 256M: http://www.jlcomp.demon.co.uk/faq/lmt_losses.html Why do I need an answer: we have several databases that were created with 20+ filesystems, 50+ tablespaces, many tablespaces between 1GB and 3 GB, and with random distribution of datafiles between filesystems. Now the filesystems are 98% or so full, AUTOEXTEND has been disabled, and I need to monitor next extent size as extent allocation in an LMT can fail even when tablespace has 15% free space. Thanks I think you need 'uniform' LMT tablespaces. IMHO 'System' LMT's exist only to avoid DBA extra work over personal/ development/nonimportant databases. On a production system you should follow the 'uniform' way. HTH. Cheers. Carlos.- Hide quoted text - - Show quoted text - |
commonly used in production environments especially to house vendor
packages.
The largest extent size I have seen or heard of at least through 10g
is 64M.
64K, 1M, 8M, 64M are the stardard sizes used in tablespace created
using auto-allocate. Other extent sizes are possilbe due to left-
over space at the end of files.
For tablespace converted to auto-allocate all kind of extent sizes are
possible and pctincrease has an effect.
I also prefer uniform extent sizes from a space management point of
view.
HTH -- Mark D Powell --
#10
| |||
| |||
|
|
On 13 jun, 11:31, Vsevolod Afanassiev <vafanass... (AT) yahoo (DOT) com> wrote: What is the maximum extent size possible in locally managed tablespaces (LMT) with autoallocate? Let me clarify the question: I am interested in conventional datafiles (not bigfiles). I understand that "strictly speaking" this question does not have an answer as extent allocation in LMT isn't documented by Oracle and can change between versions/platforms/patches, may depend on block size, datafile size, empty space, etc. So I am looking for "informal" answer applicable in real life. In my tests (done with 8K block size) max extent size was 64M, even for 20 GB table. I saw this value mentioned in several discussions, although Jonathan Lewis mentions 256M: http://www.jlcomp.demon.co.uk/faq/lmt_losses.html Why do I need an answer: we have several databases that were created with 20+ filesystems, 50+ tablespaces, many tablespaces between 1GB and 3 GB, and with random distribution of datafiles between filesystems. Now the filesystems are 98% or so full, AUTOEXTEND has been disabled, and I need to monitor next extent size as extent allocation in an LMT can fail even when tablespace has 15% free space. Thanks I think you need 'uniform' LMT tablespaces. IMHO 'System' LMT's exist only to avoid DBA extra work over personal/ development/nonimportant databases. On a production system you should follow the 'uniform' way. HTH. Cheers. Carlos.- Hide quoted text - - Show quoted text - |
commonly used in production environments especially to house vendor
packages.
The largest extent size I have seen or heard of at least through 10g
is 64M.
64K, 1M, 8M, 64M are the stardard sizes used in tablespace created
using auto-allocate. Other extent sizes are possilbe due to left-
over space at the end of files.
For tablespace converted to auto-allocate all kind of extent sizes are
possible and pctincrease has an effect.
I also prefer uniform extent sizes from a space management point of
view.
HTH -- Mark D Powell --
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.