How to Rollback After Create Table Commits in Oracle Database

When you run DDL in Oracle Database, it issues two implicit commits. One before the statement and one after (successful!) completion.

I run the following commands:

◉ Create a table

◉ Insert a row in it

◉ Create another table

◉ Rollback

◉ Now the first table is EMPTY!

How?!

To help, here's the script I used to do this with some code REMOVED:

create 

table my_tab (

  c1 int

)

/

insert into my_tab values ( 1 );

create 

table ora$my_tab (

  c1 int

) 

/

rollback;

select count (*) from my_tab;

What code do you need to add so the count returns zero?

Here are the rules for this challenge:

◉ All the code runs in a single session

◉ All the statements succeed (there are no errors)

◉ There are no other objects in play (no triggers, jobs or other shenanigans)

◉ There are no other statements between creating the first table and the query at the end

Pause a moment to consider how this is possible. There are (at least!) three ways.

While you're thinking about it, here's a picture of some cats:

Ryan McGuire / Gratisography

Got a solution?

Here's three:

Create a Private Temporary Table

My original solution was to make the second table a private temporary table (PTT). Added in Oracle Database 18c, these no longer have implicit commits!

This means any DML run before creating a PTT remains uncommitted. So when the rollback executes, it undoes the insert.

To use a PTT, the start of its name must match the value set for the private_temp_table_prefix parameter.

So to make the script work, you must change this to ora$my_ beforehand!

Giving a final solution of:

alter session set private_temp_table_prefix = ora$my_ deferred;

<restart the database> 

create

table my_tab (

  c1 int

)

/

insert into my_tab values ( 1 );

create private temporary

table ora$my_tab (

  c1 int

)

/

rollback;

select count (*) from my_tab;

If you run this, please be sure to set private_temp_table_prefix back to ora$ptt_ when you're done!

Create a Global Temporary Table

While PTT was my intended solution, Tobias Wirtz was quick to share another. You can get this effect by making the first table a global temporary table (GTT)!

This has a default clause of on commit delete rows.

So when the implicit commit kicks in when creating the second table, the GTT empties itself!

Here's an example if you want to try it for yourself:

create global temporary 

table my_tab (

  c1 int

)

/

insert into my_tab values ( 1 );

create 

table ora$my_tab (

  c1 int

)

/

rollback;

select count (*) from my_tab;

Use an Autonomous Transaction

Another neat solution came in from Hans Jakob Schelbeck-Pedersen. He pointed out that you can wrap the second create table in an autonomous transaction. This commits and rolls back independent of the parent transaction.

So even though the second create table does commit, this leaves the original transaction alone. So you can still roll it back. If you need to create a table part-way through a transaction before 18c, this is the way to do it.

If you want to try it out:

create 

table my_tab (

  c1 int

)

/

insert into my_tab values ( 1 );

DECLARE

  PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

execute immediate '

create 

table ora$my_tab (

  c1 int

)

'; 

END;

/

  

rollback;

select count (*) from my_tab;

Source: oracle.com