Best method for resizing a tablespace
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Best method for resizing a tablespace

Hybrid View

  1. #1
    Join Date
    Dec 2001
    Location
    Atlanta
    Posts
    175

    Best method for resizing a tablespace

    I'll like to know from any Oracle DBA guru the best way to resize a tablespace. I see DBA's usually edit and change the old size say from 40MB to 75M or is it better to add a new datafile or through SQLPlus to execute command like:
    ALTER DATABASE DATAFILE '/oracle/test.dbf' RESIZE 75MB;
    This is for both temporary local or permanent dictionary managed tablespace. Your advice will be highly appreciated.

    C. K.
    Apps DBA

  2. #2
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Increase the datafile to you own predetermined size. I use 2Gb or 4GB depending on what Oracle Version I'm running.
    OCP 8i, 9i DBA
    Brisbane Australia

  3. #3
    Join Date
    Dec 2001
    Location
    Atlanta
    Posts
    175
    Hi grjohnson,

    How do you increase? Do you edit the old size and make changes or do you run a command like 'ALTER ... through SQLPlus, or do you entirely add a new datafile. Which method do you use and which one is recommended.

  4. #4
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Ahhhhh *looks left and then right*... isn't that exactly the same question you asked first... I thought I answered it.

    Anyway, I create a datafile and increased it to a predetermined limit. I.e. I might create a 200MB datafile and then eventually increase this is 2GB or 4GB (depending on my Oracle version), either manually, or via AUTOEXTEND.

    Cheers,
    OCP 8i, 9i DBA
    Brisbane Australia

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Which is better? It depends . . .

    If the TS is small then just extend the file (at 75Mb, I would say you are in this area).

    As the TS gets big, adding files may give flexibility in allocating across physical drives (but adding a new TS and moving some tables is even more flexible). You may avoid OS problems too (e.g. PKZIP won't do 4Gb - so my files are max 3.5Gb - I zip them when sending them across the network to refresh my Standby db).
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

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