Printable View
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
Increase the datafile to you own predetermined size. I use 2Gb or 4GB depending on what Oracle Version I'm running.
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.
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,
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).