Re: Reduce tablespace physical file size

alonbn4_at_hotmail.com (Alon Barnes) wrote in message news:<2ad5b4b5.0310200053.246d5e7a_at_posting.google.com>...
> "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;
It's late in the day and I'm going blind from terminal-itus, but you might look at metalink note: 1039341.6 and the others that explain about the differences of TEMPORARY tablespaces and why they might keep allocated segments. If this is just the sort space for your users, just drop it and recreate it the right size when they aren't sorting anything. You might consider turning AUTOEXEND off and just make it the maximum size people will ever need.

>
> 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

jg

--
@home.com is bogus.
http://www4.fosters.com/News2003/October2003/October_19/News/su_1019b.asp