Oracle multiple databases on same server [closed]

Posted on

Question :

I want to be able to import multiple database dumps locally and be able to switch between them.

What I would like to achieve:

  1. Import dumps (objects+data) from multiple databases to local database
  2. Switch between imported databases (db1 and db2) locally

db1 and db2 have users/schemas/objects/etc with same name and this shouldn’t cause a conflict.

Expected result would be following:

  • connect with one USERNAME/PASSWORD and query tables from schema named MYSCHEMA and then db1 data would be used.
  • “switch” active database (by passing some additional parameter on connecting) or executing some command.
  • now same USERNAME/PASSWORD combination and querying tables from MYSCHEMA should return data from db2

Answer :

You can use the Multitenant option, create a container database, create 2 pluggable databases called db1 and db2, then import the first dump file to db1, the other dump file to db2.

You can switch between pluggable databases with as:

alter session set container=db1;
alter session set container=db2

But this requires at least version 12c, and it is an extra cost option.

For lower versions, or without the Multitenant option:

Simply create 2 traditional databases on the server: db1 and db2. Import one dump to db1, the other to db2. You can specify in your connection string the service (or SID) for the database you want to connect to. For example:

sqlplus user/password@hostname/db1
sqlplus user/password@hostname/db2

Leave a Reply

Your email address will not be published. Required fields are marked *