René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

savepoint [Oracle SQL]

-- DML statements 
savepoint savepoint_name_1;
-- DML statements 
savepoint savepoint_name_2;
-- further DML statements 
savepoint savepoint_name_n;
-- further DML statements
rollback to savepoint savepoint_name_n

A little demonstration

create table savepoint_test (
  n number
);
Inserting some values:
insert into savepoint_test values (1);
insert into savepoint_test values (2);
insert into savepoint_test values (3);
Establishing a savepoint named sp_one:
savepoint sp_one;
Inserting three more rows:
insert into savepoint_test values (10);
insert into savepoint_test values (20);
insert into savepoint_test values (30);
Establishing a 2nd savepoint_test, this time named sp_two:
savepoint sp_two;
Again inserting some values:
insert into savepoint_test values (100);
insert into savepoint_test values (200);
insert into savepoint_test values (300);
Let's check what we have. Not surprisingly, there are nine rows:
select * from savepoint_test;
         N
----------
         1
         2
         3
        10
        20
        30
       100
       200
       300
Rolling back to savepoint sp_two:
rollback to sp_two;
Checking once more the table's content:
select * from savepoint_test;
         N
----------
         1
         2
         3
        10
        20
        30
All DMLS (data manipulated) after savepoint sp_2 have been rolled back.
Yet again, inserting some values ...
insert into savepoint_test values (111);
insert into savepoint_test values (222);
insert into savepoint_test values (333);
... and rolling back to sp_two;
rollback to sp_two;
select * from savepoint_test;
We're at savepoint sp_2 again, so we have the same result set again.
         N
----------
         1
         2
         3
        10
        20
        30
Rolling back to an even earlier time:
rollback to sp_one;
What do we have in the table now?
select * from savepoint_test;
Of course, we only find the values that have existed before savepoint sp_1:
         N
----------
         1
         2
         3

Links

See also On solving a sudoku with Oracle for a creative use of savepoints.