Re: shrinking .dbf files?

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:<40be6e91$0$1585$afc38c87_at_news.optusnet.com.au>...
> "OtisUsenet" <otis_usenet_at_yahoo.com> wrote in message
> news:5606b639.0406021601.23e35af1_at_posting.google.com...
> > Hello,
> >
> > Problem:
> > While importing a DB dump I run out of space on the partition that
> > holds various Oracle data files (*.dbf). It seems that some of those
> > .dbf files, like the one that holds temporary tablespace, grew really
> > big during import.
> >
> > My disk (a partition with Oracle files, really) is now 100% full, and
> > I am hoping to find a way to shrink some of the big .dbf files.
> >
> > For example, here are some large files:
> >
> > 3.5G /u01/app/oracle/oradata/ogdev/temp01.dbf
> > 1.6G /u01/app/oracle/oradata/ogdev/users01.dbf
> > 441M /u01/app/oracle/oradata/ogdev/system01.dbf
> > 376M /u01/app/oracle/oradata/ogdev/undotbs01.dbf
> > 139M /u01/app/oracle/oradata/ogdev/example01.dbf
> >
> > Is there a way for me to shrink these?
> >
> > I can't really drop tablespaces that use these files, since without
> > them my DB would be useless, but I would like to at least shrink these
> > files a bit for now, so my partition is not 100% full.
> >
> > Thanks!
> > P.S.
> > I'm using Oracle 9.2.0.1 under Linux (RedHat 9.0)
>
>
> Alter database datafile '/full/path/and/filename' resize 300M;
>
> Or whatever size you deem appropriate.
>
> It may not work, though, because if it bumps into data on the way down, it
> can't just lop that data out of the database (obviously!). So you can only
> resize downwards if the end of the data file is full of fresh air. If it
> isn't, there's not a lot you can do quickly or easily to fix the problem.
>
> Regards
> HJR
... and in any case shrink your temporary tablespace. It's by far the largest one. I'll take a (safe) guess that it's a default temporary tablespace with tempfiles. You need to create another (small) temp ts, make it a default one, drop the original one and re-create it with a smaller size.

TEMP grew up because your tempfile is autoextendable. Well, now you know your temp space requirements :) I'm not sure why it grew during the import. Sure you may build indexes as part of the import but the other tablespaces don't look that big. They unlikely to contain an index which requires 3.5G to be built. Unless the list of big ts is incomplete.

Anyway, what's wrong with 100% full? Or you use filesystem which needs some free space to be resized?

Cheers,
Igor Received on Thu Jun 03 2004 - 04:24:22 CDT