Default Temporary Tablespace

[M.Shakeel Azeem]

i have checked V$temp_space_header and determine the free bytes and free blocks are 0

i also check that the temp tablespace not set to autoextensible
whether it is beneficial to add temp file in that tablespace or set to autoextensible

I also want to confirm that if a default temporary tablespace has been set at db level and each user also have its default temp tablespace then which tablespace each user will use for sorting

should we go for only one default temporary tablespace at db level
and drop all other temporary tablespace whether are used by the users as their temporary tablespace

[cis_groupie]

TEMP tablespaces should not be set to AUTOEXTEND; they way that they work is that they don't free up space unless needed, so if you set one to autoextend it will just keep on growing & growing, whereas if you don't then when it gets full it will automatically free up space that is no longer needed. (This means that after it's been used a while it will show as always being full or very nearly full - you can safely ignore this, as it's a quirk of how the TEMP tablespace works.)

I can't think of any good reason why each user should have their own TEMP tablespace, but I guess it depends on the design of your database...