Question 2

Explanation

The two true statements regarding a SAVEPOINT are:

1. A SAVEPOINT does not issue a COMMIT

• A SAVEPOINT simply marks a point within a transaction to which you can roll back—it does not commit the transaction or any changes.

2. Rolling back to a SAVEPOINT can undo a DELETE statement

• A ROLLBACK TO SAVEPOINT can undo DML operations like DELETE, INSERT, or UPDATE performed after the savepoint was set.

False statements:

• "Only one SAVEPOINT may be issued in a transaction"
  False. You can create multiple SAVEPOINTs in a single transaction.

• "Rolling back to a SAVEPOINT can undo a TRUNCATE statement"
  False. TRUNCATE is a DDL statement, which performs an implicit COMMIT, so it cannot be rolled back.

• "Rolling back to a SAVEPOINT can undo a CREATE INDEX statement"
  False. CREATE INDEX is also DDL, which auto-commits—so it's not affected by savepoints.

A SAVEPOINT is a marker within a transaction that allows for a partial ROLLBACK. As changes are made in a transaction, we can create SAVEPOINTS to mark different points within the transaction. If we encounter an error, we can rollback to a SAVEPOINT or all the way back to the beginning of the transaction.

Row locks are NOT released by SETTING a SAVEPOINT. Row locks are release by one of three events - COMMIT, ROLLBACK, or ROLLBACK to SAVEPOINT. A SAVEPOINT does not issue a COMMIT.

http://www.dba-oracle.com/t_savepoint.htm

Note that a COMMIT removes all the SAVEPOINTS which may have been set earlier in the transaction.

http://psoug.org/definition.htm/SAVEPOINT.htm

To create a SAVEPOINT, Specify the name of the SAVEPOINT to be created. SAVEPOINT names must be distinct within a given transaction. If you create a second SAVEPOINT with the same identifier as an earlier SAVEPOINT, then the earlier SAVEPOINT is erased. After a SAVEPOINT has been created, you can either continue processing, COMMIT your work, roll back the entire transaction, or ROLLBACK to the SAVEPOINT. More than one SAVEPOINT can be issued per transaction.

https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10001.htm

There is no actual limit on the number of SAVEPOINTS allowed in a transaction, it is more a question of system resources.

https://stackoverflow.com/questions/39542862/how-many-savepoints-are-allowed-in-oracle-sql

In Oracle a DDL statement is a transaction in its own right simply because an implicit COMMIT is issued before the statement is executed and again afterwards.

This means that when a DDL statement is issued, it cannot be ROLLEDBACK. This also means that statements which do not COMMIT can be ROLLEDBACK.

https://stackoverflow.com/questions/9541013/oracle-what-statements-need-to-be-committed

DDL - Data Definition Language: statements used to define the database structure or schema CANNOT BE ROLLEDBACK because they issue an implicit COMMIT.

CREATE - to create objects in the database (All statements beginning with CREATE)

ALTER - alters the structure of the database (All statements beginning with ALTER)

DROP - delete objects from the database

TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed

COMMENT - add comments to the data dictionary

RENAME - rename an object

http://www.orafaq.com/wiki/SQL_FAQ#What_are_the_difference_between_DDL.2C_DML_and_DCL_commands.3F