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.
Got some thoughts? Leave a comment