Query to shrink Datafiles and Reclaim unused Space in Oracle
Query to shrink Datafiles and Reclaim unused Space in Oracle
Steps to Shrink the datafiles
set verify off
set pages 1000
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
Step1: To Check Database block size
column value new_val blksize
select value from v$parameter where name = 'db_block_size';
Step2: Check how much space can be reclaimed
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(+);
You can also add condition if needs to be checked for a specific tablespace
Step3: Script to reclaim unused space from the datafiles
set pages 0
set lines 300
column cmd format a300 word_wrapped
select 'alter database datafile '''||file_name||''' resize ' ||
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd
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 ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0;
Subscribe to:
Post Comments
(
Atom
)
No comments :
Post a Comment