Pavan DBA's Blog

The DBA Knowledge Store

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.

3 Responses to “Temporary tablespace guidelines”

  1. Very nice article, just what I was looking for.

  2. 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

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: