I have 8 oracle instances with an only user for every instance.
My customer asked me to create an only instance with 8 tablespaces and 8 different users.
These instances could also further increase.
I think that avoiding creating all these tablespaces on an only instance, because I think that it would be possible to have a reduction of the performances, considering that the tablespaces INDX, TEMP and SYSTEM are the same.
According to you what problem would it be possible to verify afterwards doing an operation of the kind?
If what you are saying is that you have 8 users, each has his own Oracle database, then that is a bit of OVERKILL.... If you combine the schemas into 1 database (each schema having it's own tablespace), you should be able to tune the 1 database to have much better performance than 8 little ones....
1. The system tablespace should only have SYS and SYSTEM objects located in it.
2. TEMP tablespace is only for temporary segments and that will depend on whether or not the user's have that tablespace defined as their temporary tablespace.
3. You can spread the memory allocation out in the block_buffers and shared_pool depending on the amount of data and SQL statements (objects) that the database incurs.
4. INDX - Are their objects in INDX (should be only indexes)... If you wanted to keep them separated by schema, have an INDX tablespace also for each user.