Resizing Datafiles
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Resizing Datafiles

  1. #1
    Join Date
    Jan 2002
    Posts
    65
    Hi all,

    I have a Oracle 7 Database where some of the Datafiles are huge in size although the %Free Space for the Tablespace is around 99%.

    I am planning to Resize the Datafiles.

    Can Some one tell me as to what are the parameters and criteria for calculating the Resizing Limit in the following Query.

    ALTER DATABASE DATAFILE 'filename' RESIZE

    What I need to know is if there is any way by means of which one can calculate a safe size for Downsizing the datafiles using the values for PCT_USED,FREE SPACE,USED SPACE ETC for each Datafile

    Thanks
    Sabitabrata

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    the space required in the tablespace depends on the growth of objects (segments) in that tablespace. Estimate the size of those objects and resize your datafile accordingly.

    Also keep in mind that you can not resize the file beyond the last block used in the datafile.

    HTH
    Sanjay

  3. #3
    Join Date
    Jul 2000
    Location
    Amsterdam
    Posts
    234
    Hi sabita_ban,

    You can find out what type and size of segments your dealing with in the tablespace of interest by querying the dba_segments view.

    If only indexes you can rebuild them to an other tablespace and empty and coalesce the tablespace you want to shrink.

    This will give you a work around for the problem of resizeing beyond the last block used in the datafile.

    If there are only tables in the tablespace you have no alternative other than export, drop, coalesce, shrink and import.

    Regards,

    Tycho

  4. #4
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    By tycho:
    If there are only tables in the tablespace you have no alternative other than export, drop, coalesce, shrink and import.
    or..
    ALTER TABLE MOVE......

    Sanjay

  5. #5
    Join Date
    Nov 2001
    Location
    Sheffield, England, UK
    Posts
    78
    Hmmm.

    Is the 'ALTER TABLE MOVE' available in Oracle 7?

    Not being fascetious... just not sure!

  6. #6
    Join Date
    Jul 2000
    Location
    Amsterdam
    Posts
    234
    Hi AJW_ID01,

    Sadly enough no table moves in 7 or 8.0 just from 8i upwards.

    Tycho

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