Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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?
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.
An additional benefit of using separate tablespace for each schema is that these tablespaces can be placed on different disk groups to meet differing performance, cost, and recovery requirements. For example, one may need to be on flash drives, another on large slow disks, and another on a dedicated RAID 61.
