Hello. I am a novice in the data integration field. We have multiple Oracle databases, and we are looking at different options that will help to make things more organized and provide the same level of service to users.
I have a few questions:
- When to consolidate data sets into a single database?
- What are the advantages and disadvantages of integrating at the database, application and presentation layers? When is each appropriate?
I would really appreciate any help on the matter. Thanks.
There are a number of questions you need to ask:
- Are the databases related in any way?
- Do you carry out a lot of inter-database transactions?
- What are the databases used for (i.e., are they DSS or OLTP or a mix)?
If the answer to (1) and/or (2) is yes and the workload is similar(ish) then it may be better to consolidate databases. Even if the workload is different then you can look into the "resource manager" to segregate resources. At one site I had five separate but related databases for a very large data warehouse. I combined all five databases into one large one with data still separated out using different schemas. The resultant database, although large, was easy to manage, and the query + load time for the majority of the processes improved.
Dig Deeper on Oracle database design and architecture
Related Q&A from Harish Harbham
SOA and Integration expert Harish Harbham explains how to transfer data from Oracle to MS SQL Server. Continue Reading
Oracle expert Harish Harbham explains how Oracle Streams can be used for replication. Continue Reading
Expert Harish Harbham answers the question, "How do you convert SQL Server 2005 database into 0racle 10g?" Continue Reading