ORA-03297: file contains used data beyond requested RESIZE value

At some stage a DBA will encounter the following error when resizing a datafile:

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        .69

The 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.