I'm an Oracle newb.
The systems in question are strictly for developers to test against. We fire them up and then blow them away afterwards. Problem is that this takes as long as 8 minutes per build which is costing us severely in CI. Four minutes to erase and four minutes to rebuild. Also problematic is that we can only run one set of tests at a time because we only get one schema to test against.
One thing to consider is the possibility that a developer might check in something with screwed up transaction management, like something that does real commits with no transactional rollback.
What we were thinking of as a solution was to keep a copy of the schema that is kept up to date via automated means. Then do a straight copy of the entire schema to a unique name. Do the test run against the copy and then asynchronously remove it at the end.
Generally speaking, what is a good approach for this? We are working with Oracle running on our local machines in a Docker container. What I've been experimenting with is using the expdp/impdp tools. The Docker container adds a pain in the 6 O'clock, but I've been able to get limited success with this technique. In a nutshell I do the copy but then I get errors to the effect that several items pre-exist despite copying to a new name. I expect that this has something to do with the close association between Oracle Schemas and User's. I'll post examples of the errors I'm seeing as soon as I have a new test run.
Best Answer
The 12c Multitenant option is a nice tool for this.
Create a "master" pluggable database that is your starting point, and you can create other pluggable databases by cloning this PDB.
When you clone a PDB, you have the option to clone the PDB as a "snapshot copy". This does not actually copy the files, but uses storage snapshots, and that is what makes this feature great. For example, cloning a 111 GB database took 20 seconds in this example: Snapshot clone of a PDB using ACFS
When I experimented with it, smaller databases took even less time to be cloned. When you do not need the database anymore, you can just simply drop it.
Also you are not limited to 1 schema, because you can create many clones and use them at the same time concurrently, up to 252 PDBs.
Note that, the Multitenant option is an extra cost option for Enterprise Edtition databases. So you need the proper license to use it.
SNAPSHOT COPY