I guess this is an architecture question.

The question is: If I've got two (or more) Oracle databases, when does
it make sense to integrate them into a single database, and when should
they be left as two (or more)?

Background: (You can probably skip most of this without worry.)

The company has several medium-large databases running under Oracle.
There is some overlap of data in these. There are various means and
methods of getting data in, manipulating it, and getting it out again.

This system grew due to the requirements of various departments doing
work in somewhat related tasks. Issues such as billing, debt, collateral,
foreign exchange, trades of various products and services, and
settlements with the market regulator, all have their own database,
or schema in a larger database. There was not a single architected
design for the entire system, though there were some pretty good
architects on individual projects involved.

Some of these databases have been around for a long time, and even
started life in some other product and got migrated to Oracle. Others
are less than a year old.

At present, there is a not-very-robust nor well written patchwork
of data transmission between these databases. The various powers that
be are very unhappy about this and want it cleaned up.

There are two competing pressures here. One "camp" wants to do a lot
of work on the interfaces, making them better defined, more robust,
and so on. Apparently we've settled on a product for doing this.

Another camp wants to reduce the number of databases by merging
some of them. It would, they claim, reduce the inter-database
communication issues, as well as decreasing the reproduction of
data. (In some cases, entire tables are lifted from one database
and copied into another.) They want to do integration work to make
the databases serve the larger needs.

And, of course, there's a camp that wants to just keep limping along
as we are, hopefully not spending any *more* money on it. This camp
is mostly the managers of the business workers who just want to get
their work done.
Socks

puppet_sock (AT) hotmail (DOT) com wrote in message news:<c7976c46.0409101023.7d0eb102 (AT) posting (DOT) google.com>...
Hi,

it's a question of how you process the data in your databases.

if you keep them on the same server (enough disk, cpu, memory) it's
the best to have one instance that hosts the data of your n -
instances. It's easier to maintain (backups, recover), better use of
system resources and so on.

Additionally, if you can classify your databases according to their
usage in OLTP-type (many small transactions) or datawarehouse- type
(some big adhoc queries), I would separate them in 2 instances and
place them on different servers, and tune each of them separatley...

hope that helps

yk