Save Points are used to divide a transaction into smaller phases. It enables rolling back part of a transaction. There are maximum 5 save points allowed in Oracle Database. Whenever an error is encountered, it is possible to rollback from the point where the SAVEPOINT has been saved.

In Oracle databases, a savepoint is a point within a transaction to which you can later roll back. Savepoints provide a way to divide a transaction into smaller segments and selectively roll back to a specific point without affecting the entire transaction.

The usage of savepoints in Oracle databases includes:

Partial Rollback: Savepoints allow you to roll back only part of a transaction to a specific point, rather than rolling back the entire transaction. This can be useful in complex transactions where you want to undo specific changes without affecting the entire set of operations.
Error Handling: Savepoints can be used for better error handling. If an error occurs during a transaction, you can roll back to a savepoint and handle the error at a more granular level, potentially avoiding a complete transaction rollback.
Nested Transactions: Savepoints support nested transactions. You can set multiple savepoints within a transaction, providing a way to structure and manage the transaction logic in a more modular fashion.

Here is a simple example to illustrate the concept:

-- Start a transaction

START TRANSACTION;

— Make changes
INSERT INTO table1 VALUES (1, ‘Data’);

— Set a savepoint
SAVEPOINT my_savepoint;

— Make more changes
UPDATE table1 SET column2 = ‘NewData’ WHERE column1 = 1;

— If an error occurs, roll back to the savepoint
ROLLBACK TO my_savepoint;

— Continue with the transaction
COMMIT;

In this example, if an error occurs after the savepoint is set, you can roll back only the changes made after that savepoint (UPDATE statement) without affecting the initial INSERT statement.

Keep in mind that the specific syntax may vary slightly depending on the Oracle Database version you are using.