Why can’t I resize my datafile

Posted by

We’ve all done that common administrative task of:

– find the HWM in a datafile

– resize the datafile down to that mark.

But sometimes, you might get what appears to be a problem:

Here’s a tablespace I created a while back…

SQL> create tablespace DEMO
2  datafile 'C:\ORACLE\ORADATA\DB112\DATAFILE\DEMO.DBF' size 100m
3  extent management local uniform size 1m;
Tablespace created.

After a while I wanted to reclaim that 100 megabytes back, so I looked at the high water mark in DBA_EXTENTS

SQL> select max(block_id+blocks)*8192/1024/1024 high_mb
2  from dba_extents
3  where tablespace_name = 'DEMO';

So, if the high water mark is 2meg, all I need so now is resize the file….

SQL> alter database datafile 'C:\ORACLE\ORADATA\DB112\DATAFILE\DEMO.DBF' resize 10m;
alter database datafile 'C:\ORACLE\ORADATA\DB112\DATAFILE\DEMO.DBF' resize 10m
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

So what has happened ? Well, its a little anomaly where DBA_EXTENTS does not show the whole picture. Let us look at DBA_SEGMENTS instead

SQL> select segment_name
2  from   dba_segments
3  where  tablespace_name = 'DEMO';

There is a dropped object in there, still taking up space. Lets purge that out and try again

SQL> purge recyclebin;
Recyclebin purged.
SQL> alter database datafile 'C:\ORACLE\ORADATA\DB112\DATAFILE\DEMO.DBF' resize 10m;
Database altered.


  1. Connor,

    Thanks for that, I have updated my relevant scripts pointing to this site.
    would be interesting to know if we could create or own “dba_extents” ( would be version dependent ) that would “show the whole picture”.

    In, dba_segments queries sys.sys_dba_segs ds with
    bitand(NVL(ds.segment_flags, 0), 1) = 1
    and bitand(NVL(ds.segment_flags,0), 65536) = 0

    a quick test ( create table t with segment ) shows that after creation segment_flags is 4325633, when the segment goes into recyclebin this one is updated to 4391169

    sokrates> select bitand(4325633, 1), bitand(4325633, 65536) from dual;

    BITAND(4325633,1) BITAND(4325633,65536)
    ----------------- ---------------------
    1 0

    sokrates> select bitand(4391169, 1), bitand(4391169, 65536) from dual;

    BITAND(4391169,1) BITAND(4391169,65536)
    ----------------- ---------------------
    1 65536


Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.