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.
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
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.
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.
OCP 8i, 9i DBA
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
Click Here to Expand Forum to Full Width