Use the ROLLBACK statement to undo work done in the current transaction or to manually undo the work done by an in-doubt distributed transaction.
An implicit ROLLBACK occurs when the session (or program) abnormally terminates.
An explicit commit occurs when the ROLLBACK statement is executed.
rollback [work] to savepoint;
If the savepoint is unknown, Oracle will display a warning but the transaction can continue.
rollback
Oracle Database supports statement-level atomicity, which means that a SQL statement is an atomic unit of work and either completely succeeds or completely fails.
A successful statement is different from a committed transaction. A single SQL statement executes successfully if the database parses and runs it without error as an atomic unit, as when all rows are changed in a multirow update.
If a SQL statement causes an error during execution, then it is not successful and so all effects of the statement are rolled back. This operation is a statement-level rollback. This operation has the following characteristics:
The unsuccessful statement does not cause the loss of any work that preceded it in the current transaction. For example, if the execution of the second UPDATE statement in Figure 10-1 causes an error and is rolled back, then the work performed by the first UPDATE statement is not rolled back. The first UPDATE statement can be committed or rolled back explicitly by the user.
Any side effects of an atomic statement, for example, triggers invoked upon execution of the statement, are considered part of the atomic statement. Either all work generated as part of the atomic statement succeeds or none does.
An example of an error causing a statement-level rollback is an attempt to insert a duplicate primary key. Single SQL statements involved in a deadlock, which is competition for the same data, can also cause a statement-level rollback. However, errors discovered during SQL statement parsing, such as a syntax error, have not yet been run and so do not cause a statement-level rollback.
gerardnico@orcl>create table big_table as select * from all_objects where 1=0;
Table created.
gerardnico@orcl>select count(*) from big_table;
COUNT(*)
----------
0
gerardnico@orcl>insert INTO big_table SELECT * FROM all_objects
66651 rows created.
gerardnico@orcl>select count(*) from big_table;
COUNT(*)
----------
66651
gerardnico@orcl>rollback;
Rollback complete.
gerardnico@orcl>select count(*) from big_table;
COUNT(*)
----------
0