In this comprehensive guide, we’ll show you how to reclaim space from datafile oracle. Let’s open Virtualbox. Follow along for optimizing oracle database files.
In Oracle database, the HWM (High water mark) refers to the highest point in a data segment (for example a table) that has ever been used. When Oracle allocates space for a segment (such as a table), it keeps track of this HWM. Over time, as data is deleted or moved, but the HWM doesn’t decrease automatically, which means the space in the data file may still be allocated even though it’s no longer in use.so over time, Oracle Databases can accumulate unused space in its data files.
Sometimes DBA can encounter the following error when resizing a datafile:
ORA-03297: file contains used data beyond requested RESIZE value
A datafile cannot be resized down below the High Water Mark(HWM) and that’s what causes the ORA-03297 error. You can run the below-mentioned script to solve the issue.
Here’s the SQL script that will identify the data files that can be resized to reclaim space:
Find the data files that can be resized:
set linesize 1000 pagesize 0 feedback off trimspool on
column value new_val bsize
select value from v$parameter where name = 'db_block_size';
column cmd format a250 word_wrapped
select 'alter database datafile '''||file_name||''' resize ' ||ceil( (nvl(hwm,1)*&&bsize)/1024/1024 ) || 'm;' cmd from dba_data_files x,
(select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) y where x.file_id = y.file_id(+) and ceil( blocks*&&bsize/1024/1024) -
ceil( (nvl(hwm,1)*&&bsize)/1024/1024 ) > 0;
The above output commands can be run manually to resize datafile in oracle and reclaim unused space oracle tablespace.
I hope you found this article helpful. Feel free to reach out if you have any questions. You can go through the related article tablespace utilization in Oracle if you want.
Pingback: Reduce a datafile in oracle: 1 simple script
Hi Samir, thanks for the nice script – could help to free diskspace in an emergency.
I replaced in your resize-command “ceil” with “floor” to prevent from a “ORA-03297 File contains used data beyond requested RESIZE value.” message