---

Oracle / PLSQL: Rollback behavior and DDL's

Question: I'm working in a SQL prompt (like SQLPlus). After doing a few DML operations, I execute a ROLLBACK in which cases it rolls back to the proper save point area.

However, if I execute a DDL in the middle of the DML operations, I don't ROLLBACK to the same save point. The DDL's are not related to the table that I'm executing the DML's for. How come Oracle rolls back to a different save point when I execute a DDL?

Answer: First, let's explain some of the terminology that you used in your question.

DML stands for "Data Manipulation Language". A DML operation includes SELECT, INSERT, UPDATE, and DELETE statements.

DDL stands for "Data Definition Language". A DDL operation includes CREATE TABLE, CREATE INDEX, among other operations.

The Rollback statement undoes all changes for the current session up to the savepoint specified.

When you execute a DDL operation, there is an implied commit after the DDL. The save point is then changed to the position following the DDL operation. Therefore, any rollbacks will only undo changes to the new save point - which is after the last DDL operation.