Decreasing Data file size in Oracle 8.05

Hi,

I need to reduce the size of one of my datafiles. It currently has a size of 240M. Several tables in different schemas have their data stored in that datafile. The segments for these tables were taking up more than 200M of space. However, I deleted an old schema that is no longer in use. This reduced the amount of space utilized in the datafile to about 160M (according to storage manager). Looking at dba_segments and dba_extents seems to confirm that only 160M in the datafile is currently utilized.

Unfortunately when I try to resize the datafile as follows:

alter database datafile '/u02/oradata/ihelparchcontent_tbl.dbf' resize 200M

I get the following error:

ORA-03297: file contains 10267 blocks of data beyond requested RESIZE value

Deleting the old user removed a 40M extent that was stored in the datafile. However, Oracle does not seem to recognize that space in the datafile has been freed.

Does anyone have any suggestions how I can reduce the size of this file?

Thanks,

Jeff Bowes Received on Sat Jul 21 2001 - 16:25:06 CDT