Shrink datafile with free space beyond high water mark

There are times where we need to clear the space of the mount point when the database grows.
Sometimes, we run out of space and we may need to wait for storage team to allocate the space.

Below script will be life saving script, where you can shrink the datafiles and save space immediately.

Upon allocation of the storage, you may resize the datafiles again if needed.

SELECT ceil( blocks*(a.BlockSize)/1024/1024) “Current Size”,
   ceil( (nvl(hwm,1)*(a.BlockSize))/1024/1024 ) “Smallest Poss.”,
   ceil( blocks*(a.BlockSize)/1024/1024) –
   ceil( (nvl(hwm,1)*(a.BlockSize))/1024/1024 ) “Savings”,
   ‘alter database datafile ”’|| file_name || ”’ resize ‘ ||
      ceil((nvl(hwm,1)*(a.BlockSize))/1024/1024/100)*100  || ‘m;’ “Command”
FROM (SELECT a.*, p.value BlockSize FROM dba_data_files a
JOIN v$parameter p ON p.Name=’db_block_size’) a
LEFT JOIN (SELECT file_id, max(block_id+blocks-1) hwm FROM dba_extents GROUP BY file_id ) b
ON a.file_id = b.file_id
WHERE ceil( blocks*(a.BlockSize)/1024/1024) – ceil( (nvl(hwm,1)*(a.BlockSize))/1024/1024 )
   > 100 /* Minimum MB it must shrink by to be considered. */
ORDER BY “Savings” Desc;
  • June 30, 2019 | 20 views
  • Comments