Re: rollback tablespace datafile too big

In article <8lseml$sut$1_at_news.sinet.slb.com>,   "Yong" <yhuang_at_slb.com> wrote:
> I thought the only restriction was you cannot shrink a datafile to
 where
> there's data. In other words, if you create a tablespace with 100M
 datafile,
> create some small tables in there, you can still resize the datafile
 to,
> say, 50M.
>
> Yong Huang
>
> <oratune_at_aol.com> wrote in message news:8ls6v8$il0$1_at_nnrp1.deja.com...
> > In article <8ls0j2$df9$1_at_nnrp1.deja.com>,
> > fly13_at_my-deja.com wrote:
> > > Hi
> > > Here is a n easy question about RBS tablespace on Oracle 8.1.6.
> > > I had big transaction to do on a table.
> > > And so my RBS datafile (rbs1.dbf) is very big.
> > > The problem is when there is no transaction any more, and the
 commit
> > > has been done, the size of the RBS datafile is still the same.
> > > I try to coalesce this tablespace, but no result.
> > > Can someone help me to reduce the size of this file?
> > >
> > > Cheers in advance!!!
> > >
> > > Fly13
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
> > >
> >
> > Datafiles don't shrink when there is no data in them. Once you
 create a
> > datafile you can't make it smaller without dropping the tablespace,
> > deleting the datafile and then recreating the tablespace with a
 smaller
> > datafile.
> >
> > --
> > David Fitzjarrell
> > Oracle Certified DBA
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
>

I stand corrected; a datafile can be resized (or Oracle can attempt to resize) with the following syntax:

alter database
datafile <...> resize <new size> K|M;

However the datafile will not be resized automatically simply because there is no data in it. The RBS datafile will need to be manually resized. In this case I would suggest that the extremely large rollback segment be assigned to the large datafile and a smaller datafile be added to the RBS tablespace to accomodate the rollback segments for more "normal" sized transactions.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.