Script to resize Oracle Datafiles

Over-allocation of space at the file level affects the backup/recovery window, file checking times and, most  painfully, limits the potential allocation of space to a tablespace that needs the extra room. A simpler solution would be to review the evolution of the script, which lets you know which files can and cannot be resized to create more space.

It's possible to release space from data files but only down to the first block of data. This is done with the 'alter database' command.

The following script allows to calculate the amount of space used by each tablespace

SELECT   tablespace_name, SUM (bytes) bytes_full
    FROM   dba_extents
GROUP BY   tablespace_name;

The following scripts allows to calculate the total space available for each tablespace

SELECT   tablespace_name, SUM (bytes) bytes_total
    FROM   dba_data_files
GROUP BY   tablespace_name;

The following script allows to find the last data block that has been inserted for each file

SELECT   tablespace_name, file_id, MAX (block_id) max_data_block_id
    FROM   dba_extents
GROUP BY   tablespace_name, file_id;

The following allows to find the free space in each file above the last data block inserted

SELECT   a.tablespace_name, a.file_id, b.bytes bytes_free
  FROM   (  SELECT   tablespace_name, file_id, MAX (block_id) max_data_block_id
              FROM   dba_extents
          GROUP BY   tablespace_name, file_id) a, dba_free_space b
 WHERE       a.tablespace_name = b.tablespace_name
         AND a.file_id = b.file_id
         AND b.block_id > a.max_data_block_id;

Finally the following will allow to generate alter statements to resize your datafiles

SELECT      'alter database '
         || a.name
         || ' datafile '''
         || b.file_name
         || ''''
         || ' resize '
         || GREATEST (TRUNC (bytes_full / .7), (bytes_total - bytes_free))
  FROM   v$database a,
         dba_data_files b,
         (  SELECT   tablespace_name, SUM (bytes) bytes_full
              FROM   dba_extents
          GROUP BY   tablespace_name) c,
         (  SELECT   tablespace_name, SUM (bytes) bytes_total
              FROM   dba_data_files
          GROUP BY   tablespace_name) d,
         (SELECT   a.tablespace_name, a.file_id, b.bytes bytes_free
            FROM   (  SELECT   tablespace_name,
                               file_id,
                               MAX (block_id) max_data_block_id
                        FROM   dba_extents
                    GROUP BY   tablespace_name, file_id) a,
                   dba_free_space b
           WHERE       a.tablespace_name = b.tablespace_name
                   AND a.file_id = b.file_id
                   AND b.block_id > a.max_data_block_id) e
 WHERE       b.tablespace_name = c.tablespace_name
         AND b.tablespace_name = d.tablespace_name
         AND bytes_full / bytes_total < .7
         AND b.tablespace_name = e.tablespace_name
         AND b.file_id = e.file_id;