How to reshrink tablespace to reclaim the file system/Diskgroup free space

This doc describes how to resize datafiles to reclaim file system or diskgroups free space.

Verify Diskgroup usage,Database physical size and tablespaces sizes before reshrink

SQL> select name,open_mode from v$database;

NAME   OPEN_MODE
--------- -----------
TEST1   READ WRITE

Check Status of DB Size and diskgroups  before reshrink:

SQL> select sum(bytes/1024/1024/1024) from dba_data_files;

SUM(BYTES/1024/1024/1024)
-------------------------
               187.489258

SQL> select name,total_mb/1024,free_mb/1024 from v$asm_diskgroup;

NAME                           TOTAL_MB/1024 FREE_MB/1024
------------------------------ ------------- ------------
ARCHIVE                                  200   188.091797
DATA                                    1600   171.867188
LOG                                       10   2.59667969
OCR                                       10   9.94824219


SQL>set linesize 400
SELECT tablespace_name, round(size_mb/1024) as ALLOC_SIZE_GB ,
round(free_mb/1024) as ALLOC_FREE_GB,round(max_size_mb/1024) as MAX_SIZE_GB,
round(max_free_mb/1024) as MAX_FREE_GB,
       TRUNC((max_free_mb/max_size_mb) * 100) AS free_pct,
       (max_size_mb-max_free_mb)/max_size_mb*100 AS used_pct
FROM   (SELECT a.tablespace_name,b.size_mb,a.free_mb,b.max_size_mb, a.free_mb + (b.max_size_mb - b.size_mb) AS max_free_mb
        FROM (SELECT tablespace_name,TRUNC(SUM(bytes)/1024/1024) AS free_mb FROM dba_free_space GROUP BY tablespace_name) a,
             (SELECT tablespace_name,TRUNC(SUM(bytes)/1024/1024) AS size_mb,TRUNC(SUM(GREATEST(bytes,maxbytes))/1024/1024) AS max_size_mb
                FROM   dba_data_files GROUP BY tablespace_name) b
        WHERE  a.tablespace_name = b.tablespace_name) ORDER BY used_pct asc;

TABLESPACE_NAME ALLOC_SIZE_GB ALLOC_FREE_GB MAX_SIZE_GB MAX_FREE_GB   FREE_PCT USED_PCT
--------------- ------------- ------------- ----------- ------------  --------  ----------
USERS 120       8            120 8        88 12.1220703
INDX 7       4           7 4        58 41.4760045
SYSTEM 20       10      20 10        50 49.9023438
SYSAUX 5             2           5 2        31 68.1054688
UNDOTBS1        35       1         35 1               1 98.3761161

In our test case, We are resizing USERS tablespace datafiles to reclaim space.

SQL> col cmd for a100
set linesize 400
select bytes/1024/1024/1024 real_size, ceil( (nvl(hwm,1)*8192)/1024/1024/1024 ) shrinked_size,
 bytes/1024/1024/1024-ceil( (nvl(hwm,1)*8192)/1024/1024/1024 ) released_size,
 'alter database datafile '|| ''''||file_name||'''' || ' resize ' || ceil( (nvl(hwm,1)*8192)/1024/1024/1024 ) || 'G;' cmd from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) b Where tablespace_name='USERS' and
a.file_id = b.file_id(+)  and ceil(blocks*8192/1024/1024/1024)- ceil((nvl(hwm,1)* 8192)/1024/1024/1024 ) > 0;  


 REAL_SIZE SHRINKED_SIZE RELEASED_SIZE  CMD
---------- ------------- -------------  -----------------------------------------------------------------------
30         5             25             alter database datafile '+DATA/TEST1/DATAFILE/users.287.993935637' resize 5G;
30         5             25             alter database datafile '+DATA/TEST1/DATAFILE/users.263.997178879' resize 5G;
30    5          25             alter database datafile '+DATA/TEST1/DATAFILE/users.258.997178689' resize 5G;
30    5          25             alter database datafile '+DATA/TEST1/DATAFILE/users.300.101263060' resize 5G;

Resize users tablespace datafiles by executing above alter statements

SQL> alter database datafile '+DATA/TEST1/DATAFILE/users.287.993935637' resize 5G;

Database altered.

SQL> alter database datafile '+DATA/TEST1/DATAFILE/users.263.997178879' resize 5G;

Database altered.

SQL> alter database datafile '+DATA/TEST1/DATAFILE/users.258.997178689' resize 5G;

Database altered.

SQL> alter database datafile '+DATA/TEST1/DATAFILE/users.300.101263060' resize 5G;

Database altered.


Check status after resizing datafiles

SQL> set linesize 400
SELECT tablespace_name, round(size_mb/1024) as ALLOC_SIZE_GB ,
round(free_mb/1024) as ALLOC_FREE_GB,round(max_size_mb/1024) as MAX_SIZE_GB,
round(max_free_mb/1024) as MAX_FREE_GB,
       TRUNC((max_free_mb/max_size_mb) * 100) AS free_pct,
       (max_size_mb-max_free_mb)/max_size_mb*100 AS used_pct
FROM   (SELECT a.tablespace_name,b.size_mb,a.free_mb,b.max_size_mb, a.free_mb + (b.max_size_mb - b.size_mb) AS max_free_mb
        FROM (SELECT tablespace_name,TRUNC(SUM(bytes)/1024/1024) AS free_mb FROM dba_free_space GROUP BY tablespace_name) a,
             (SELECT tablespace_name,TRUNC(SUM(bytes)/1024/1024) AS size_mb,TRUNC(SUM(GREATEST(bytes,maxbytes))/1024/1024) AS max_size_mb
                FROM   dba_data_files GROUP BY tablespace_name) b
        WHERE  a.tablespace_name = b.tablespace_name) ORDER BY used_pct asc;


TABLESPACE_NAME ALLOC_SIZE_GB ALLOC_FREE_GB MAX_SIZE_GB MAX_FREE_GB   FREE_PCT USED_PCT
--------------- ------------- ------------- ----------- ------------  --------  ----------
USERS 20       8             20 8        60  40.1220703
INDX 7       4           7 4        58  41.4760045
SYSTEM 20       10      20 10        50  49.9023438
SYSAUX 5             2           5 2        31  68.1054688
UNDOTBS1        35       1          35 1               1 98.3761161

Verify Diskgroup usage and DB Physical size after reshrink

SQL> select sum(bytes/1024/1024/1024) from dba_data_files;

SUM(BYTES/1024/1024/1024)
-------------------------
               87.489258

SQL> select name,total_mb/1024,free_mb/1024 from v$asm_diskgroup;

NAME                           TOTAL_MB/1024 FREE_MB/1024
------------------------------ ------------- ------------
ARCHIVE                                  200   188.091797
DATA                                    1600   271.867188
LOG                                       10   2.59667969
OCR                                       10   9.94824219