Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2008

    Unanswered: Recovering unused space from datafiles...


    Using Oracle 9i. I know this is a much discussed topic and I done a lot of research on this, however, could not find any convincing answers....

    I simply want to recover as much 'unused' space from datafiles as I can. For this I understand I need to find the HWM out for which there are a number of scripts available on the net.
    And then the easiest thing is to simply recover the space available above the HWM first and then look at recovering the space below the HWM....

    But what I would really appreciate is some instructions for dummies (if you know what I mean). Like for instance how to start doing this in Toad for example...

    Taking the tablespace offline (or can this be done with the tablespace online) Personally I don't think it can be done with the tablespace online.

    And then using:

    Alter tablespace A resize datafile 'datafile_name' 'newsize'M

    Will be grateful for any responses... Thanks.

  2. #2
    Join Date
    Aug 2003
    Where the Surf Meets the Turf @Del Mar, CA
    Provided Answers: 1
    From my perspective the cost to "reclaim" any "unused" disk space is higher than the cost of the disk space itself.

    I will pose 1 possible alternative, but recommend against doing so.
    Create a new tablespace (TS) move objects 1 at a time (online) from old TS to new TS.
    After old TS is empty, drop it to reclaim the disk space.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2003
    Provided Answers: 23
    Quote Originally Posted by shajju
    I simply want to recover as much 'unused' space from datafiles as I can.
    Do you expect your database to not grow any more?

Posting Permissions

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