Re: Shrinking datafiles

What version of the rdbms are you using? It is very difficult to answer questions such as this if you don't provide that information.

However, if you are using 7.3.4 or higher, you can issue the following command from svrmgrl:

alter database datafile 'datafile.dbf' resize nnn;

where nnn is the size that you want the datafile to be when you are done. However, you can only use this to shrink the datafile to a point where the highest extent is located. So, if you have a 250M datafile and you want to shrink it to 200M but there is an extent at the 225M point, you can only decrease the data file to 225. However, if you can map out the datafile you may find that by selectively moving tables, dropping indexes, etc. etc. you can free up the extents and be able to shrink the datafile some more.

Ken

as wrote:

> Is it possible to shrink a datafile? The scenario is as such - after a few
> months we have seen that our live tables are not going to grow anymore as we
> archive data to an archive database. We initially set the tables to a large
> initial extent but have dropped and imported them to new smaller tables. We
> have a lot more free space available in the tablespace but this is not
> needed.
> Is it possible to reclaim this space in the physical file system by
> shrinking the .dbf datafile???
>
> Thanking you in advance
> Andre
Received on Tue Aug 03 1999 - 07:12:32 CDT