QUESTION: Which two statements describe the consequence of issuing the ROLLBACK TO SAVE POINT a command in the session?

SQL> CREATE TABLE product
2 (pcode NUMBER(2),
3 pname VARCHAR2(10));

Table created.

SQL> INSERT INTO product VALUES (1,'pen');

1 row created.

SQL> INSERT INTO product VALUES (2,'pencil');

1 row created.

SQL> SAVEPOINT a;

Savepoint created.

SQL> UPDATE product SET pcode=10 WHERE pcode=1;

1 row updated.

SQL> select * from product;

PCODE      PNAME
---------- ----------
10         pen
2          pencil

SQL> SAVEPOINT b;

Savepoint created.

SQL> DELETE FROM product WHERE pcode=2;

1 row deleted.

SQL> COMMIT;

Commit complete.

SQL> select * from product;

PCODE      PNAME
---------- ----------
10         pen

SQL> DELETE FROM product WHERE pcode=10;

1 row deleted.

SQL> ROLLBACK TO SAVEPOINT a;
ROLLBACK TO SAVEPOINT a
*
ERROR at line 1:
ORA-01086: savepoint 'A' never established in this session or is invalid

Which two statements describe the consequence of issuing the ROLLBACK TO SAVE POINT a command in the session? (Choose two.)

A. The rollback generates an error

B. No SQL statements are rolled back

C. Only the DELETE statements are rolled back

D. Only the seconds DELETE statement is rolled back

E. Both the DELETE statements and the UPDATE statement are rolled back

Answer: A,B

SAVEPOINT is a feature used to control a long-running set of SQL statements within a transaction. They can be used as rollback points, undoing everything that came after the SAVEPOINT was set. Once a COMMIT is executed, though, the SAVEPOINTS are gone because the transaction is done. Following is an example of a question for the 1Z0-051 exam concerning SAVEPOINTs.

A transaction is a unit of work containing SQL statements. Transactions end when they are committed or rolled back, or when a DDL statement is issued. Commits make the changes in the transaction permanent. Rollbacks undo the changes.

Using SAVEPOINTs, you can rollback a portion of a transaction. SAVEPOINTs are like bookmarks. Everything from where you are back to the SAVEPOINT can be undone. This breaks up the transaction into smaller pieces. SAVEPOINTs can be created before functions or procedures are ran, giving you the option to re-run the function or procedure by rolling back to the SAVEPOINT and trying again.

ROLLBACK TO SAVEPOINT will do the following:

• Rolls back statements that occur after the SAVEPOINT was created
• Release locks on the data changed back to that SAVEPOINT
• Keeps the SAVEPOINT used in the rollback but releases SAVEPOINTs occuring after

When you COMMIT, all of the SAVEPOINTs created in that transaction are removed because SAVEPOINTs are only able to be used within a transaction. COMMITs end transactions and commit them permanently to the database.