Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> comp.databases.theory -> Re: DB Design Dilemma!! One DB or Many DBs??

Re: DB Design Dilemma!! One DB or Many DBs??

From: Mark Townsend <>
Date: Thu, 14 Feb 2002 02:41:42 GMT
Message-ID: <>

in article, Rob Hindman at wrote on 2/11/02 7:20 PM:

> Hi I have a major DB Design Dilemma !!

This is a classic consolidation play. Oracle has specific capabilities to address these type of requirements

  1. Virtual Private Database for the automatic data seperation - i.e one set of tables containing the common data for all business entities, with any query or DML automagically rewritten by the database to limit it's effect to the corresponding business unit. You can develop the application once without the need to build in the seperation logic, and the same business rules also work for users that want to access the data via ad-hoc tools etc that bypass the application. This is especially useful when you manage your users in an LDAP directory, as the directory can provide the corresponding unit information automatically (i.e key the seperation logic in the database on the actual org definitions information in the directory definition). Also a better solution than views - views work OK with 10 business units, but with 1000 (or 10,000, or 100,000) quickly become unmanageable.
  2. Resource management so that you can manage one business units workload requirements against another - i.e allow one business unit to close their books (run lots of reports) without this query load unduly impacting on the other units ability to post data. (Oracle concurrency control also helps facilitate this as well). Or have the resource availability managed to 'follow the sun' if your business units are global.
  3. List partitioning to seperate the actual physical data-to-disk layout of each business units data - so that you can have different backup/recovery service level and timetable for each one business unit if required. Couple this with pre-populated transportable tablespaces and you can add a new business unit immediately.

A number of large ASPs use these capabilities to great effect to solve exactly these types of problems. Received on Wed Feb 13 2002 - 20:41:42 CST

Original text of this message