This content has been marked as final. Show 3 replies
SAVEPOINT gives a name to and marks a point in the processing of your transaction. This marker allows you to ROLLBACK TO that point, erasing any changes and releasing any locks issued after that savepoint, but preserving any changes and locks which occurred before you marked the savepoint.
The syntax for the SAVEPOINT statement is:
where savepoint_name is an undeclared identifier. This means that it must conform to the rules for an Oracle identifier (up to 30 characters in length, starting with a letter, containing letters, numbers and #, $, or _ ), but that you do not need to (nor can you) declare that identifier.
Savepoints are not scoped to PL/SQL blocks. If you reuse a savepoint name within the current transaction, that savepoint is "moved" from its original position to the current point in the transaction, regardless of the procedure, function, or anonymous block in which the SAVEPOINT statements are executed. As a corollary, if you issue a SAVEPOINT inside a recursive program, a new SAVEPOINT is executed at each level of recursion, but you can only roll back to the most recently marked savepoint.
Thanks Syed for ur response on the usage of SAVEPOINTs. I would like to know that how SAVEPOINT works internally using Oracle architecture. For eg., following happens when COMMIT is issued for changes made to data :
1. The server process modifies data in the system global area. The DBWR process writes modified blocks permanently to disk. Because the transaction is committed, the LGWR process immediately records the transaction in the online redo log file.
2. If the transaction is successful, then the server process sends a message across the network to the application. If it is not successful, then an error message is transmitted.
I also want to know why frequent use of SAVEPOINT reduces performance.
Frequent uses of savepoint will not reduce performance. Frequent rollbacks to the savepoint will. A savepoint is nothing more then a pointer to a particular point in the rollback log. If you issue a rollback, everything is rolled back and all savepoints are removed. If you rollback to a savepoint, ONLY those uncommitted changes from the savepoint forward are rolledback. Every thing before the savepoint is still uncommitted. When you commit, all the savepoints are propped and all uncommited changes are committed.