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:
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:
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:
Got a solution?
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!
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;
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;
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!
Just getting started? Learn SQL with Databases for Developers: Foundations. Improve your skills with Databases for Developers: Next Level and Analytic SQL for Developers.