What are the benefits of using a different tablespace for a new user schema

oracletablespaces

The tables in the schema are intended to be used read only and is only updated once each three month.

My question refers to
performance
maintenance (backup/restore, export/import)

What about the temporary tablespace, is it better to use a different one in this case?

Best Answer

If you are planning to drop schemas, having a separate tablespace which can also be dropped will reduce fragmentation in whatever tablespace you would otherwise use (unless you are using uniform extents, but that is not the default).

RMAN recovery options are different as you would be able to do TSPITR on each individual schema.

Fragmentation is not an issue for temp tablespaces because "All extents of temporary tablespaces are of uniform size". Obviously there are no backup/restore, export/import implications for temp. By giving each user their own temp tablespace you'd be imposing a kind of resource limit on them, but there are less wasteful ways of doing that if it is your aim.