Re: Shrink Datafiles

On 12 Jan 1998 17:30:10 -0800, joelga_at_pebble.ml.org (Joel Garry) wrote:

>>>What is the command for shrinking the datafile?
 

>>>Thanks in advance.
>>
>>In Oracle 7.3 you can do something like
>>
>>alter datafile 'f:\orant\database\datafile.ora' resize 20M;
>>
>>I don't know what happens is the datafile is full so that there is no room
>>for the shrink.
>
>It doesn't have to be full, just have system stuff at a high "watermark"
>in the file. For example, I have two files in my system tablespace,
>which had stuff that shouldn't have been there, that has been removed.
>So, they are 1.7G and 2.0G. Oracle politely tells you if there is data
>above your proposed eof, so you keep increasing the resize until it works.
>In my case, it wound up being 1G and 1.9G, so I still have to do the
>major surgery. YMMV. COALESCE doesn't help much.
>

There are 2 commands for reclaiming unused space that are new to 7.3.

One is 'alter database datafile <FILENAME> resize <SIZE>; the other is 'alter tables deallocate unused'.

The high water mark is only of importance with the 'alter table command, it has nothing to do with the 'alter database datafile resize' command.

That one is only concerned with space that has been allocated to tables, regardless of whether that space has been used to store data.

The 'alter table deallocate' command is the one that cares about the high water mark.

You might want to be careful with the 'resize' command. There have been cases of datafiles being trashed by this command when the size requested was less than what was actually needed to accomadate space already allocated to objects, and when the size requested caused the disk device to run out of space.

Check with Oracle for details, or consult your favorite search engine. Received on Tue Jan 13 1998 - 00:00:00 CET