SAVEPOINT

1.    SAVEPOINT in Oracle is used to mark a specific point within a transaction so that changes made after that point can be rolled back without affecting earlier work.

 

2.    SAVEPOINT allows partial rollback of a transaction, which is useful when a long transaction has multiple logical steps and only some of them need to be undone.

 

3.    The syntax for creating a savepoint is simple, using the SAVEPOINT savepoint_name statement, and it does not end the transaction.

 

4.    ROLLBACK TO SAVEPOINT can be used to undo changes made after the savepoint, while keeping the transaction active and preserving earlier changes.

 

5.    Multiple savepoints can be created within a single transaction, and Oracle allows reuse of the same savepoint name, which replaces the previous one.

 

6.    SAVEPOINT does not release any locks; locks are only released when a COMMIT or full ROLLBACK is issued.

 

7.    If a COMMIT or full ROLLBACK is performed, all savepoints created in that transaction are automatically erased.

 

8.    SAVEPOINT is frequently used in exception handling within PL/SQL blocks to recover from errors without rolling back the entire transaction.

 

9.    SAVEPOINT uses undo space because Oracle must store information required to roll back changes made after the savepoint.

 

10.   SAVEPOINT is an important transaction control feature that improves flexibility and control when managing complex database transactions.