We have two Oracle 8i database instances on a Solaris 7 box. Both of these databases have a single schema each. As the data in these databases is much related we decided to put both schemas in a single database (along with upgrading to Solaris 8 and 9i). What is the possible impact of having one instance with two schemas instead of two instances with a schema each? Any impact on SGA, processes, etc.?
Combining schemas into one database is done all the time without major problems. I would ensure that the two schemas are housed in the database with separate user accounts so that you do not have conflicts with object names. Then ensure that each account cannot see information in the other account, unless you have a very good reason to share information between applications. With this approach, you will not have too many problems having two different applications running in the same Oracle database.
There are a few things to think about though. You will have two application's user bases attaching to this single database so you will need to ensure that the database is configured properly to handle the resource requirements of both. This may mean that you need a larger SGA or more memory allocated to your PGA, etc. Another thing to look out for concerns Oracle versions. You may find that one vendor will support the latest and greatest version, while the other application vendor will not. In this case, you may not be able to upgrade until both application vendors support the version you want to upgrade to.
Dig Deeper on Oracle database design and architecture