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
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