ORA-03297: file contains used data beyond requested RESIZE value
What may be more frustrating is that the datafile has plenty of free space.
So what can be done to solve this error and resize down the datafile? Why does it occur? Some might be tempted to just add more space to the filesystem and be done with it. Not everyone has that option.
A datafile cannot be resized down below the High Water Mark (HWM) and that's what causes the ORA-03297 error. . In the lifetime of the datafile some extents have been created bumping up the HWM. Others below the HWM have also been DELETED.
I will show an example of a datafile encountering this error, what is happening inside the datafile and how to resolve the error. Let's create a tablespace first and the two tables, one with many rows and a second with a few rows.
CREATE TABLESPACE "MYTS" DATAFILE '/oradata1/MYDB//mytsdatafile01.dbf' SIZE 32M AUTOEXTEND ON NEXT 1310720 MAXSIZE 64M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO Tablespace created. SQL> create table myuser.mytab1 tablespace MYTS as (select level id,dbms_random.string('a',dbms_random.value(1,4000)) str from dual connect by level < 20000 ) / Table created. SQL> create table myuser.mytab2 tablespace MYTS as (select level id,dbms_random.string('a',dbms_random.value(1,4000)) str from dual connect by level < 10 ) / Table created.
How many MB is now in use by these tables?
select segment_name, bytes/1024/1024 from dba_segments where segment_name in ('MYTAB1','MYTAB2') and owner = 'MYUSER' / SEGMENT_NAME BYTES/1024/1024 ----------------------------------------------- MYTAB1 54 MYTAB2 .0625
To demonstrate how these two tables fit in the MYTS tablespace I want to firstly show a few things about the datafile.
DFSIZEMB = the datafile size in MB on disk.
HWMMB = the location of the HWM in the datafile.
DFREEMB = the number of FREE MB in the datafile.%FREE = the percentage of FREE space in the datafile.
RESIZEABLE = the amount of MB that can be gained from resizing.
Use the below SQL to see how much space can be gained from a RESIZE and the location of the HWM in a datafile. For a database with many datafiles this query may take a while.
QUERY 1 - datafile free space, HWM location, resizeable MB
column file_name format a50 column tablespace_name format a20 select tablespace_name, file_id, file_name, dfsizeMB, hwmMB, dffreeMB, trunc((dffreeMB/dfsizeMB)*100,2) "% Free", trunc(dfsizeMB-hwmMB,2) "Resizeble" from ( select df.tablespace_name tablespace_name, df.file_id file_id, df.file_name file_name, df.bytes/1024/1024 dfsizeMB, trunc((ex.hwm*(ts.block_size))/1024/1024,2) hwmMB, dffreeMB from dba_data_files df, dba_tablespaces ts, ( select file_id, sum(bytes/1024/1024) dffreeMB from dba_free_space group by file_id ) free, ( select file_id, max(block_id+blocks) hwm from dba_extents group by file_id ) ex where df.file_id = ex.file_id and df.tablespace_name = ts.tablespace_name and df.file_id = free.file_id (+) order by df.tablespace_name, df.file_id ) / TABLESPACE_NAME FILE_ID File Name DFSIZEMB HWMMB DFFREEMB % Free Resizeble -------------------- ---------- ------------------------------ ---------- ---------- ---------- ---------- ----------... MYTS 14 /oradata1/MYDB/mytsdatafile01.dbf 55.75 55.06 .6875 1.23 .69 ...Above, the datafile can only be resized by 0.69MB and the HWM is at the 55MB mark with the datafile size on disk being 55.75MB.
How do these two tables look like within the datafile? NOTE: This query may take a while for a large datafile with lots of extents.
QUERY 2 - location of segments within a datafile
column segment heading 'Segment Name' format a14 column file_name heading 'File Name' format a40 column segment_type heading 'Segment Type' format a10 select file_name, segment_type, owner||'.'||segment_name segment, block_id, blockIdMB from ( select ex.owner owner, ex.segment_name segment_name, ex.segment_type segment_type, ex.block_id block_id, df.file_name file_name, trunc((ex.block_id*(ts.block_size))/1024/1024,2) blockIdMB from dba_extents ex, dba_data_files df, dba_tablespaces ts where df.file_id = &file_id and df.file_id = ex.file_id and df.tablespace_name = ts.tablespace_name order by ex.block_id desc ) where rownum <= 100 / Enter value for file_id: 14 File Name Segment Ty Segment Name BLOCK_ID BLOCKIDMB --------------------------------- ---------- -------------- ---------- ---------- /oradata1/MYDB/mytsdatafile01.dbf TABLE MYUSER.MYTAB2 7040 55 /oradata1/MYDB/mytsdatafile01.dbf TABLE MYUSER.MYTAB1 6912 54 /oradata1/MYDB/mytsdatafile01.dbf TABLE MYUSER.MYTAB1 6784 53 /oradata1/MYDB/mytsdatafile01.dbf TABLE MYUSER.MYTAB1 6656 52 /oradata1/MYDB/mytsdatafile01.dbf TABLE MYUSER.MYTAB1 6528 51 /oradata1/MYDB/mytsdatafile01.dbf TABLE MYUSER.MYTAB1 6400 50 ... ... /oradata1/MYDB/mytsdatafile01.dbf TABLE MYUSER.MYTAB1 144 1.12 /oradata1/MYDB/mytsdatafile01.dbf TABLE MYUSER.MYTAB1 136 1.06 /oradata1/MYDB/mytsdatafile01.dbf TABLE MYUSER.MYTAB1 128 1
We can see table MYTAB1 ends around the 54MB block in the datafile and MYTAB2 starts around the 55MB block.
So we now have two tables in the MYTS tablespace created one after the other. Further, how about we create a situation where there is alot of free space in the datafile and cannot be resized resulting in "ORA-03297: file contains used data beyond requested RESIZE value". DELETING all the rows from the MYTAB1 table will not free up any space and will still have it allocated to MYTAB1, so let's TRUNCATE the table MYTAB1 and run the same SQL above again to see the result of the datafile.
SQL> truncate table myuser.mytab1; Table truncated. TABLESPACE_NAME FILE_ID File Name DFSIZEMB HWMMB DFFREEMB % Free Resizeble -------------------- ---------- -------------------------------------------------- ---------- ---------- ---------- ---------- ---------- MYTS 14 /oradata1/MYDB/mytsdatafile01.dbf 55.75 55.06 54.625 97.98 .69The datafile now has 97% and 54MB FREE space. The HWM is still at the 55MB mark. Inspecting the datafile, MYTAB1 is at block 1MB and MYTAB2 is still at of course 55MB.
File Name Segment Ty Segment Name BLOCK_ID BLOCKIDMB --------------------------------- ---------- -------------- ---------- ---------- /oradata1/MYDB/mytsdatafile01.dbf TABLE MYUSER.MYTAB2 7040 55 /oradata1/MYDB/mytsdatafile01.dbf TABLE MYUSER.MYTAB1 128 1
So seeing we have 54MB FREE space in a 55MB datafile let's try to resize it down to 50MB.
SQL> alter database datafile '/oradata1/MYDB/mytsdatafile01.dbf' resize 50M; alter database datafile '/oradata1/MYDB/mytsdatafile01.dbf' resize 50M * ERROR at line 1: ORA-03297: file contains used data beyond requested RESIZE value
The only way to resize the datafile is to lower the HWM. This is achieved by moving the extents located at the HWM either to another tablespace or the same tablespace. For the same tablespace ensure plenty of free space and run QUERY 2 after each MOVE to see if anything is achieved.
Of course for a production environment where there are many users accessing the table, index, LOB etc it might not be that easy and this sort of activity would need to be carefully planned, especially with LOB segments. Also, when moving a table the underlying INDEXES are marked UNUSABLE and need to be rebuilt.
SQL> alter table myuser.mytab2 move tablespace MYTS; Table altered.Now the datafile shows that MYTAB2 is at block 1MB.
File Name Segment Ty Segment Name BLOCK_ID BLOCKIDMB ----------------------------------- ---------- -------------- ---------- ---------- /oradata1/MYDB/mytsdatafile01.dbf TABLE MYUSER.MYTAB2 136 1.06 /oradata1/MYDB/mytsdatafile01.dbf TABLE MYUSER.MYTAB1 128 1
What about the HWM?
TABLESPACE_NAME FILE_ID File Name DFSIZEMB HWMMB DFFREEMB % Free Resizeble -------------------- ---------- -------------------------------------------------- ---------- ---------- ---------- ---------- ---------- MYTS 14 /oradata1/MYDB/mytsdatafile01.dbf 55.75 1.12 54.625 97.98 54.63
Now the HWM is at the 1.12 MB mark and there is 54MB of reclaimable space in the datafile.
SQL> alter database datafile '/oradata1/MYDB/mytsdatafile01.dbf' resize 2M; Database altered.
Datafile now resized! In a production environment there may be many different objects extents near the HWM and simply moving one table might not be so common. It's best to see how many different objects are around the HWM and calculate how much space can be reclaimed via the MOVE. Unless there is a benefit in reclaiming a large amount of space, or moving tables, indexes, LOBs etc is something that just has to be done, then if possible get more space added to the filesystem.