Re: shrinking .dbf files?

"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 Received on Wed Jun 02 2004 - 19:19:49 CDT