X

Celebrating the joy and power of Oracle SQL with the Oracle Developer Advocate team

  • January 6, 2020

How to Rollback After Create Table Commits in Oracle Database

Chris Saxon
Developer Advocate

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

Last week I posed the following quiz on Twitter which defies this rule.

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;

Summary

Private temporary tables break the long-held rule in Oracle Database that "DDL always commits". Things you've learned in the past may no longer by true. Keep learning!

And remember: there's always more than one way to complete a task. Can you think of any other ways to achieve the original challenge? Let us know in the comments!


Want to know more about SQL & Oracle Database? Follow me on Twitter for more database musings, quizzes, and SQL news. Or SQLDaily for daily tips on using SQL.

Just getting started? Learn SQL with Databases for Developers: Foundations. Improve your skills with Databases for Developers: Next Level and Analytic SQL for Developers.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.