DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: datafile resizing

  1. #1
    Join Date
    Aug 2000
    Posts
    163
    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.

  2. #2
    Join Date
    Oct 2000
    Posts
    250
    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]

  3. #3
    Join Date
    Feb 2000
    Posts
    175
    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.

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Or, create a tablespace that will hold your objects, move the objects to that tablespace, resize the datafile, move the objects back.
    Jeff Hunter

  5. #5
    Join Date
    May 2000
    Location
    Portsmouth, NH, USA
    Posts
    378

    Talking how about this too?


    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


  6. #6
    Join Date
    Oct 2000
    Posts
    76
    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?
    J.T.

  7. #7
    Join Date
    May 2000
    Location
    Portsmouth, NH, USA
    Posts
    378

    Red face fragmented


    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width