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

I neglected to answer the third question in the other post.

in sqlplus, run the following:

select owner, segment_type, segement_name, trunc(bytes/1048576) mb   from dba_segments
 where tablespace_name=<tablespace_name> /

hth.

Pd Received on Sun Oct 19 2003 - 13:25:14 CDT