Hi list,
I'm having trouble shrinking a data file that at one time extended to 32 GB and
now is using about 4 GB. I have done this in the past where I move tables and
indexes from the "back end" out of the tablespace, so that there is nothing but
free space after about the 4 GB mark of the file. I can see this with a
tablespace map on the data file through TOAD. I am on Oracle 12.1.0.2 EE.
I've even bounced the instance after all of the moves just in case that had
anything to do with it.
I found an Ask Tom thread to do the following:
select file_name,
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
ceil( blocks*&&blksize/1024/1024) currsize,
ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
/
It showed:
Smallest
Size Current Poss.
FILE_NAME Poss.
Size Savings
-------------------------------------------------- -------- -------- --------
/SDWSTG/DATA/dwdata.dbf 3,327 31,507 28,180
I then ran his next script to create the command to shrink. This was the
result.
alter database datafile '/SDWSTG/DATA/dwdata.dbf' resize 3327m;
When I attempt to shrink I get:
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
I get this error if I try to shrink the file to anything below the current size
of 31,507.
Anyone know what I may be missing?
