When to commit in a series of transactions is an oft-asked question, the answer to which depends upon what the user has done, is doing and plans on doing after the data changes are made. So, which operations commit automatically and which ones don’t? Obviously any DML requires a commit. Unless it’s followed by DDL. DDL issues commits automagically, both before the DDL starts and after the DDL completes. Now that we have that concept clear as mud, let’s look at an example:
SQL> -- SQL> -- Create test table SQL> -- SQL> create table commit_test(my_id number, 2 my_data varchar2(40), 3 my_color varchar2(12), 4 constraint commit_test_pk 5 primary key(my_id) 6 ); Table created. SQL> SQL> -- SQL> -- Load it up SQL> -- SQL> insert all 2 into commit_test 3 values (1, 'Lollie''s knickers are','blue') 4 into commit_test 5 values (2, 'Lollie''s knickers are','pink') 6 into commit_test 7 values (3, 'Lollie''s knickers are','orange') 8 into commit_test 9 values (4, 'Lollie''s knickers are','blue') 10 into commit_test 11 values (5, 'Lollie''s knickers are','blue') 12 into commit_test 13 values (6, 'Lollie''s knickers are','orange') 14 into commit_test 15 values (7, 'Lollie''s knickers are','blue') 16 into commit_test 17 values (8, 'Lollie''s knickers are','chartreuse') 18 into commit_test 19 values (9, 'Lollie''s knickers are','blue') 20 select * from dual; 9 rows created. SQL> SQL> -- SQL> -- Commit (we want to do this) SQL> -- SQL> commit; Commit complete. SQL> SQL> -- SQL> -- Check table contents SQL> -- SQL> select * 2 from commit_test; MY_ID MY_DATA MY_COLOR ---------- ---------------------------------------- ------------ 1 Lollie's knickers are blue 2 Lollie's knickers are pink 3 Lollie's knickers are orange 4 Lollie's knickers are blue 5 Lollie's knickers are blue 6 Lollie's knickers are orange 7 Lollie's knickers are blue 8 Lollie's knickers are chartreuse 9 Lollie's knickers are blue 9 rows selected. SQL> SQL> -- SQL> -- Make a 'temporary' update SQL> -- SQL> update commit_test 2 set my_color = 'cinnamon' 3 where my_color = 'blue' 4 / 5 rows updated. SQL> SQL> -- SQL> -- Check table contents SQL> -- SQL> select * 2 from commit_test; MY_ID MY_DATA MY_COLOR ---------- ---------------------------------------- ------------ 1 Lollie's knickers are cinnamon 2 Lollie's knickers are pink 3 Lollie's knickers are orange 4 Lollie's knickers are cinnamon 5 Lollie's knickers are cinnamon 6 Lollie's knickers are orange 7 Lollie's knickers are cinnamon 8 Lollie's knickers are chartreuse 9 Lollie's knickers are cinnamon 9 rows selected. SQL> SQL> -- SQL> -- We SHOULD rollback here, but we SQL> -- execute a CTAS instead SQL> -- SQL> create table commit_test_save 2 as select * from commit_test; Table created. SQL> SQL> -- SQL> -- Check original table contents SQL> -- SQL> -- Ooops! We committed our changes SQL> -- SQL> select * 2 from commit_test; MY_ID MY_DATA MY_COLOR ---------- ---------------------------------------- ------------ 1 Lollie's knickers are cinnamon 2 Lollie's knickers are pink 3 Lollie's knickers are orange 4 Lollie's knickers are cinnamon 5 Lollie's knickers are cinnamon 6 Lollie's knickers are orange 7 Lollie's knickers are cinnamon 8 Lollie's knickers are chartreuse 9 Lollie's knickers are cinnamon 9 rows selected.
Well, gee, our temporary changes were made permanent by the DDL creating our table of saved data. Let’s try this again, and rollback our changes before we try to create that second table:
SQL>
SQL> --
SQL> -- Truncate original table, drop
SQL> -- table with saved data
SQL> --
SQL> truncate table commit_test;
Table truncated.
SQL> drop table commit_test_save;
Table dropped.
SQL>
SQL> --
SQL> -- Load up original table again
SQL> --
SQL> insert all
2 into commit_test
3 values (1, 'Lollie''s knickers are','blue')
4 into commit_test
5 values (2, 'Lollie''s knickers are','pink')
6 into commit_test
7 values (3, 'Lollie''s knickers are','orange')
8 into commit_test
9 values (4, 'Lollie''s knickers are','blue')
10 into commit_test
11 values (5, 'Lollie''s knickers are','blue')
12 into commit_test
13 values (6, 'Lollie''s knickers are','orange')
14 into commit_test
15 values (7, 'Lollie''s knickers are','blue')
16 into commit_test
17 values (8, 'Lollie''s knickers are','chartreuse')
18 into commit_test
19 values (9, 'Lollie''s knickers are','blue')
20 select * from dual;
9 rows created.
SQL>
SQL> --
SQL> -- Commit (we want to do this)
SQL> --
SQL> commit;
Commit complete.
SQL>
SQL> --
SQL> -- Check table contents
SQL> --
SQL> select *
2 from commit_test;
MY_ID MY_DATA MY_COLOR
---------- ---------------------------------------- ------------
1 Lollie's knickers are blue
2 Lollie's knickers are pink
3 Lollie's knickers are orange
4 Lollie's knickers are blue
5 Lollie's knickers are blue
6 Lollie's knickers are orange
7 Lollie's knickers are blue
8 Lollie's knickers are chartreuse
9 Lollie's knickers are blue
9 rows selected.
SQL>
SQL> --
SQL> -- Make a 'temporary' update
SQL> --
SQL> update commit_test
2 set my_color = 'cinnamon'
3 where my_color = 'blue'
4 /
5 rows updated.
SQL>
SQL> --
SQL> -- Check table contents
SQL> --
SQL> select *
2 from commit_test;
MY_ID MY_DATA MY_COLOR
---------- ---------------------------------------- ------------
1 Lollie's knickers are cinnamon
2 Lollie's knickers are pink
3 Lollie's knickers are orange
4 Lollie's knickers are cinnamon
5 Lollie's knickers are cinnamon
6 Lollie's knickers are orange
7 Lollie's knickers are cinnamon
8 Lollie's knickers are chartreuse
9 Lollie's knickers are cinnamon
9 rows selected.
SQL>
SQL> --
SQL> -- We rollback here
SQL> --
SQL> rollback;
Rollback complete.
SQL>
SQL> --
SQL> -- NOW we save the original table
SQL> -- data
SQL> --
SQL> create table commit_test_save
2 as select * from commit_test;
Table created.
SQL>
SQL> --
SQL> -- Check original table contents
SQL> --
SQL> select *
2 from commit_test;
MY_ID MY_DATA MY_COLOR
---------- ---------------------------------------- ------------
1 Lollie's knickers are blue
2 Lollie's knickers are pink
3 Lollie's knickers are orange
4 Lollie's knickers are blue
5 Lollie's knickers are blue
6 Lollie's knickers are orange
7 Lollie's knickers are blue
8 Lollie's knickers are chartreuse
9 Lollie's knickers are blue
9 rows selected.
SQL>
SQL> --
SQL> -- Check saved data contents
SQL> --
SQL> select *
2 from commit_test_save;
MY_ID MY_DATA MY_COLOR
---------- ---------------------------------------- ------------
1 Lollie's knickers are blue
2 Lollie's knickers are pink
3 Lollie's knickers are orange
4 Lollie's knickers are blue
5 Lollie's knickers are blue
6 Lollie's knickers are orange
7 Lollie's knickers are blue
8 Lollie's knickers are chartreuse
9 Lollie's knickers are blue
9 rows selected.
SQL>
Now we have the original source data saved. Of course I’d have started off with the CTAS, but that wouldn’t have illustrated the concept of DDL implicit commits.
Yet another way to commit without committing is to simply exit your SQL*Plus session. A garden-variety exit is actually an ‘exit commit’, so any work you’ve done prior to the exit will be committed whether you wanted it to or not. A little-known option to exit is:
SQL> exit rollback
which will, miracle of miracles, rollback your pending work to the last commit. And it works, too:
SQL> create table mytest (mykey number, 2 myval varchar2(40), 3 constraint mytest_pk 4 primary key(mykey) 5 ); Table created. SQL> SQL> insert all 2 into mytest 3 values (1, 'Test 1') 4 into mytest 5 values (2, 'Test 2') 6 into mytest 7 values (3, 'Test 3') 8 into mytest 9 values (4, 'Test 4') 10 into mytest 11 values (5, 'Test 5') 12 select * 13 from dual; 5 rows created. SQL> SQL> select * from mytest; MYKEY MYVAL ---------- ---------------------------------------- 1 Test 1 2 Test 2 3 Test 3 4 Test 4 5 Test 5 SQL> SQL> exit rollback Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit ProductionWith the Data Mining option C:\sql\oraoft01\examples>sqlplus /nolog SQL*Plus: Release 10.2.0.3.0 - Production on Fri Dec 14 11:49:17 2007 Copyright (c) 1982, 2006, Oracle. All Rights Reserved. SQL> connect ********** Connected. SQL> select * from mytest; no rows selected SQL>
So you’ve seen two ways where commits can be executed without ever coding them. Which can be a blessing, or a curse, depending upon your situation.
Of course normally you’d want to commit your changes, so in those cases it’s best to simply submit a commit and let Oracle take care of the rest. When would you not want to commit? Inside a PL/SQL loop is one time, but that I’ll leave for another discussion.
Basically, if you’re changing data, commit. That way you’re not surprised later.