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';

   HIGH_MB
----------
2

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';

SEGMENT_NAME
----------------------------------------
BIN$Rst2XC5sT8Kn2ud7jhCwtA==$0
T

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.

2 comments

Sokrates says:

November 23, 2012 at 9:24 pm

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 11.2.0.2, 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

Matthias

Pingback: empty tablespace. datafiles won’t resize ORA-03297 | ur is not a word