Re: Reduce tablespace physical file size

alonbn4_at_hotmail.com (Alon Barnes) wrote in message news:<2ad5b4b5.0310190414.5af364b_at_posting.google.com>...
> I'm encountering a strange problem in one of my tablespaces in the
> database.
>
> One of my oracle tablespaces (that is represented as a phisical DBF
> file) is with size of 4GB. Several checks I made showed that there is
> no table or index in this tablespace.
>
> BUT - when I try to reduce the size of the file with this command:
>
> alter database datafile 'TS.DBF' resize 2000M;
>
> I get an error message:
>
> ORA-03297: file contains 96350 blocks of data beyond requested RESIZE
> value
>
> which means that the tablespace IS containing something (someone told
> me that there might be system data in it).
>
> Does anyone have an idea how to handle this? How can I reduce the size
> of this TS? what is the data kept in it?
>
> Thanks a lot,
> Alon

Connor McDonald posted a useful script on his site:

http://www.oracledba.co.uk/

click on "Administration".
scroll to the bottom, and click on "File high water mark"

(Showing the high water mark for a datafile (with a view toward shrink))

This should provide you with enough information as to determine how low you can go, in shrinking a datafile.

hth.

Pd Received on Sun Oct 19 2003 - 13:00:15 CDT