I need to decrease the size of the datafile. My datafile is only half full and it won't be used in the future. I'd like to eliminate the unused portion of it.
Is there a way to do that?
Thank you.
Printable View
I need to decrease the size of the datafile. My datafile is only half full and it won't be used in the future. I'd like to eliminate the unused portion of it.
Is there a way to do that?
Thank you.
hi,
Try the command as below
alter database datafile 'd:\data.dbf' resize 10M;
Hopes this will help.
[Edited by ckwan on 12-06-2000 at 10:37 PM]
Hi,
If the above doesn't work you'll have to export all the tables in the
tablespace that uses the datafile.
Drop the tablespace. (removing the O/S file)
Recreate the tablespace using the correctly sized datafile.
import the tables into the new tablespace.
Cheers
Moff.
Or, create a tablespace that will hold your objects, move the objects to that tablespace, resize the datafile, move the objects back.
Why can't ya do this?
1. Export the tablespace
2. delete all objects in tablespace
3. alter tablespace tablespace_A coalesce;
4. import the .dmp file
5. resize datafile
this is normally what I do. Is this okay or am I doing something wrong?
thanks,
Magnus
I would think for the purpose of the question posted, doing the alter database 'datafile' resize xxxM is the easiest way. Is there any reason why you would prefer the export method over this simple command?
If the datafile is fragmented or the space was once used then maybe deleted/shrunk, I think you cannot reduce the size of the datafile at that point. The only course of action is to export, delete data, THEN coalesce, and THEN reduce datafile size and import data.
That is at least how I understand it.
- Magnus