shrink Datafiles and Reclaim unused Space in Oracle

Space issue:-

Found space utilization at data file mount point on prod.

/dev/mapper/datavg-lvol0 404G  365G   19G  96% /saprod_data01   

Avail Use% Mounted on

/dev/mapper/datavg-lvol1 404G  372G   12G  97% /saprod_data02

Solution:-

Step1:- verify 

set linesize 400
col tablespace_name format a15
col file_size format 99999
col file_name format a50
col hwm format 99999
col can_save format 99999
SELECT tablespace_name, file_name, file_size, hwm, file_size-hwm can_save
FROM (SELECT /*+ RULE */ ddf.tablespace_name, ddf.file_name file_name,
ddf.bytes/1048576 file_size,(ebf.maximum + de.blocks-1)*dbs.db_block_size/1048576 hwm
FROM dba_data_files ddf,(SELECT file_id, MAX(block_id) maximum FROM dba_extents GROUP BY file_id) ebf,dba_extents de,
(SELECT value db_block_size FROM v$parameter WHERE name='db_block_size') dbs
WHERE ddf.file_id = ebf.file_id
AND de.file_id = ebf.file_id
AND de.block_id = ebf.maximum
ORDER BY 1,2);

Step 2:- Output of the script

--------------- -------------------------------------------------- --------- ------ --------
BIAPPS_BIACOMP  /saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_b       624    584       40
                iacomp.dbf
BIAPPS_BIA_ODIR /saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_o     18704  17799      905
EPO             di.dbf
BIAPPS_BIPLATFO /saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_b        64     16       48
RM              iplatform.dbf
BIAPPS_DW_DATA  /saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_d     32768  32768        0
                wdata.dbf
TABLESPACE_NAME FILE_NAME                                          FILE_SIZE    HWM CAN_SAVE
--------------- -------------------------------------------------- --------- ------ --------
BIAPPS_DW_DATA  /saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_d     10240  10177       63
                wdata5.dbf
BIAPPS_DW_DATA  /saprod_data02/saprod/BIAPPS_dwdata1.dbf               32704  32697        7
BIAPPS_DW_DATA  /saprod_data02/saprod/BIAPPS_dwdata2.dbf               32704  32641       63
BIAPPS_DW_DATA  /saprod_data02/saprod/BIAPPS_dwdata3.dbf               32704  32704        0
BIAPPS_DW_DATA  /saprod_data02/saprod/BIAPPS_dwdata4.dbf               20480  20480        0
BIAPPS_MDS      /saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_m       100     18       82
                ds.dbf

TABLESPACE_NAME FILE_NAME                                          FILE_SIZE    HWM CAN_SAVE
--------------- -------------------------------------------------- --------- ------ --------
DACREP_PRODDATA /saprod_data01/oracle/base/oradata/SAPROD/dacrep_d      2048    878     1170
                ata01.dbf
DWH_PRODDATA    /saprod_data01/oracle/base/oradata/SAPROD/dwh_data     32768  32767        1
                01.dbf
DWH_PRODDATA    /saprod_data01/oracle/base/oradata/SAPROD/dwh_data     31744  31744        0
                02.dbf
DWH_PRODDATA    /saprod_data01/oracle/base/oradata/SAPROD/dwh_data     31744  31744        0
                03.dbf
TABLESPACE_NAME FILE_NAME                                          FILE_SIZE    HWM CAN_SAVE
--------------- -------------------------------------------------- --------- ------ --------
DWH_PRODDATA    /saprod_data01/oracle/base/oradata/SAPROD/dwh_data     31744  31744        0
                04.dbf
DWH_PRODDATA    /saprod_data01/oracle/base/oradata/SAPROD/dwh_data     31744  31593      151
                05.dbf
DWH_PRODDATA    /saprod_data01/oracle/base/oradata/SAPROD/dwh_data     31744  31744        0
                06.dbf
DWH_PRODDATA    /saprod_data01/oracle/base/oradata/SAPROD/dwh_data     32767  32726       41
TABLESPACE_NAME FILE_NAME                                          FILE_SIZE    HWM CAN_SAVE
--------------- -------------------------------------------------- --------- ------ --------
                07.dbf
DWH_PRODDATA    /saprod_data02/saprod/dwh_data07.dbf                   32767  32767        0
DWH_PRODDATA    /saprod_data02/saprod/dwh_data08.dbf                   32767  32767        0
DWH_PRODDATA    /saprod_data02/saprod/dwh_data09.dbf                   32767  32767        0
DWH_PRODDATA    /saprod_data02/saprod/dwh_data10.dbf                   20480  20480        0
DWH_PRODDATA    /saprod_data02/saprod/dwh_data11.dbf                   20480  20479        1
EXAMPLE         /saprod_data01/oracle/base/oradata/SAPROD/example0       100     81       19
                1.dbf
INFAREP_PRODDAT /saprod_data01/oracle/base/oradata/SAPROD/infarep_      3072   2120      952
TABLESPACE_NAME FILE_NAME                                          FILE_SIZE    HWM CAN_SAVE
--------------- -------------------------------------------------- --------- ------ --------
A               data01.dbf
PROD_BIPLATFORM /saprod_data01/oracle/base/oradata/SAPROD/PROD_bip        64      2       62
                latform.dbf
PROD_MDS        /saprod_data01/oracle/base/oradata/SAPROD/PROD_mds       100      6       94
                .dbf
SYSAUX          /saprod_data01/oracle/base/oradata/SAPROD/sysaux01      3072   1591     1481
                .dbf

TABLESPACE_NAME FILE_NAME                                          FILE_SIZE    HWM CAN_SAVE
--------------- -------------------------------------------------- --------- ------ --------
SYSTEM          /saprod_data01/oracle/base/oradata/SAPROD/system01      5120   3264     1856
                .dbf
UNDOTBS1        /saprod_data01/oracle/base/oradata/SAPROD/undotbs0     23020  22273      747
                1.dbf
USERS           /saprod_data01/oracle/base/oradata/SAPROD/users01.         5      4        1
                dbf

31 rows selected.
SQL>

By the output i can reclaim space around  7.783g of space

Step3:-Run below query 

set linesize 1000 pagesize 0 feedback off trimspool on
with
 hwm as (
  -- get highest block id from each datafiles ( from x$ktfbue as we don't need all joins from dba_extents )
  select /*+ materialize */ ktfbuesegtsn ts#,ktfbuefno relative_fno,max(ktfbuebno+ktfbueblks-1) hwm_blocks
  from sys.x$ktfbue group by ktfbuefno,ktfbuesegtsn
 ),
 hwmts as (
  -- join ts# with tablespace_name
  select name tablespace_name,relative_fno,hwm_blocks
  from hwm join v$tablespace using(ts#)
 ),
 hwmdf as (
  -- join with datafiles, put 5M minimum for datafiles with no extents
  select file_name,nvl(hwm_blocks*(bytes/blocks),5*1024*1024) hwm_bytes,bytes,autoextensible,maxbytes
  from hwmts right join dba_data_files using(tablespace_name,relative_fno)
 )
select
 case when autoextensible='YES' and maxbytes>=bytes
 then -- we generate resize statements only if autoextensible can grow back to current size
  '/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
   ||'M from '||to_char(ceil(bytes/1024/1024),999999)||'M */ '
   ||'alter database datafile '''||file_name||''' resize '||ceil(hwm_bytes/1024/1024)||'M;'
 else -- generate only a comment when autoextensible is off
  '/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
   ||'M from '||to_char(ceil(bytes/1024/1024),999999)
   ||'M after setting autoextensible maxsize higher than current size for file '
   || file_name||' */'
 end SQL
from hwmdf
where
 bytes-hwm_bytes>1024*1024 -- resize only if at least 1MB can be reclaimed
order by bytes-hwm_bytes desc
/

Step4:- Output

SQL

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

/* reclaim    5115M from    5120M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_dwstage.dbf' resize 5M;

/* reclaim    5115M from    5120M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_dwindex.dbf' resize 5M;

/* reclaim    5115M from    5120M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/dwh_index02.dbf' resize 5M;

/* reclaim    2043M from    2048M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/dwh_index01.dbf' resize 5M;

/* reclaim    1857M from    5120M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/system01.dbf' resize 3264M;

/* reclaim    1482M from    3072M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/sysaux01.dbf' resize 1591M;

/* reclaim    1171M from    2048M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/dacrep_data01.dbf' resize 878M;

/* reclaim     953M from    3072M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/infarep_data01.dbf' resize 2120M;

/* reclaim     905M from   18704M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_odi.dbf' resize 17800M;

/* reclaim     748M from   23020M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/undotbs01.dbf' resize 22273M;

/* reclaim     152M from   31744M after setting autoextensible maxsize higher than current size for file /saprod_data01/oracle/base/oradata/SAPROD/dwh_data05.dbf */

SQL

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

/* reclaim      95M from     100M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/PROD_mds.dbf' resize 6M;

/* reclaim      83M from     100M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_mds.dbf' resize 18M;

/* reclaim      64M from   10240M after setting autoextensible maxsize higher than current size for file /saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_dwdata5.dbf */

/* reclaim      64M from   32704M */ alter database datafile '/saprod_data02/saprod/BIAPPS_dwdata2.dbf' resize 32641M;

/* reclaim      63M from      64M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/PROD_biplatform.dbf' resize 2M;

/* reclaim      48M from      64M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_biplatform.dbf' resize 17M;

/* reclaim      42M from   32767M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/dwh_data07.dbf' resize 32726M;

/* reclaim      41M from     624M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_biacomp.dbf' resize 584M;

/* reclaim      19M from     100M */ alter database datafile '/saprod_data01/oracle/base/oradata/SAPROD/example01.dbf' resize 82M;

/* reclaim       8M from   32704M */ alter database datafile '/saprod_data02/saprod/BIAPPS_dwdata1.dbf' resize 32697M;

21 rows selected.

SQL>

Step5:- Resize all datafile using above output.

Step6:-Execute step1 query to check changes after resizing of datafiles.

Tablespace      FILE_NAME                                          FILE_SIZE    HWM CAN_SAVE
--------------- -------------------------------------------------- --------- ------ --------
BIAPPS_BIACOMP  /saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_b       584    584        0
                iacomp.dbf
BIAPPS_BIA_ODIR /saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_o     17800  17799        1
EPO             di.dbf
BIAPPS_BIPLATFO /saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_b        17     16        1
RM              iplatform.dbf
BIAPPS_DW_DATA  /saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_d     32768  32768        0
                wdata.dbf
BIAPPS_DW_DATA  /saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_d     10240  10177       63
                wdata5.dbf
BIAPPS_DW_DATA  /saprod_data02/saprod/BIAPPS_dwdata1.dbf               32761  32697       64
BIAPPS_DW_DATA  /saprod_data02/saprod/BIAPPS_dwdata2.dbf               32705  32641       64
BIAPPS_DW_DATA  /saprod_data02/saprod/BIAPPS_dwdata3.dbf               32704  32704        0
BIAPPS_DW_DATA  /saprod_data02/saprod/BIAPPS_dwdata4.dbf               20480  20480        0
BIAPPS_MDS      /saprod_data01/oracle/base/oradata/SAPROD/BIAPPS_m        18     18        0
                ds.dbf
DACREP_PRODDATA /saprod_data01/oracle/base/oradata/SAPROD/dacrep_d       878    878        0
                ata01.dbf
DWH_PRODDATA    /saprod_data01/oracle/base/oradata/SAPROD/dwh_data     32768  32767        1
                01.dbf
DWH_PRODDATA    /saprod_data01/oracle/base/oradata/SAPROD/dwh_data     31744  31744        0
                02.dbf
DWH_PRODDATA    /saprod_data01/oracle/base/oradata/SAPROD/dwh_data     31744  31744        0
                03.dbf
DWH_PRODDATA    /saprod_data01/oracle/base/oradata/SAPROD/dwh_data     31744  31744        0
                04.dbf
DWH_PRODDATA    /saprod_data01/oracle/base/oradata/SAPROD/dwh_data     31744  31593      151
                05.dbf
DWH_PRODDATA    /saprod_data01/oracle/base/oradata/SAPROD/dwh_data     31744  31744        0
                06.dbf
DWH_PRODDATA    /saprod_data01/oracle/base/oradata/SAPROD/dwh_data     32726  32726        0
                07.dbf
DWH_PRODDATA    /saprod_data02/saprod/dwh_data07.dbf                   32767  32767        0
DWH_PRODDATA    /saprod_data02/saprod/dwh_data08.dbf                   32767  32767        0
DWH_PRODDATA    /saprod_data02/saprod/dwh_data09.dbf                   32767  32767        0
DWH_PRODDATA    /saprod_data02/saprod/dwh_data10.dbf                   20480  20480        0
DWH_PRODDATA    /saprod_data02/saprod/dwh_data11.dbf                   20480  20479        1
EXAMPLE         /saprod_data01/oracle/base/oradata/SAPROD/example0        82     81        1
                1.dbf
INFAREP_PRODDAT /saprod_data01/oracle/base/oradata/SAPROD/infarep_      2120   2120        0
A               data01.dbf
PROD_BIPLATFORM /saprod_data01/oracle/base/oradata/SAPROD/PROD_bip         2      2        0
                latform.dbf
PROD_MDS        /saprod_data01/oracle/base/oradata/SAPROD/PROD_mds         6      6        0
                .dbf
SYSAUX          /saprod_data01/oracle/base/oradata/SAPROD/sysaux01      1731   1591      140
                .dbf
SYSTEM          /saprod_data01/oracle/base/oradata/SAPROD/system01      3264   3264        0
                .dbf
UNDOTBS1        /saprod_data01/oracle/base/oradata/SAPROD/undotbs0     22273  22273        0
                1.dbf
USERS           /saprod_data01/oracle/base/oradata/SAPROD/users01.         5      4        1
                dbf

31 rows selected.
SQL>

In earlier output possible savings is 7.783g , Now possible savings is 356mb, So we reclaimed 7gb of space at OS level

Step6:- check space at os level

/dev/mapper/datavg-lvol0

                      404G  341G   43G  89% /saprod_data01

/dev/mapper/datavg-lvol1

                      404G  360G   24G  94% /saprod_data02

/dev/mapper/datavg-lvol2