Tempfile always with size on the limit

[albertomattos]

Dear colleagues DBA's, I have a question: My bank is always with tempfile at the limit of 32 GB. Is this harmful to the Bank's performance? Or is it normal? I do not see errors in the applications. But whenever I create a new file and exclude the previous one, it quickly grows for 32 GB. Is there any form of this file "suffer a cleaning" from time to time, or just dropping and creating a new one?

[dr_gori]

It is not harmful.
The temp file is used for temporary operations, type, sort, index, distinct, group by, etc.
When it does not fit in memory, he saves there.

After using, it releases for other temporary operations. That's why it grows fast and then at the 32GB limit.

(let's wait for a DBA to answer more technically) : Roll:

[fbifabio]

Being on the use limit from time to time is no problem, the problem is if it is always at the limit, this can affect the performance of many SQLs in the BD. Evaluate the use by running multiple times during the SQL below.

- See Space in use or maximum already used by Tablespace Temp:

Select all

select    a.tablespace_name, b.Total_MB, 
          b.Total_MB - round(a.used_blocks*8/1024) Current_Free_MB, 
          round(used_blocks*8/1024)                Current_Used_MB, 
          round(max_used_blocks*8/1024)             Max_used_MB 
from      v$sort_segment a, 
          (select round(sum(bytes)/1024/1024) Total_MB from dba_temp_files ) b;

Perform a cleaning in it may not help you a lot as it is already recycled automatically, which may need to do if it is in the limit is to increase the size of it or create new temporary tablespaces by assigning them to specific users or creating a group of temporary tablespaces.