Re: Decreasing Data file size in Oracle 8.05

"Jeff Bowes" <bowesj_at_telusplanet.net> wrote in message news:3B42DAC2.DE36E365_at_telusplanet.net...
> 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

Oracle did recognize the space was freed, but you now have holes of free space in your tablespace. That is: Oracle doesn't automatically reorg your tablespace so all free space is coalesced, and all used space is contiguous. The quickest solution is the export/drop/import route, the imp will take care of all data being adjacent.

Hth,

Sybrand Bakker, Senior Oracle DBA Received on Sat Jul 21 2001 - 16:25:18 CDT