SAVEPOINT FAQS

1. What is the main purpose of a SAVEPOINT in Oracle?

A SAVEPOINT allows you to create a marker within a transaction to which you can roll back later. This helps in managing complex transactions by undoing only certain parts of the work rather than the entire transaction.

 

2. Can I have multiple SAVEPOINTs in a single transaction?

Yes, you can create multiple savepoints within a single transaction. Each savepoint represents a different point in time to which you can roll back.

 

3. How do I roll back to a specific SAVEPOINT?

To roll back to a specific savepoint, you can use the ROLLBACK TO SAVEPOINT command. For example:

ROLLBACK TO SAVEPOINT savepoint_name;

This undoes all changes made after the specified savepoint.

 

4. Can I commit a transaction after using SAVEPOINT?

Yes, you can commit a transaction after using savepoints. The commit will make all changes permanent, including those made before the savepoint. Once committed, you cannot roll back to the savepoint anymore.

 

5. What happens to SAVEPOINTs after a COMMIT?

Once a transaction is committed, all savepoints in that transaction are discarded. A COMMIT permanently saves the changes and ends the transaction, meaning no further rollbacks (to savepoints or otherwise) can be made.

 

6. Can I use SAVEPOINT outside of a transaction?

No, savepoints can only be used inside an active transaction. You cannot set a savepoint unless you have started a transaction using BEGIN.

 

7. What happens to a SAVEPOINT after a ROLLBACK without a SAVEPOINT?

If you execute a ROLLBACK without specifying a savepoint (i.e., rolling back the entire transaction), the transaction is completely undone, including all savepoints.

 

8. Is there a limit to how many SAVEPOINTs I can create?

There is no hard limit on the number of savepoints you can create in a transaction, but excessive use of savepoints may lead to performance overhead in complex transactions. It’s best to use them strategically.

 

9. Can I roll back a savepoint after a COMMIT?

No, after a commit, all savepoints are discarded, and you cannot roll back to a savepoint. Once a commit is executed, all changes are permanent.

 

10. Are SAVEPOINTs session-specific?

Yes, savepoints are specific to the session. If the session is rolled back, terminated, or disconnected, all savepoints are lost. They cannot be used across different sessions.

 

11. Do SAVEPOINTs persist across transactions?

No, savepoints only exist for the duration of a single transaction. Once the transaction is committed or rolled back, the savepoints are no longer available.

 

12. Can I use SAVEPOINT in PL/SQL blocks?

Yes, you can use SAVEPOINT inside PL/SQL blocks. For example:

BEGIN

  -- Perform some operations

  SAVEPOINT my_savepoint;

  -- Perform more operations

  ROLLBACK TO SAVEPOINT my_savepoint;

  COMMIT;

END;

 

13. How does SAVEPOINT affect performance?

While savepoints provide greater control over transactions, excessive use of them can lead to performance degradation, especially in large or complex transactions. Savepoints require Oracle to keep track of the transaction's state, which can add overhead.

 

14. Can I rollback to a savepoint in a stored procedure?

Yes, you can use SAVEPOINT and ROLLBACK TO SAVEPOINT within a stored procedure, as long as the stored procedure is part of an active transaction.

 

15. What happens if a SAVEPOINT name is duplicated?

Each savepoint name must be unique within a transaction. If you attempt to create a savepoint with a name that already exists, it will overwrite the previous savepoint with the same name.