I want to be able to import multiple database dumps locally and be able to switch between them.
What I would like to achieve:
- Import dumps (objects+data) from multiple databases to local database
- Switch between imported databases (
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
db1data 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
You can use the Multitenant option, create a container database, create 2 pluggable databases called
db2, then import the first dump file to
db1, the other dump file to
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:
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