ROLLBACK FAQS

1. What is a ROLLBACK in Oracle?

• A ROLLBACK in Oracle is a command used to undo changes made in the current transaction. 
• It reverts the database to its state before the transaction began or to the last committed point.

2. What is the difference between ROLLBACK and COMMIT?

• A ROLLBACK undoes changes made during a transaction, ensuring that no modifications are saved in the database.
• A COMMIT, on the other hand, makes all changes permanent and saves them to the database.

3. Can you roll back a DDL statement in Oracle?

• No, DDL (Data Definition Language) statements like CREATE, ALTER, and DROP are automatically committed when they are executed.
• A ROLLBACK will not affect them once they've been executed.

4. What happens if I execute a ROLLBACK after a COMMIT?

• After a COMMIT is issued, the changes are permanent.
• If you issue a ROLLBACK afterward, it has no effect, since the transaction is already finalized.

5. Can I undo a ROLLBACK in Oracle?

• No, once a ROLLBACK is executed, it is irreversible.
• You cannot undo a rollback operation.

6. What is a SAVEPOINT in Oracle?

• A SAVEPOINT is a marker in the middle of a transaction.
• You can roll back to a specific savepoint instead of rolling back the entire transaction.
• This allows for partial rollbacks.

7. How does the ROLLBACK command interact with SAVEPOINTs?

• A ROLLBACK TO SAVEPOINT command rolls back the changes made after the specified savepoint, while preserving changes made before it. 
• It allows for finer control over the transaction rollback.

8. Does ROLLBACK affect other sessions?

• No, a ROLLBACK only affects the current session and transaction.
• Other sessions that are not part of the transaction are unaffected by the rollback.

9. Can ROLLBACK be used for undoing changes in all transactions?

•  No, ROLLBACK only undoes changes within the current transaction.
• It does not undo changes from completed transactions or other sessions.

10. What happens to locks during a ROLLBACK?

• When you execute a ROLLBACK, any locks that were acquired during the transaction are released.
• This allows other transactions to access the locked resources.

11. Can a ROLLBACK be issued without an explicit transaction?

• Yes, every DML (Data Manipulation Language) statement like INSERT, UPDATE, or DELETE implicitly begins a transaction.
• If you don't issue a COMMIT, Oracle assumes an implicit transaction, and you can roll it back if needed.

12. Is ROLLBACK used in distributed transactions?

• Yes, ROLLBACK can be used in distributed transactions, where it ensures that changes made across multiple databases are undone to maintain consistency.

13. What happens if an error occurs during a transaction in Oracle?

•  If a fatal error occurs during a transaction, Oracle automatically performs a ROLLBACK to restore the database to a consistent state.

14. Can a ROLLBACK be used in PL/SQL?

• Yes, ROLLBACK can be used in PL/SQL blocks to undo changes made during the execution of the block.
• It works in the same way as in regular SQL.

15. What happens if you use ROLLBACK after an INSERT statement?

• If you use ROLLBACK after an INSERT statement, the inserted data will be removed from the database, as though it was never added.

16. Can I use ROLLBACK to undo an UPDATE statement?

• Yes, if you issue a ROLLBACK after an UPDATE statement, the changes made to the updated records will be reverted to their original values.

17. How does ROLLBACK affect temporary tables?

• ROLLBACK also affects data in global temporary tables.
• If the data was modified during the transaction and not committed, it will be rolled back when the transaction is rolled back.

18. What is the performance impact of frequent ROLLBACKs?

• Frequent ROLLBACKs might have a performance overhead due to the undo operations involved, especially for large transactions.
• To minimize the impact, it's better to commit frequently, especially in long-running transactions.

19. Can ROLLBACK be used in triggers?

• Yes, ROLLBACK can be used in triggers to undo changes if certain conditions are met, although this is not common practice.
• It might lead to complex error handling and is typically avoided in triggers unless necessary.

20. Can I use ROLLBACK if I have already used ROLLBACK TO SAVEPOINT?

•  Yes, if you have used ROLLBACK TO SAVEPOINT, you can still issue a ROLLBACK without specifying a savepoint, which will undo all changes made in the transaction since its beginning, not just the ones after the savepoint.