Thank you this is great. FYI: all instances are used for reporting and ad hoc queries - we have 30 schools each with their own instance. None are testing or development and none can be eliminated.

The path I would follow are :

1) Eliminate instances you do not need or perhaps you may be able to
combine use of instances?

2) From the list of remaining instances, take out those used as
development, uat, performace etc.. These non-production instances
should be housed in a separate server/domain.

Now you essentially have 2 main options.

1) Have one large machine capable of running all these instances..( ie.
SUnE15K for example ) . Have one database instance and use different
schemas for separation. However, you will need to segregate machine and
I/O resource by use of Resource Manager.

2) Go for Oracle RAC, and one database instance. Still use separate
schema to achieve the 'data' separation you may require. However, the
server resources are now handled via the RAC nodes.


3) use of row level security, streams etc , I would suggest, are not
ideal for the type of consolidation you want.

I've been through the same exercise and hence my suggestions.

Harish.



Oracle LazyDBA home page