Re: Tablespaces sizing!

On May 17, 5:02 pm, scorpio1..._at_yahoo.com wrote:
> I wanted to create 2 separate tablespaces for tables and for indexes.
>
> The question is what is the best option for the sizes of these
> tablespaces? And why?
> How can understand that if I wanted to create the different
> application or managing the size of  the tablespaces for the different
> accept.
>
>  For example for the application above I create the tablespaces with
> these options:
>
>   SQL> create tablespace tables
>   2  datafile 'D:\ORACLE\PRODUCT\10.2.0\ORCL2\tables01.dbf'
>   3  size 100m autoextend on maxsize 200m
>   4  extent management local uniform size 100k;
>
> Tablespace created.
>
> SQL> create tablespace indexes
>   2  datafile 'D:\ORACLE\PRODUCT\10.2.0\ORCL2\indexes01.dbf'
>   3  size 100m autoextend on maxsize 200m
>   4  extent management local uniform size 100k;
>
> Tablespace created.
>
> I know this is the just practice; I created as a part of my personal
> server at home but how about the real word? What is going to be the
> best options and why?
>
> Thank you all

Richard Foote authored several excellent articles that strongly suggest that data and the indexes on that data should usually be placed in the same tablespace. Links to those articles are provided below:

http://richardfoote.wordpress.com/2008/04/16/separate-indexes-from-tables-some-thoughts-part-i-everything-in-its-right-place/
http://richardfoote.wordpress.com/2008/04/18/separate-indexes-from-tables-some-thoughts-part-ii-there-there/
http://richardfoote.wordpress.com/2008/04/28/indexes-in-their-own-tablespace-availabilty-advantages-is-there-anybody-out-there/
http://richardfoote.wordpress.com/2008/05/02/indexes-in-their-own-tablespace-recoverability-advantages-get-back/

100KB is an odd size for an extent - values that are a power of 2 are more common: 64KB, 256KB, 1MB, 16MB, etc. If there is the potential for multiple sessions to be inserting, updating, or deleting rows at the same time, consider using ASSM (automatic segment space management), which is also able to create extents that automatically increase in size without introducing tablespace fragmentation. That specification looks like this: "EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO". It is good that you have specified the beginning size of the data files. However, you have not specified how the data files should increase in size up to the 200MB maximum size. Without specifying how the data files should be extended, the data files will extend by the specified extent size - this increases the risk that the data files will become increasingly fragmented at the file system level. Consider using "AUTOEXTEND ON NEXT 40M" (or some other value).

Consider what might happen when the defined maxsize is reached and additional data needs to be stored in the tablespace - are you setting the database up for failure? Consider setting MAXSIZE to a much larger value or specify "MAXSIZE UNLIMITED".

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Sat May 17 2008 - 20:22:35 CDT