Settings for Temporary Tablespaces ?

[MMCP]

Hi,everybody.
Our Temporary TS is 300 MB size,INITIAL=NEXT=SORT_AREA_SIZE = 1MB,with 2 Datafiles of 150 MB (each)and the AUTOEXTENT disabled. Have NT 4 and RAID 5.

PROBLEM we have: we are receiving occasionally,several times a day the message:
"ORA-1652:unable to extend temp segment by 128 in tablespace TEMPORARY_DATA"

NEED YOUR OPINION ABOUT:
I'd like to know,if the followings features are right when you define a Temporary TS:
1. The INITIAL=NEXT and must be equal or multiple of sort_area_size ?
2. Must MAXEXTENTS "have a limit" or be "Unlimited" ?
3. Is right to enable AUTOEXTENT for the datafiles of the Temp TS or give them a fixed size? What's better ?
If AUTOEXTENT is used, when the instance is shutdown and then startup,these datafiles return to their original sizes?

What is your best reccomendation for me ?

Thanks in advance.

[Sharma]

You are right on No. 1.

Doesn't matter to have maxextent or unlimited as nothing is permanent in that Tablespace. Better to have high MAXEXTENTS.

Autoextend is alright as long as you have MAXSIZE of some value.

I think the file size will not reduce after you recycle the database, but I have Never Tried it.

[Prince]

ORA-01652 unable to extend temp segment by string in tablespace string
Cause: Failed to allocate an extent for temporary segment in tablespace.
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.


Duration creation of Temporary Tablespac

Uniform size= sort_area_size * n

[nagarjuna]

Originally posted by MMCP
Hi,everybody.
Our Temporary TS is 300 MB size,INITIAL=NEXT=SORT_AREA_SIZE = 1MB,with 2 Datafiles of 150 MB (each)and the AUTOEXTENT disabled. Have NT 4 and RAID 5.

PROBLEM we have: we are receiving occasionally,several times a day the message:
"ORA-1652:unable to extend temp segment by 128 in tablespace TEMPORARY_DATA"


What is your best reccomendation for me ?

Thanks in advance.

What is the size of your database and size of frequently used segments??? and what are the queries that cause huge amount of sort segments??? If the size of temp tablespace is comparable to other sizes (db and segment sizes), then tune SQL queries. If temp tablespace is very small compared with db size, then increase the size of temp tablespace either by adding a tempfile or increase the size of existings files... the conclusion is... "it depends" ... solution depends on your system..