We sometimes have shrink datafile in oracle to reclaim filesystem space.We can do it like
Alter database datafile <datafile name > resize <smaller size>;
Many times we may get the below error as the extent may spread out on the outer of the file
ORA-03297: file contains used data beyond requested RESIZE value”.
To resolve the error,We can find the true resize value for all the data-files in the tablespace by the below procedure. This basically tells for the high water mark for the tablespace
Script is meant for Oracle version 8i and higher
set serveroutput on
exec dbms_output.enable(1000000);
declare
cursor c_dbfile is
select f.tablespace_name,f.file_name,f.file_id,f.blocks,t.block_size
,decode(t.allocation_type,'UNIFORM',t.initial_extent/t.block_size,0) uni_extent
,decode(t.allocation_type,'UNIFORM',(128+(t.initial_extent/t.block_size)),128) file_min_size
from dba_data_files f,
dba_tablespaces t
where f.tablespace_name = t.tablespace_name
and t.tablespace_name='&1'
order by f.tablespace_name,f.file_id;
cursor c_freespace(v_file_id in number) is
select block_id, block_id+blocks max_block
from dba_free_space
where file_id = v_file_id
order by block_id desc;
/* variables to check settings/values /
dummy number;
checkval varchar2(10);
block_correction number; / running variable to show (possible) end-of-file /
file_min_block number; / variables to check if recycle_bin is on and if extent as checked is in … /
recycle_bin boolean:=false;
extent_in_recycle_bin boolean; / exception handler needed for non-existing tables note:344940.1 /
sqlstr varchar2(100);
table_does_not_exist exception;
pragma exception_init(table_does_not_exist,-942); / variable to spot space wastage in datafile of uniform tablespace /
space_wastage number; begin / recyclebin is present in Oracle 10.2 and higher and might contain extent as checked /
begin
select value into checkval from v$parameter where name = 'recyclebin';
if checkval = 'on'
then
recycle_bin := true;
end if;
exception
when no_data_found
then
recycle_bin := false;
end; / main loop /
for c_file in c_dbfile
loop
/ initialization of loop variables /
dummy :=0;
extent_in_recycle_bin := false;
file_min_block := c_file.blocks; begin space_wastage:=0; / reset for every file check / <> for c_free in c_freespace(c_file.file_id)
loop
/ if blocks is an uneven value there is a need to correct
with -1 to compare with end-of-file which is even /
block_correction := (0-mod(c_free.max_block,2));
if file_min_block = c_free.max_block+block_correction
then / free extent is at end so file can be resized /
file_min_block := c_free.block_id; / Uniform sized tablespace check if space at end of file
is less then uniform extent size /
elsif (c_file.uni_extent !=0) and ((c_file.blocks - c_free.max_block) < c_file.uni_extent)
then / uniform tablespace which has a wastage of space in datafile
due to fact that space at end of file is smaller than uniform extent size / space_wastage:=c_file.blocks - c_free.max_block;
file_min_block := c_free.block_id; else
/ no more free extent at end of file, file cannot be further resized /
exit check_free;
end if;
end loop;
end; / check if file can be resized, minimal size of file 128 {+ initial_extent} blocks /
if (file_min_block = c_file.blocks) or (c_file.blocks <= c_file.file_min_size)
then dbms_output.put_line('Tablespace: '||c_file.tablespace_name||' Datafile: '||c_file.file_name);
dbms_output.put_line('cannot be resized no free extents found');
dbms_output.put_line('.'); else / file needs minimal no of blocks which does vary over versions,
using safe value of 128 {+ initial_extent} /
if file_min_block < c_file.file_min_size then file_min_block := c_file.file_min_size; end if; dbms_output.put_line('Tablespace: '||c_file.tablespace_name||' Datafile: '||c_file.file_name); dbms_output.put_line('current size: '||(c_file.blocksc_file.block_size)/1024||'K'||' can be resized to: '||round((file_min_blockc_file.block_size)/1024)||'K (reduction of: '||round(((c_file.blocks-file_min_block)/c_file.blocks)100,2)||' %)'); /* below is only true if recyclebin is on / if recycle_bin then begin sqlstr:='select distinct 1 from recyclebin$ where file#='||c_file.file_id; execute immediate sqlstr into dummy; if dummy > 0
then dbms_output.put_line('Extents found in recyclebin for above file/tablespace');
dbms_output.put_line('Implying that purge of recyclebin might be needed in order to resize');
dbms_output.put_line('SQL> purge tablespace '||c_file.tablespace_name||';');
end if;
exception
when no_data_found
then null;
when table_does_not_exist
then null;
end;
end if;
dbms_output.put_line('SQL> alter database datafile '''||c_file.file_name||''' resize '||round((file_min_blockc_file.block_size)/1024)||'K;');
if space_wastage!=0
then
dbms_output.put_line('Datafile belongs to uniform sized tablespace and is not optimally sized.');
dbms_output.put_line('Size of datafile is not a multiple of NN uniform_extent_size + overhead');
dbms_output.put_line('Space that cannot be used (space wastage): '||round((space_wastagec_file.block_size)/1024)||'K');
dbms_output.put_line('For optimal usage of space in file either resize OR increase to: '||round(((c_file.blocks+(c_file.uni_extent-space_wastage))*c_file.block_size)/1024)||'K');
end if;
dbms_output.put_line('.');
end if;
end loop;
end;
/
Script is meant for Oracle version 8 and lower
REM Script is meant for Oracle version 8 and lower
REM ----------------------------------------------
set serveroutput on
exec dbms_output.enable(1000000);
declare
cursor c_dbfile is
select f.tablespace_name,f.file_name,f.file_id,f.blocks
from dba_data_files f,
dba_tablespaces t
where f.tablespace_name = t.tablespace_name
and t.status = 'ONLINE'
order by f.tablespace_name,f.file_id;
cursor c_freespace(v_file_id in number) is
select block_id, block_id+blocks max_block
from dba_free_space
where file_id = v_file_id
order by block_id desc;
/* variables to check settings/values /
block_correction number;
block_size number; / running variable to show (possible) end-of-file /
file_min_block number; begin select value into block_size from v$parameter where name='db_block_size'; / main loop /
for c_file in c_dbfile
loop
/ initialization of loop variables /
file_min_block := c_file.blocks; begin < for c_free in c_freespace(c_file.file_id)
loop
/ if blocks is an uneven value there is a need to correct with -1 to compare with end-of-file which is even /
block_correction := (0-mod(c_free.max_block,2));
if file_min_block = c_free.max_block+block_correction
then / free extent is at end so file can be resized /
file_min_block := c_free.block_id; else
/ no more free extent at end of file, file cannot be further resized /
exit check_free;
end if;
end loop;
end; / check if file can be resized, minimal size of file 16 blocks /
if (file_min_block = c_file.blocks) or (c_file.blocks <= 16)
then dbms_output.put_line('Tablespace: '||c_file.tablespace_name||' Datafile: '||c_file.file_name);
dbms_output.put_line('cannot be resized no free extents found');
dbms_output.put_line('.'); else / file needs minimal no of blocks which does vary over versions /
if file_min_block < 16 then file_min_block := 16; end if; dbms_output.put_line('Tablespace: '||c_file.tablespace_name||' Datafile: '||c_file.file_name); dbms_output.put_line('current size: '||(c_file.blocksblock_size)/1024||'K'||' can be resized to: '||round((file_min_blockblock_size)/1024)||'K (reduction of: '||round(((c_file.blocks-file_min_block)/c_file.blocks)100,2)||' %)'); dbms_output.put_line('SQL> alter database datafile '''||c_file.file_name||''' resize '||round((file_min_block*block_size)/1024)||'K;');
dbms_output.put_line('.');
end if;
end loop;
end;
/
We may still have huge free space in the datafiles even after resizing based on the scripts. In that case, we will need to rebuild few tables or indexes to free up the free space
We can check for highest allocated extent in datafile using the below query and try moving that table or index and then again trying the resize statement
Check highest allocated extent in datafile (likely slow when having many extents)
column file_name format a50;
column tablespace_name format a15;
column highwater format 9999999999;
set pagesize 9999
select a.tablespace_name
,a.file_name
,(b.maximum+c.blocks-1)*d.db_block_size highwater
from dba_data_files a
,(select file_id,max(block_id) maximum
from dba_extents
group by file_id) b
,dba_extents c
,(select value db_block_size
from v$parameter
where name='db_block_size') d
where a.file_id = b.file_id
and c.file_id = b.file_id
and c.block_id = b.maximum
order by a.tablespace_name,a.file_name
/
I hope this will be helpful on how to shrink datafile in oracle. Please do provide the feedback and problems you face while doing this
Related Articles
tablespace in oracle: This article on how to create tablespace in oracle, various characteristics associated with it and different create tablespace statements
ORA-01652 : ORA-01652 error usually because when the tablespace does not have free space in Permanent and Temporary tablespace in oracle database. Check out how to resolve it
how to move lob segment: how to move lob segment from one tablespace to another, how to reclaim space after major deleted in lob segment
alter table move: Check out this post for the step by step method on How to rebuild the table in oracle using alter table move, How to rebuild table having lobs, long column
how to change default tablespace in oracle: Default tablespace is the tablespace where the objects are created when no tablespace name is specified by users. Find out how to check default tablespace
How to check temp tablespace in Oracle: This article is about temp tablespace in Oracle, resize the tempfile, drop the tempfile, find the temp usage by Session
alter tablespace add datafile: Check out this post on How to add a datafile to tablespace in Oracle, add tempfile to the temporary tablespace, how to add datafile in ASM
https://docs.oracle.com/cd/B28359_01/server.111/b28310/tspaces002.htm