（172）The SQL statements executed in a user session are as follows:

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

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

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

SAVEPOINT a;

UPDATE product SET pcode = 10 WHERE pcode = 1;

SAVEPOINT b;

DELETE FROM product WHERE pcode = 2;

COMMIT;

DELETE FROM product WHERE pcode=10;

Which two statements describe the consequences 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 second DELETE statement is rolled back.

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

答案：（A、B）

解析：

执行完commit语句后，保存点a已经被释放。

--1. DML操作

scott@TESTDB11>delete from emp1 where empno = 7369;

1 row deleted.

--2. 创建保存点

scott@TESTDB11>savepoint sp_delete;

Savepoint created.

--3. 提交

scott@TESTDB11>commit;

Commit complete.

--4. 回滚到前面创建的保存点出错,因为事务已经提交,保存点已经释放

scott@TESTDB11>rollback to sp_delete;

rollback to sp_delete

*

ERROR at line 1:

ORA-01086: savepoint 'SP_DELETE' never established in this session or is invalid

ITPub博客

OCP 1Z0-051 172