Tablespaces -advisors

[badrinathn]

Hi,
This is another question to clarifing my self on Tablespaces and datafiles.

In one of the threads: Pando says:
I always create as follows

create tablespace XYZ
datafile '/u01/prod/XYZ.DBF' XXMB autoextend on next 1028K maxsize YYMB
minimum extent 128K
default storage(
initial 128K
next 128K
minextents 1
maxextents 4096
pctincrease 0
)
****
Can I always follow a convention that(Mine is a medium size database),
I create tablespaces with default values of 128K initial,
128 K next , minextents 128K and maxextents unlimited,pctincrease 0... parameters
& datafile of the calculated size.(may be 2-3GB as designed)


and specify my storage parameters at the time of creating the objecs(tables/indexes.).

Here when I am creating a table( for eg:1MB then about 8 continuous extents will be allocated) .

Please advise

[sambavan]

What is the environment that you are working. Win/Unix. If its win, then your datafile can be at most 2GB max. On Unix, depending on the version, the size would differ. So as a safty cap, stop your datafile growth at 2GB. One other thing is try to create the table space as locally managed and have even number of extents. This would reduce fragmentation.

Sam

[pando]

Well if you use minimum extent 128K no matter what initial next you are specifying in you table/index creation storage parameters they will ALWAYS be multiple of 128K.
For example if you set initial of a table 400K and next 200K Oracle will actually allocate 512K and 256K the advantage of this is you always get same multiple size extents therefore you tend to get uniform extent sizes.

And if I was using 32bit OS I would forget about 3GB file since 2 to 32 is 2GB, size limit for 32 bit OS