Temporary tablespace guidelines
Posted by Pavan DBA on March 21, 2013
I thought to share some points a DBA need to follow while configuring temp tablespaces
1. when ever we create a user along with default permanent tablespace, it is beneficial to specify temporary tablespace also. otherwise, oracle will use that permanent tablespace for any sort operations performed by user and this will be costlier
2. it would be better to create multiple temporary tablespaces for multiple users. for example, create 2 temporary tablespaces and assign them to 10 users so that 5 users can use one temp tablespace (numbers are just for understanding)
3. always have tempfiles spread across multiple disks
4. If you are specifying storage parameters, please make sure INITIAL and NEXT values are same. Maintaining uniform extent allocation is always better for temp tablespaces
5. when using storage parameters, please don’t specify INITIAL and NEXT values too small. This will make oracle to create hundreds of small temp segments and SMON will take quite long time (sometimes may be hours of time also) to clean up those segments.
6. If possible, try to load data into table in ascending order of indexed column. If you store data like that, you can use NOSORT clause while creating index and this will improve index creation performance.
Pavan DBA's Blog 
weź chwilkę Przerwy i Poczytaj - kosmetyki dla Ciebie said
Very nice article, just what I was looking for.
sivakumar said
Good Evening sir, As per my knowledge, from 10g onward s, there will be a new option of temporary tablespace groups, we can use for multiple users can be have one temporary tablespace group, but am not sure, how many users can have a temp table space group,but it would be the best option to the user for sort operations, if more than pga also, it works, if am wrong please correct me.
Pavan DBA said
you are correct siva. Agreed. temp tablespace group can also be used. But the guidelines I specified are irrespective of version, that is why in my post I have not mentioned any version