Resize A Datafile

In order to release unused space in the database, we have to resize the appropriate datafiles by lowering the high water mark level till the unused space. Whenever we delete a set of data, the deleted data is not released to the OS immediately. It will still be hanging around as unused space in the form of fragmented data. These fragmented data may not be distributed continuously and may be random. Hence, we can reduce the high water mark level until the last fragmented block only. Below query can be used to find until where the high water mark level can be brought down. The resized data block will be released back to OS.

SELECT 'alter database datafile ''' || file_name || ''' resize ' || CEIL ( (NVL (hwm, 1) * &&blksize) / 1024 / 1024) || 'm;' cmd

  FROM dba_data_files a,

       (  SELECT file_id, MAX (block_id + blocks - 1) hwm

            FROM dba_extents

        GROUP BY file_id) b

 WHERE a.TABLESPACE_NAME='&TABLESPACE_NAME' and a.file_id = b.file_id(+) AND CEIL (blocks * &&blksize / 1024 / 1024) - CEIL ( (NVL (hwm, 1) * &&blksize) / 1024 / 1024) > 0 order by 1;

Output :

Input the db_block_size and the tablespace name:

CMD

-----------------------------------------------------------------------------------

alter database datafile '+DEV_DG1/dev/datafile/d_s_mall.273.959572767' resize 2m;