Introduction to Oraclesavepoint

Oraclesavepoint Introduction: by creating a checkpoint, we can roll back to any transaction that has created a checkpoint before the transaction is committed. The checkpoint does not exist after the transaction is committed.

Oracle savepoint Introduction: by creating a checkpoint, we can roll back to any transaction that has created a checkpoint before the transaction is committed. The checkpoint does not exist after the transaction is committed.

SAVEPOINT is provided by official documentation

1. Purpose:

Use the SAVEPOINT statement to identify a point in a transaction to which you can later roll back.

Use the checkpoint statement to identify a transaction point so that rollback can be performed later.

2. Prerequisites:

None.

3. Syntax:

SAVEPOINT savepoint;

4. The following is an example:

1) First, let's take a look at Banda's salary in the employee table.

SQL> select employee_id, last_name, salary from employees where last_name = 'banda ';

EMPLOYEE_ID LAST_NAME SALARY
----------------------------------------------
167 Banda 6200

2) Let's modify Banda's salary and create the checkpoint banda_sal:

SQL> update employees
2 set salary = 7000
3 where last_name = 'banda ';

1 row updated.

SQL> savepoint banda_sal;

The Save point has been created.

3) Modify Banda's salary and create the checkpoint banda_sal2:

SQL> update employees
2 set salary = 8000
3 where last_name = 'banda ';

1 row updated.

SQL> savepoint banda_sal2;

The Save point has been created.

SQL> select employee_id, last_name, salary from employees where last_name = 'banda ';

EMPLOYEE_ID LAST_NAME SALARY
----------------------------------------------
167 Banda8000

4) perform the rollback operation below to roll back to the checkpoint banda_sal:

SQL> rollback to savepoint banda_sal;

Rollback completed.

SQL> select employee_id, last_name, salary from employees where last_name = 'banda ';

EMPLOYEE_ID LAST_NAME SALARY
----------------------------------------------
167 Banda7000

5) Submit the job and perform the rollback operation:

SQL> commit;

Submitted.

SQL> rollback to savepoint banda_sal2;
Rollback to savepoint banda_sal2
*
Row 3 has an error:
ORA-01086: never created Save point 'banda _ sal2'

5. Conclusion:

1) by creating a checkpoint, we can roll back to any transaction that has created a checkpoint before the transaction is committed.

2) The checkpoint does not exist after the transaction is committed. This can be seen in the example.