Grouping Objects Into Tablespaces

We need to revisit our tablespaces for migration to a new set of hardware. So, I am thinking about how best to carve up our space into tablespaces. We have let our tablespaces and objects get out of hand. With applications and users putting data everywhere. For our new system, I plan on enforcing tablespace quotas. However, we still need to rethink how to logically divide up the Oracle database into tablespaces. Of course, the required tablespaces will be present: SYSTEM, TEMP, UNDOTBS, SYSAUX, etc. I prefer to keep everything system managed. No more manual extent and FREESP management for me.

The new system is a 10g data warehouse/DSS hybrid. So, tables with similar characteristics will be grouped into a tablespace. For example, all tables that are read-only could be grouped into a single, read-only tablespace. Tables with random I/O patterns could also be grouped together; all small tables should be grouped together. Segregate Indexes in separate tablespaces away from their tables. Partition really large table and indexes into separate tablespaces. Keep working or intermediate objects that will be dropped following a processing cycle in separate tablespaces. These are just a few examples.

Now how large is really large? For my databases, any tables over 100GB would be considered really large. Of course, what constitutes really large in your environment will depend upon the underlying hardware and applications of your database server.