Showing results for 
Search instead for 
Did you mean: 

In 9i which tablespace should I choose ?

Occasional Advisor

In 9i which tablespace should I choose ?

In 9i which tablespace should I choose ? I choose LMT the "AUTOALLOCATE option" or "UNIFORM SIZE"? Thanks!!

Andreas D. Skjervold
Honored Contributor

Re: In 9i which tablespace should I choose ?


Depends, but be careful with the UNIFORM clause.
Autoallocate is the "Old" behavior from Oracle where you set the storage clause in the tablespace and segments created inherits these values unless storage clause set on the segment itself.
The dangers of autoallocate is getting the tablespace fragmented as a result of different sized object getting created and erased.
To avoid this you can apply the SAFE standard described in an Oracle Whitepaper to get the segments uniform sized.
You can use the UNIFORM clause at the tablespace. This allocates the set size for each segment nomatter the segments size. So if you set UNIFORM to 1M and create a 10k segment you'll end up using 1M of space in the tablespace. When the semgent grows in extents it does so whitin the allocated 1M size until size extends 1M and a new UNIFORM space chunck is set aside to this segment.

Hope this clears things up.

Only by ignoring what everyone think is important, can you be aware of what everyone ignores!

Re: In 9i which tablespace should I choose ?

The Oracle documentation is not very clear about this issue. We also need explanations for values in the dba_tablespace view and had posted a query on Oracle metalink.

This is the response we got for ALLOCATION_TYPE :
1) UNIFORM = every extent is the same size
2) SYSTEM = the system decices, first 8 or so extents will be 64k, then the next 8 or so will be 1m etc
3) USER = this is a tablespace that was once a dictionary tspace and has been migrated with DBMS_SPACE_ADMIN. Extent sizes are unchanged from before.