Re: Reduce tablespace physical file size

Alon Barnes wrote:

> 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

Not a lot of information to go on, is there?

what does a select * from dba_segments where tablespace_name='TS' give you (replace TS with the actual name of your tablespace).

A version and operating system would be nice, too.

And is 'TS.DBF' the real name of the datafile, or were you just trying to be helpful. Thing is, if it was actually called RBS.DBF or something like that, that would have been more helpful.

Regards
HJR

-- 
--------------------------------------------
See my brand new website, soon to be full of 
new articles: www.dizwell.com.
Nothing much there yet, but give it time!!
--------------------------------------------