We have an Oracle 11g (11.2.0.4) database that has a temp tablespace with a number of temp files:
SYS@kec1proddb> r
1* select FILE_NAME,BYTES/1024/1024, MAXBYTES/1024/1024 from dba_temp_files order by 1
FILE_NAME BYTES/1024/1024 MAXBYTES/1024/1024
---------------------------------------- --------------- ------------------
/gmtemp/gmtemp01.dbf 1024 0
/gmtemp/gmtemp02.dbf 1024 0
/gmtemp/gmtemp03.dbf 1024 0
/gmtemp/gmtemp04.dbf 1024 0
/gmtemp/gmtemp05.dbf 1024 0
/gmtemp/gmtemp06.dbf 1024 0
/gmtemp/gmtemp07.dbf 1024 0
/gmtemp/gmtemp08.dbf 1024 0
/gmtemp/gmtemp09.dbf 2048 0
/gmtemp/gmtemp10.dbf 2048 0
/gmtemp/gmtemp11.dbf 2048 0
/gmtemp/gmtemp12.dbf 2048 0
/gmtemp/gmtemp13.dbf 2048 0
/gmtemp/gmtemp14.dbf 2048 0
/gmtemp/gmtemp15.dbf 2048 0
/gmtemp/gmtemp16.dbf 2048 0
/gmtemp/gmtemp17.dbf 2048 0
/gmtemp/gmtemp18.dbf 2048 0
/gmtemp/gmtemp19.dbf 2048 0
/gmtemp/gmtemp20.dbf 2048 0
/gmtemp/gmtemp21.dbf 2048 0
21 rows selected.
The above data files total up to 34gb; now, is it better to have one larger file (of 34gb) as appose to 21 individual files? I would imagine certainly yes from a data-file management, but how about temp tablespace usage? Isn't the above more pruned to "ORA-1652: unable to extend temp segment by .." errors if it doesn't find enough contiguous space within a specific temp data file? Yes, one could argue that the same can happen in a one-data-file temp space of a similar size, but I'd imagine it will be less!
Really appreciate all your help.