Reducing datafile size to recover free space – Oracle Database 10g

At time, we want to recover some space from database just to allocate the same to some other tablespace or to return it back to OS disk. This situation arises many times.
And many time we hit with error “ORA-03297: file contains used data beyond requested RESIZE value“.
The concept is simple and many of you must be knowing, but just putting in a simpler words.
Lets take an example of one of the datafile in a database.
Lets see the total free space in a datafile 194.
SQL> select sum(bytes)/1024/1024 from dba_free_space
2  where tablespace_name = ‘APPS_TS_TX_DATA’
3  and file_id = 194;
SUM(BYTES)/1024/1024
——————–
1844.125
Now lets see the distribution for the file.
SQL> select file_id, block_id, blocks, bytes,  ‘Free’ from dba_free_space
2  where tablespace_name = ‘APPS_TS_TX_DATA’
3  and file_id = 194
4  and rownum < 7
5  order by block_id desc;
   FILE_ID   BLOCK_ID     BLOCKS      BYTES ‘FRE
———- ———- ———- ———- —-
194      35001     220992 1810366464 Free
194      13433         16     131072 Free
194      13417         16     131072 Free
194      13401         16     131072 Free
194      13385         16     131072 Free
194      13369         16     131072 Free
We can see that there are so many blocks which are free.  Its divided into chunks of 16 Blocks and each of these chunks are given a block ID. Now we can see that this formating is done until block 35001 and after that there is no partitions. Beyond the block ID 35001, the whole space is available as a single large unit. This is because of high water mark. When a object is created, it will be created physically in the datafile and will be allocated a block. The number of blocks it will be allocated will depend on the parameter “INITIAL EXTENT” which can be given at the time of creating an object. If we dont give this parameter it will take a default value of 16. So 16 block gets assigned when you create any abject, example a table.
You might be wondering that after block 35001, we have all free space and also we have free space at blocks 13401, 13417, 13433 etc. But where are the blocks between 13433 and 35001??
The answer can be found from dba_extents. All the blocks between 13433 and 35001 are occupied by the objects and are not free. That why you are not able to see them in dba_free_space view. But you can find then in dba_extents. So in case of file 194, objects were getting created until block no 35001 (so we can see that block formating till block 35001) and then at later point of time some of the objects got dropped, so the space got freed, but the formating of blocks remain intact (example block IDs which got freed are 13401, 13417, 13433 etc.). This we call it as high water mark for a datafile.
As we saw earlier that we have around 1844.125 MB free space. Can we recover all of them?? I wish .. but no, we cannot. Reason being that, some of the blocks are free “in-between” and there is a fragmentation. To make my point clear, lets try to reduce the file size.
Lets try that !!
This file original size is 2000M

SQL> select bytes/1024/1024 from dba_data_files where file_id = 194;

BYTES/1024/1024
—————
2000
and as per dba_free_space its having 1844.125 MB free space. Lets reduce the file size by 1800 MB and it will definately give error.
SQL> alter database datafile 194 resize 200M;
alter database datafile 194 resize 200M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

What happened??
The reason you are not able to free the space is because we have certain objects created in between and there is no chunk of 1800MB free. The structure of this file is as given below.
X -> Occupied Block
0 -> Free Block

So here block 35001, 13433 are free, but in-between blocks are occupied. When you are trying to reduce the size of datafile by 1800 MB, you are going beyond block ID 35001, where the objects do exits. After those objects there may be free space, but that doesnt matter, you can at max reduce the size of datafile until block ID 35001, and not beyond that.
So here if we see that there are around 220992 blocks free after block ID 35001. That makes a space of around (220992 X 8192 = 1810366464 bytes = 1726.5 MB free). Thats what the bytes column is showing.
So we can reduce the size of this datafile by maximum of 1810366464 bytes = 172.5 MB. If you try to reduce the size more than that, then you will hit with an error ORA-03297: file contains used data beyond requested RESIZE value.
Now try reducing by 1700 MB and it will succeed !!!
SQL> alter database datafile 194 resize 300M;
database altered
What if you want to still reduce the size beyond 1800 MB. i.e. what if you want to make the size to 200MB.
To do that, you need to do following steps.
- Find block ID at 1800th byte.
We know that last 1810366464 bytes are free.
1800 MB = 1887436800 Bytes
Bytes Remaining = 1887436800 – 1810366464 = 77070336 Bytes = 9408 Blocks
Block ID till we want to clear the objects id  35001 – 9408 = 25593
- Drop all the objects which are beyond this block. That will make 1800MB free in this datafile and you can reduce the size of datafile by 1800 MB.
Moral of story is dont get into these many calculations. Its good to know the logic and helps in crisis situation. But not good to go for such drop object, reduce the size if file and recreate the object again.

For 9i or higher versions script.

REM Script is meant for Oracle version 9 and higher
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,t.block_size
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 */
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);

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


<<check_free>>

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.blocks*c_file.block_size)/1024||'K'||' can be resized to: '||round((file_min_block*c_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_block*c_file.block_size)/1024)||'K;');
dbms_output.put_line('.');

end if;

end loop;

end;
/

Example output for Oracle version 9 and higher:

Tablespace: TEST Datafile: /oradata/v112/test01.dbf
cannot be resized no free extents found
.
Tablespace: UNDOTBS1 Datafile: /oradata/v112/undotbs01.dbf
current size: 9384960K can be resized to: 106496K (reduction of: 98.87 %)
SQL> alter database datafile '/oradata/v112/undotbs01.dbf' resize 106496K;
.
Tablespace: USERS Datafile: /oradata/v112/users01.dbf
current size: 328960K can be resized to: 117248K (reduction of: 64.36 %)
Extents found in recyclebin for above file/tablespace
Implying that purge of recyclebin might be needed in order to resize
SQL> purge tablespace USERS;
SQL> alter database datafile '/oradata/v112/users01.dbf' resize 117248K;