Oracle Rollback Faqs With Examples

1. What is ROLLBACK?

ROLLBACK is a TCL (Transaction Control Language) command used to undo changes made in the current transaction that have not been committed.

Syntax:

ROLLBACK;

Example:

UPDATE employees
SET salary = 60000
WHERE employee_id = 101;

ROLLBACK;

The salary change is undone.

2. Why do we use ROLLBACK?

ROLLBACK is used when you want to cancel changes made during the current transaction.

Example:

DELETE FROM employees
WHERE employee_id = 101;

ROLLBACK;

The deleted row is restored because the deletion had not been committed.

3. Is ROLLBACK DDL, DML, or TCL?

ROLLBACK is a TCL command. Common TCL commands are:

• COMMIT
• ROLLBACK
• SAVEPOINT

4. What happens when ROLLBACK is executed?

When you execute ROLLBACK, Oracle:

1. Undoes the uncommitted changes in the current transaction.
2. Ends the current transaction.
3. Releases transaction-related locks.
4. Removes the transaction's savepoints.

5. ROLLBACK with INSERT

Suppose:

INSERT INTO students
(student_id, student_name)
VALUES
(101, 'Ravi');

Before COMMIT, execute:

ROLLBACK;

The inserted row is undone.

Flow:

INSERT
↓
Uncommitted change
↓
ROLLBACK
↓
INSERT is undone

6. ROLLBACK with UPDATE

UPDATE employees
SET salary = 80000
WHERE employee_id = 101;

ROLLBACK;

The employee's salary returns to its previous value.

7. ROLLBACK with DELETE

DELETE FROM employees
WHERE employee_id = 101;

ROLLBACK;

The deleted row is restored.

8. COMMIT vs ROLLBACK

COMMIT	ROLLBACK
Saves changes	Undoes uncommitted changes
Makes changes permanent	Cancels changes
Ends the transaction	Ends the transaction
Releases transaction-related locks	Releases transaction-related locks

Easy memory trick:

• COMMIT = Save
• ROLLBACK = Undo

9. Can ROLLBACK undo a COMMIT?

No.

Example:

UPDATE employees
SET salary = 90000
WHERE employee_id = 101;

COMMIT;

ROLLBACK;

The ROLLBACK cannot undo the already committed update. ROLLBACK can undo uncommitted changes, not already committed changes.

10. What happens if we execute ROLLBACK without making changes?

ROLLBACK;

If there are no uncommitted changes, there is simply nothing to undo.

11. Can ROLLBACK undo multiple statements?

Yes.

Example:

INSERT INTO students
VALUES (101, 'Ravi');

UPDATE employees
SET salary = 50000
WHERE employee_id = 101;

DELETE FROM employees
WHERE employee_id = 102;

ROLLBACK;

The uncommitted changes from all three statements are undone.

12. ROLLBACK with SAVEPOINT

A SAVEPOINT allows you to undo only part of a transaction.

Example:

INSERT INTO students
VALUES (101, 'Ravi');

SAVEPOINT sp1;

UPDATE employees
SET salary = 60000
WHERE employee_id = 101;

ROLLBACK TO sp1;

What happens?

INSERT
↓
SAVEPOINT sp1
↓
UPDATE
↓
ROLLBACK TO sp1
↓
UPDATE is undone
↓
INSERT remains

So ROLLBACK TO sp1 does not roll back the entire transaction.

13. What is ROLLBACK TO SAVEPOINT?

Syntax:

ROLLBACK TO savepoint_name;

Example:

SAVEPOINT before_update;

UPDATE employees
SET salary = salary + 5000
WHERE employee_id = 101;

ROLLBACK TO before_update;

Only the changes made after before_update are undone.

14. Does ROLLBACK TO SAVEPOINT end the transaction?

No. This is an important exam point.

Example:

INSERT INTO students
VALUES (101, 'Ravi');

SAVEPOINT sp1;

UPDATE employees
SET salary = 60000
WHERE employee_id = 101;

ROLLBACK TO sp1;

The transaction is still active after ROLLBACK TO sp1. You can still perform more operations and eventually COMMIT or ROLLBACK.

15. ROLLBACK vs ROLLBACK TO

ROLLBACK	ROLLBACK TO SAVEPOINT
Undoes the entire current transaction	Undoes changes after a savepoint
Ends the transaction	Does not end the transaction
Removes transaction savepoints	The specified savepoint remains usable

Example:

ROLLBACK;

versus

ROLLBACK TO sp1;

16. Example with Multiple SAVEPOINTs

INSERT INTO students
VALUES (101, 'Ravi');

SAVEPOINT sp1;

UPDATE employees
SET salary = 50000
WHERE employee_id = 101;

SAVEPOINT sp2;

DELETE FROM employees
WHERE employee_id = 102;

ROLLBACK TO sp2;

The DELETE is undone, while the earlier changes remain.

17. What happens to SAVEPOINTs after ROLLBACK?

With a complete ROLLBACK, the current transaction ends and its savepoints are removed. With ROLLBACK TO sp1, the specified savepoint remains available, while savepoints created after it are removed.

18. Does ROLLBACK release locks?

Yes. A complete ROLLBACK ends the transaction and releases transaction-related locks.

Example:

UPDATE employees
SET salary = 70000
WHERE employee_id = 101;

ROLLBACK;

The transaction is ended and the locks associated with that transaction are released.

19. Does ROLLBACK make changes visible to other users?

ROLLBACK does not make your changes visible as committed changes. Instead, it removes the uncommitted changes from the transaction. Other sessions will continue to see data according to Oracle's transaction and read-consistency rules.

20. Does ROLLBACK work with DML?

Yes. DML includes:

• INSERT
• UPDATE
• DELETE
• MERGE

Example:

UPDATE employees
SET salary = 75000
WHERE employee_id = 101;

ROLLBACK;

The update is undone.

21. Can ROLLBACK undo DDL?

This is a very important Oracle question. Generally, no. Oracle performs implicit commits around DDL statements.

Example:

CREATE TABLE test (
    id NUMBER
);

You cannot use a later ROLLBACK to treat that DDL as an ordinary uncommitted DML change and undo it.

22. What happens when a DDL statement is executed?

Oracle performs an implicit commit before the DDL and, if the DDL succeeds, another implicit commit after it.

Example:

INSERT INTO students
VALUES (101, 'Ravi');

CREATE TABLE test (
id NUMBER
);

The CREATE TABLE causes implicit transaction boundaries, so you should not expect a later ROLLBACK to undo the earlier INSERT.

Exam point ⭐ DDL causes implicit commits in Oracle.

23. Does ROLLBACK undo a TRUNCATE?

No. TRUNCATE is DDL in Oracle and causes an implicit commit.

Example:

TRUNCATE TABLE employees;

ROLLBACK;

The ROLLBACK does not restore the rows removed by TRUNCATE.

Important comparison:

• DELETE + ROLLBACK → DELETE can be undone before COMMIT
• TRUNCATE + ROLLBACK → TRUNCATE cannot be undone by ROLLBACK

24. ROLLBACK after COMMIT

Consider:

DELETE FROM employees
WHERE employee_id = 101;

COMMIT;

ROLLBACK;

The ROLLBACK cannot restore employee 101 because the deletion was already committed.

25. What happens if a statement fails?

Suppose:

INSERT INTO employees
VALUES (101, 'Ravi', 50000);

INSERT INTO employees
VALUES (101, 'Kiran', 60000);

If the second statement fails because of a constraint violation, the first successful statement is not automatically rolled back merely because the second statement failed. You can decide what to do: COMMIT or ROLLBACK depending on the desired transaction outcome.

26. ROLLBACK in PL/SQL

BEGIN
    UPDATE employees
    SET salary = salary + 1000
    WHERE employee_id = 101;

```
ROLLBACK;
```

END;
/

The update is undone.

27. ROLLBACK Example — Complete Transaction

INSERT INTO employees
(employee_id, first_name, salary)
VALUES
(101, 'Ravi', 50000);

UPDATE employees
SET salary = 55000
WHERE employee_id = 101;

DELETE FROM employees
WHERE employee_id = 102;

ROLLBACK;

All three uncommitted DML changes are rolled back.

28. ROLLBACK Example — Partial Undo

INSERT INTO employees
(employee_id, first_name, salary)
VALUES
(101, 'Ravi', 50000);

SAVEPOINT s1;

UPDATE employees
SET salary = 60000
WHERE employee_id = 101;

ROLLBACK TO s1;

COMMIT;

Result:

• The INSERT remains.
• The UPDATE is undone.
• COMMIT saves the remaining transaction.

29. Frequently Asked Interview Questions

Question	Answer
What is ROLLBACK?	A TCL command that undoes uncommitted changes in the current transaction.
What is the syntax?	ROLLBACK;
Can ROLLBACK undo INSERT?	Yes, if the insert has not been committed.
Can ROLLBACK undo UPDATE?	Yes, if the update has not been committed.
Can ROLLBACK undo DELETE?	Yes, if the delete has not been committed.
Can ROLLBACK undo COMMIT?	No.
Does ROLLBACK end the transaction?	Yes, a complete ROLLBACK ends the current transaction.
Does ROLLBACK TO SAVEPOINT end the transaction?	No.
Does ROLLBACK release locks?	Yes, a complete rollback releases transaction-related locks.
Can ROLLBACK undo TRUNCATE?	No.
Can ROLLBACK undo CREATE TABLE?	No, not as an ordinary uncommitted DML operation, because DDL causes implicit commits.
What is the difference between ROLLBACK and ROLLBACK TO?	ROLLBACK undoes the entire current transaction, while ROLLBACK TO savepoint undoes only changes after the savepoint.

30. Important Exam Points

1. ROLLBACK is a TCL command.
2. It undoes uncommitted changes.
3. ROLLBACK ends the current transaction.
4. It releases transaction-related locks.
5. ROLLBACK can undo INSERT, UPDATE, DELETE, and MERGE changes before commit.
6. ROLLBACK cannot undo an already executed COMMIT.
7. ROLLBACK TO SAVEPOINT performs a partial rollback.
8. ROLLBACK TO SAVEPOINT does not end the transaction.
9. DDL causes implicit commits in Oracle.
10. TRUNCATE cannot be undone using normal ROLLBACK.

31. One-line Memory Trick

ROLLBACK = Undo uncommitted changes
ROLLBACK TO = Undo changes after a savepoint
COMMIT = Save changes permanently