A way to be able to clone a set of schemas into a sandbox/virtual database for the purpose of testing/CI builds.
Current option is to extract the DDL/exp/expdp and run in as dev user schemas, e.g. testsr1, testsr2.
Often times this will fail if objects are directly reference between schemas in PLSQL e.g. select * from schema1.table1. In a CI build we have to run grep/sed to fix these for the CI build.
What might be better if there was a facility to create a virtual/sandbox database that would clone a set of schemas to the sandbox. Each developer would have their own sandbox. The sandboxes would exists with a single instance to avoid having many databases. Teams, developers, sprints, projects could have their own sandbox database.
CREATE SANDBOX testsr;
GRANT SYSDBA ON testsr TO <owner> ; -- this to allow the dev user to act as a virtual DBA in this sandbox ... idea is to allow devs to self manage as much as possible to reduce reliance on DBA availability.
CLONE SCHEMAS schema1, schema2 INTO SANDBOX testsr;
The result would be that under the instance there would be the normal schemas schema1 and schema2, but in addition there would be schema1 and schema2 under sandbox testsr.
The tricky part is to make this transparent to applications. Either via a special connection string that restricts schema1 under testsr to just the objects under testsr, or a context.
The main benefit would be to allow teams to assign developers their own virtual database environment which they would manage without the need to increase the number of instances.