Who's online

There are currently 0 users and 30 guests online.

Recent comments

Why can’t I resize my datafile


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.