LLI Wiki — My Digital Knowledge Base

Oracle: shrink temporary files to their useful size

Compute the high water mark of Oracle tempfiles and generate the ALTER DATABASE TEMPFILE ... RESIZE statements to reclaim unused space.

Oracle temporary files (tempfiles) do not return to their useful size after a peak of activity. The queries below compare the current size of each file with its smallest useful size, the high water mark (HWM), and then generate the resize statements. The method is taken from pavandba.com.

Get the block size

The queries use the database block size, stored in the substitution variable blksize.

column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/

Show the current and minimum sizes

select file_name,
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
       ceil( blocks*&&blksize/1024/1024) currsize,
       ceil( blocks*&&blksize/1024/1024) - ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_temp_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)
/
Column Meaning
smallest Smallest size of the file in MB, based on the HWM
currsize Current size of the file in MB
savings Space that can be reclaimed, in MB

Generate the resize statements

This query keeps only the files whose savings is greater than 0.

column cmd format a75 word_wrapped

select
   'alter database tempfile "' || file_name || '" resize ' || ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )  || 'm;' cmd
from dba_temp_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)
  and ceil( blocks*&&blksize/1024/1024) - ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
/

Example of a generated statement:

alter database tempfile '/path/to/temp01.dbf' resize 30844m;

Review the generated statements before running them.

Mastodon LLIMastodon SocialFosstodon