In my current application all tables are mapped to USERS table-space. I don't have much idea on how to organize table-space efficiently. This is what I'm thinking:
a. Based on row_counts, I'm planning to have three table-space. Small, Medium and Large.
b. Have different table-space for indexes itself.
Is this good enough or any other suggestion? BTW, I'm using Oracle 12c database.
I would not worry about splitting data over different tablespaces for data or indexes - thats very out dated thinking. The one thing that you may want to consider is if you are implementing some sort of data lief cycle, where by you need to archive off data (files). In that case, I might implement some sort of tablespaces strategy to support that. For example, may have a tablespace for each year of data, as I will only hold a rolling 7 years of data. Older data gets archived to offline storage.