SAVEPOINT FAQS

1) What is a SAVEPOINT in Oracle?

A SAVEPOINT is a marker within a transaction that allows you to roll back part of the transaction without rolling back the entire transaction.

It works with:

·        SAVEPOINT

·        ROLLBACK TO SAVEPOINT

·        COMMIT

2) What is the syntax for creating a SAVEPOINT?

SAVEPOINT savepoint_name;

Example:

SAVEPOINT before_update;

3) How do you roll back to a SAVEPOINT?

ROLLBACK TO savepoint_name;

Example:

ROLLBACK TO before_update;

This undoes changes made after the savepoint but keeps changes made before it.

4) What happens to SAVEPOINTS after COMMIT?

After a COMMIT:

·        All savepoints are erased.

·        You cannot roll back to any previous savepoint.

·        The transaction ends permanently.

5) What happens after a full ROLLBACK?

After:

ROLLBACK;

·        The entire transaction is undone.

·        All savepoints are removed.

6) Can you create multiple SAVEPOINTS in one transaction?

Yes.

SAVEPOINT sp1;

SAVEPOINT sp2;

SAVEPOINT sp3;

You can roll back to any of them.

7) What happens if you reuse the same SAVEPOINT name?

If you create a savepoint with the same name again, it overwrites the previous one and moves it to the current position in the transaction.

8) Can SAVEPOINT be used inside PL/SQL?

Yes.

BEGIN

   SAVEPOINT before_insert;

   INSERT INTO employees VALUES (101, 'John');

   ROLLBACK TO before_insert;

END;

/

9) Does SAVEPOINT release locks?

·        Rolling back to a savepoint releases locks acquired after that savepoint.

·        Locks acquired before the savepoint remain.

10) Can SAVEPOINT be used in autonomous transactions?

No. Savepoints in a parent transaction are not visible inside an autonomous transaction and vice versa.

Autonomous transactions use:

PRAGMA AUTONOMOUS_TRANSACTION;

11) Is there a limit to the number of SAVEPOINTS?

There is no fixed numeric limit, but too many savepoints can consume resources and affect performance.

12) When should you use SAVEPOINT?

Use SAVEPOINT when:

·        You need partial rollback capability.

·        You are performing multiple DML operations.

·        You are handling exceptions in PL/SQL.

·        You want better transaction control.

13) What is the difference between ROLLBACK and ROLLBACK TO SAVEPOINT?

ROLLBACK;
→ Undoes the entire transaction.

ROLLBACK TO savepoint;
→ Undoes changes after the savepoint only.

14) Can you roll back to a SAVEPOINT after DDL?

No.

DDL statements such as:
CREATE, ALTER, DROP, TRUNCATE

cause an implicit COMMIT. After DDL, savepoints are lost.

15) Does SAVEPOINT work across sessions?

No.

Savepoints are:

·        Session-specific

·        Transaction-specific

·        Not visible to other users