Re: Reduce tablespace physical file size

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:<3f9284d8$0$10617$afc38c87_at_news.optusnet.com.au>...
> 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
Thanks for the information and sorry for my lack of info (Actually, I'm not an Oracle DBA, and the TS is one of many in a product I just joined its DEV team and assinged for this problem).

I will try to add some more info:

1. Operating system is WinNT and oracle 8.17
2. TS is not the real name of the table space. here is the creation command of the read tablespace:

CREATE TABLESPACE LSC_USERTEMP
DATAFILE 'F:\database\LSC_USERTEMP.DBF' SIZE 5M AUTOEXTEND ON NEXT 4M MAXSIZE 4097M ONLINE DEFAULT STORAGE
(INITIAL 128K NEXT 128K MINEXTENTS 1
MAXEXTENTS UNLIMITED PCTINCREASE 0)
TEMPORARY; 3. Since this is a tablespace in a customer site, It will take me some time
to run few of the queries written in the replyes here.

Thanks again for your help! appreceate it a lot.

Alon Received on Mon Oct 20 2003 - 03:53:16 CDT