When creating a user/schema to hold external application data, what are best practices for the tablespaces that the user is defaulted to?
Should the user created be defaulted to the USER tablespace for the default tablespace? Does this hold true for the TEMP tablespace? Or should this user have a separate default and temp tablespaces created apart from the SYSTEM user tablespaces to hold the application data?
I tried looking for any indication of this in the Oracle documentation, but wasn't able to find anything close to what I was looking for – apologies if this is subjective in nature.
Best Answer
This is a best practice, and the answer is "it depends". For example, if the schema has low accesses and few data, using a different tablespace different to USERS/TEMP isn't really necessary,because you can assign a quota to user's data (in this case, creation of separated tablespaces is a merely question about organization). Conversely, if the schema has an high i/o or has a lot of data, the uses of different tablespaces is a good choice, because you can create them to a different pool of disks (for example, Oracle is installed in magnetic hard disk, and tablespace data is created on a pool of SSD). Note that: in this case, you want also a different tablespace for saving indexes