:

Oracle Implicit Commit

The idea behind this test came from a script which was rolled into a production environment. The script had been tested but the incorrect data was only on the production database and we decided that we would leave the commit out of the script and see what the results were before committing it.

Within the script a temporary table was created and the results used in order to update one of the main tables in the database. At the end of the script the temporary table was dropped before the commit took place. Now, here is what happened to our script…

First of all we will set up the temporary table, TEMP_TAB, and the permanent table, DROP_TAB_COMMIT, and insert some data.

— This table is equivalent of a permanent table in the DB

CREATE TABLE drop_tab_commit (
id                  number(10),
name                varchar2(20));

insert into drop_tab_commit values (1,'Dave');
insert into drop_tab_commit values (2,'Dave');
insert into drop_tab_commit values (3,'Dave');
insert into drop_tab_commit values (4,'Dave');
insert into drop_tab_commit values (5,'Dave');
commit;

— This table represents a temporary table used to update the original. When I say temporary table in this case, I mean it in terms of we will drop it later not that it is of the Oracle temporary table type.

CREATE TABLE temp_tab (
id                  number(10),
name                varchar2(20));

insert into temp_tab values (1, 'Steve');
insert into temp_tab values (2, 'Steve');
insert into temp_tab values (3, 'Steve');
insert into temp_tab values (4, 'Steve');
insert into temp_tab values (5, 'Steve');
commit;

The next step demonstrates that the process of updating the  drop_tab_commit table based on the temp_tab and then rolling back the changes  does actually work.

— This is an update to the permanent table based on the  temporary table:

UPDATE drop_tab_commit
SET name = ‘Steve’
WHERE id IN (SELECT id FROM temp_tab);

— Check all rows have been updated

SELECT *
FROM drop_tab_commit;

— Rollback to check

ROLLBACK;

— Check that all rows have been put back to normal

SELECT *
FROM drop_tab_commit;

The 2nd part of this is to test what happens when you drop  the temp_tab table while you have outstanding updates which have not been  committed against the drop_tab_commit table.

— First of all we run the update again

UPDATE drop_tab_commit
SET name = ‘Steve’
WHERE id IN (SELECT id FROM temp_tab);

— Check all rows have been updated

SELECT *
FROM drop_tab_commit;

— Now we drop the temporary table and see the effect

DROP table temp_tab;

— Check to see that all changed rows have been committed.

SELECT *
FROM drop_tab_commit;

This shows that the changes which were based on the dropped  table have now been implicitly committed by Oracle.

The result of this shows that when we ran our script into the production environment the results were actually committed before we wanted them to be. This is just something that you have to be careful of when you didn’t write the script and are relying on someone else telling you that it’s all OK and has been tested.

Like it, share it...

Category: SQL


Related Posts

Comments (3)

Trackback URL | Comments RSS Feed

  1. Actually Oracle performs two commits. One commit before the DDL is issued, to make sure there is a stable database state and then one after the DDL so the changes are visible to every session currently connected and they can be used right away.

Leave a Reply

Your email address will not be published. Required fields are marked *