Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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.aspReceived on Mon Oct 20 2003 - 19:04:22 CDT