Skip to Main Content
  • Questions
  • Complete transient session for testing


Question and Answer

Connor McDonald

Thanks for the question, H.E..

Asked: October 19, 2020 - 12:01 pm UTC

Answered by: Connor McDonald - Last updated: October 21, 2020 - 3:40 am UTC

Category: Database Development - Version: 12c

Viewed 100+ times

You Asked

We have looked at dbms_flashback and dbms_wm but both don't deliver what we actually want:

Is there a possibility to make an entire session transient?

We would like to use this for our testing scenario's:

1. Start the session in "transient mode"
2. execute a lot of pl/sql with commits, rollbacks and savepoints
3. drop/stop the session

....and everything is magically back to before point 1.

dbms_flashback.ENABLE_AT_SYSTEM_CHANGE_NUMBER is almost there by showing the data at a specified scn,
but then you have to reverse every table that is hit by the -third party- code by hand. Not feasible in our environment

I'm not very optimistic, but perhaps I missed a new capability of the DB.

Is there a way?

and we said...

Not really...

There is "flashback pluggable database" so if you can run a test in its own pluggable database could take advantage of that.

Similarly you could create a thin snapshot clone of a pluggable, do your test and just throw the clone away.

If you can get to 19c, you can have 3 pluggables per database without any license implications

More to Explore


Check out more PL/SQL tutorials on our LiveSQL tool.