Savepoint of Oracle

What is savepoint?
Use
The SAVEPOINT statement to identify a point in a transaction to which you can
Later roll back.
For example:
SQL> SELECT * FROM SCOTT. DEPT;

DEPTNO
DNAME LOC
---------------------------------
10
ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30
SALES CHICAGO
40 OPERATIONS BOSTON
SQL> UPDATE
SCOTT. dept set loc = 'A' WHERE loc = 'New YORK ';

1 row updated
SQL>
SAVEPOINT;

Savepoint created
SQL> update scott. dept set loc = 'B'
WHERE loc = 'Dallas ';

1 row updated
SQL> SAVEPOINT
B;

Savepoint created
SQL> ROLLBACK TO SAVEPOINT;

Rollback
Complete
SQL> COMMIT;

Commit complete
SQL> SELECT * FROM
SCOTT. DEPT;

DEPTNO DNAME LOC
--------------------
-------------
10 ACCOUNTING
20 RESEARCH
DALLAS
30 SALES CHICAGO
40 OPERATIONS
BOSTON

Savepoints in a transaction
You can declare the intermediate tag called savepoint in the transaction context. Savepoint separates long transactions into smaller parts.
With savepoint, you can mark your operations at any point in a long transaction. Then you can choose to roll back the operations performed before the current point in the transaction and after the declared savepoint. For example, you can use savepoint in a long and complex update. If you make an error, you do not need to submit all statements again.
Savepoints is also useful in applications. If a process contains several functions, you can create a savepoint before each function. If a function fails, it is very easy to return data to the status before the function starts and re-run the function after modifying the parameters or performing a recovery operation.
After a savepoint is rolled back, Oracle releases the lock held by the rollback statement. Other transactions waiting for resources to be locked can be performed. Other transactions that are locked before update can also be executed.
When a transaction rolls back to a savepoint, the following events occur:
1.
Oracle only rolls back the statement after the savepoint.
2.
Oracle retains this savepoint, but all the savepoints established thereafter are lost.
3.
Oracle Releases all table and row locks obtained after the savepoint, but retains all previous locks.
The transaction is active and can continue.
No matter when a session is waiting for a transaction, the rollback to the savepoint will not release the row lock. To ensure that the transaction will not be hung if it cannot obtain the lock, use
UPDATE...
NOWAIT. (The lock obtained before the savepoint is rolled back. The row lock obtained after the savepoint is released, and the statements executed later are completely rolled back .)

Note:
1. savepoint
Keep the name unique
2. If the name of a newly set savepoint is the same as that of the previous one, the previous one will be canceled.
3. After the savepoint is set, the transaction can continue to commit, and all transactions can be rolled back or rolled back to a specific savepoints.
(Savepoint
Names must be distinct within a given transaction. If you create a second
Savepoint with the same identifier as an earlier savepoint, then the earlier
Savepoint is erased. After a savepoint has been created, you can either continue
Processing, commit your work, roll back the entire transaction, or roll back
The savepoint .)
4. The Undo operation must be effective only when the commit command is not issued.
Execute rollback after commit;
Failed to savepoint
SQL> SELECT * FROM SCOTT. DEPT;

DEPTNO
DNAME LOC
---------------------------------
10
ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30
SALES CHICAGO
40 OPERATIONS BOSTON
SQL> UPDATE
SCOTT. dept set loc = 'A' WHERE loc = 'New YORK ';

1 row updated
SQL>
SAVEPOINT;

Savepoint created
SQL> update scott. dept set loc = 'B'
WHERE loc = 'Dallas ';

1 row updated
SQL> SAVEPOINT
B;

Savepoint created
SQL> COMMIT;

Commit complete
SQL>
Rollback to savepoint;

Rollback to savepoint

ORA-01086:
Never created A reserved vertex 'A'
SQL> SELECT * FROM SCOTT. DEPT;

DEPTNO
DNAME LOC
---------------------------------
10
ACCOUNTING
20 RESEARCH B
30
SALES CHICAGO
40 OPERATIONS BOSTON