Advertisement

Friday, May 31, 2019

Oracle Database: How much can you shrink tablespace / Size

It's one of the big questions on how much I will be able to shrink my tablespace or my datafiles. 

It is easy just run this and substitute the name of tablespace (currently SYSAUX in bold)
You will find list of files and how much they can be reduced 

set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings  format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report
column value new_val blksize
select value from v$parameter where name = 'db_block_size';
/
select file_name,
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
       ceil( blocks*&&blksize/1024/1024) currsize,
       ceil( blocks*&&blksize/1024/1024) -
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)  and
a.tablespace_name = 'SYSAUX
order by savings desc

/

Output similar to below 


                                                   Smallest
                                                       Size  Current    Poss.
FILE_NAME                                             Poss.     Size  Savings
-------------------------------------------------- -------- -------- --------
+DATA2/chmprod/datafile/sysaux.431.1009335297         9,372   30,720   21,348
+DATA2/chmprod/datafile/sysaux.421.1009334935        29,894   30,720      826
+DATA2/chmprod/datafile/sysaux.299.1009331535        32,269   32,767      498
+DATA2/chmprod/datafile/sysaux.297.1009331535        32,669   32,750       81
+DATA2/chmprod/datafile/sysaux.296.1009331535        32,717   32,750       33
+DATA2/chmprod/datafile/sysaux.422.1009334937        30,690   30,720       30
+DATA2/chmprod/datafile/sysaux.298.1009331535        32,764   32,767        3
+DATA2/chmprod/datafile/sysaux.420.1009334933        30,720   30,720        0
                                                                     --------

                                                   Smallest
                                                       Size  Current    Poss.
FILE_NAME                                             Poss.     Size  Savings
-------------------------------------------------- -------- -------- --------

sum                                                                    22,819

No comments:
Write comments