1. What does COMMIT do in Oracle?

• COMMIT makes all changes in the current transaction permanent.
• It also releases locks held by the transaction and ends the transaction.

2. When should COMMIT be used?

Use COMMIT when you are satisfied with all DML operations, want changes visible to other sessions, or need to release locks.

3. What happens if COMMIT is not issued?

Until a COMMIT or ROLLBACK is executed:

• Changes remain temporary
• Modified rows stay locked
• Other sessions cannot see your changes
• An unexpected session termination causes an automatic rollback

4. Does Oracle automatically commit transactions?

Yes, in certain situations:

• Executing DDL statements (CREATE, ALTER, DROP)
• Exiting SQL*Plus normally
• When tools have auto-commit enabled

DML statements do not auto-commit.

5. Difference between COMMIT and ROLLBACK

COMMIT: Makes all changes permanent and ends the transaction
ROLLBACK: Undoes all changes since the last COMMIT or SAVEPOINT

6. Does COMMIT release locks?

Yes. All row-level locks acquired during the transaction are released.

7. Can a COMMIT be undone?

No. Once committed, the changes are permanent unless flashback features are configured.

8. What is an implicit COMMIT?

An automatic commit performed by Oracle before and after:

• DDL statements
• Exiting certain clients or tools

Implicit commits cannot be avoided.

9. Does COMMIT affect cursors?

• DML-related cursors close after COMMIT
• Read-only cursors remain open but may show inconsistent data depending on isolation level

10. Does COMMIT improve performance?

Not necessarily. Frequent commits can increase overhead and reduce performance, but they do help free undo, release locks, and reduce long transaction risks.

11. What happens internally during a COMMIT?

Oracle performs the following:

1.    Writes redo records to the redo log buffer

2.    Instructs LGWR to write redo to online redo logs

3.    Marks the transaction as complete

4.    Releases locks

12. Does COMMIT flush data to datafiles?

No. Dirty blocks are not written to datafiles during COMMIT. DBWR writes them later based on internal algorithms.

13. Can COMMIT be used inside PL/SQL procedures?

Yes, but it is generally discouraged because it removes transaction control from the caller and complicates error handling.

14. Is COMMIT WORK different from COMMIT?

No. COMMIT WORK is simply ANSI-standard syntax for COMMIT.

15. What is a two-phase commit?

A mechanism used in distributed transactions ensuring that all participating databases either commit or roll back the transaction together for consistency.