Problem solve
Get help with specific problems with your technologies, process and projects.
Is a separate tablespace for every table and index a good idea?
I just took a job and found out they want to have a separate tablespace for every table and index. I am trying to convince them this is not good, but they want reasons. Can you give me a few good reasons why this is not good? We currently have 250 tablespaces, but this will grow to over 850 before the end of the year.
You will have more data files and tablespaces to keep track of. This will increase the amount of time required to administer the database and increase the complexity of administration. The more complex the environment is, the greater the chance for human error.
You will have to monitor freespace on all of the data files. The more data files you have, the greater the chance you have of losing or corrupting one. Oracle's entire architecture is built upon the multiple table and index per tablespace principle. I have never heard of anyone implementing one table or index per tablespace in Oracle.
Every time Oracle executes a system checkpoint, it writes to the file headers of every file. Checkpoints will be much slower.
Note: Thanks go to my good friend and Oracle guru, Chris Foot, for his help in preparing this answer.
