Re: Shrink Datafiles

In article <611.316T1568T13023779_at_rheingau.netsurf.de>, Lothar Armbruester <lothar.armbruester_at_rheingau.netsurf.de> wrote:
>On 02-Jan-98 18:23:36 Qiuping Mao 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.

>
>As always, check the manual for the correct syntax because ¡'m writing
>this at home with neither Oracle nor manual to check!

alter database datafile 'completefilename' resize 1900M;

>
>Hope that helps,
>Lothar
>
>--
>Lothar Armbrüster | lothar.armbruester_at_rheingau.netsurf.de
>Schulstr. 12 | lothar.armbruester_at_t-online.de
>D-65375 Oestrich-Winkel |
>

-- 
These opinions are my own and not necessarily those of Information Quest
jgarry_at_eiq.com                           http://www.informationquest.com
http://ourworld.compuserve.com/homepages/joel_garry
"See your DBA?"  I AM the _at_#%*& DBA!