COMMIT FAQS

1. What is the purpose of the COMMIT statement in Oracle?

• The COMMIT statement is used to permanently save all changes made during a transaction (such as inserts, updates, and deletes) to the database.
• It marks the end of a transaction and releases any locks held by the transaction.

2. What happens if you don't commit a transaction?

• If you don't commit a transaction, all changes made will not be saved permanently, and they will be lost if the session is closed or the transaction is rolled back.
• Additionally, locks on the data will remain in place, potentially affecting performance.

3. Can you commit multiple times in a transaction?

• You can issue a commit only once at the end of a transaction.
• However, you can use savepoints within a transaction to mark intermediate points where you can roll back to without losing the entire transaction.

4. What is the difference between COMMIT and ROLLBACK?

• COMMIT saves all changes made in the transaction permanently to the database, while ROLLBACK undoes all changes made since the last COMMIT, effectively canceling the transaction.

5. Can I commit in the middle of a PL/SQL block?

• Yes, you can issue a COMMIT statement inside a PL/SQL block to save changes.
• However, doing this will end the transaction, and any changes made prior to the commit will be permanently saved.

6. What are "AFTER COMMIT" triggers in Oracle?

• Oracle allows for AFTER COMMIT triggers, which are fired after a COMMIT operation is executed.
• These triggers are useful for performing actions such as logging or updating related systems once the transaction is confirmed.

7. What is the syntax for committing with a comment?

• You can commit changes and include a comment by using the following syntax:

·        COMMIT COMMENT 'Your description here';

This comment is saved as part of the commit record and can be used for auditing or tracking purposes.

8. Can I commit after every DML operation (INSERT, UPDATE, DELETE)?

• Yes, you can commit after each DML operation, but it’s generally recommended to commit changes at the end of a logical unit of work to avoid excessive I/O operations.
• Committing too frequently can also lead to locking issues and affect performance.

9. What is an Autonomous Transaction, and how does it relate to COMMIT?

• An autonomous transaction allows a subprogram or procedure to commit or roll back its own changes, independent of the parent transaction.
• This is done using the PRAGMA AUTONOMOUS_TRANSACTION directive in PL/SQL. These transactions are committed or rolled back without affecting the main transaction.

10. Can a COMMIT be issued in a trigger?

• You can issue a COMMIT inside a trigger, but it is generally not recommended.
• Committing within a trigger can lead to unexpected behavior, such as causing the transaction to commit before you expect or affecting the consistency of data across related triggers.

11. What is a distributed transaction, and how does COMMIT work in it?

• A distributed transaction involves multiple databases or systems.
• The COMMIT command in distributed transactions ensures that all systems involved in the transaction are updated.
• Oracle uses the two-phase commit protocol (2PC) to ensure consistency and durability across multiple systems when committing a distributed transaction.

12. What happens if there is a failure after a COMMIT?

• Once a COMMIT is issued, the changes are permanently written to the database and cannot be rolled back.
• However, if there is a system failure after a commit, the database’s transaction logs (redo logs) are used to recover and ensure that the transaction was properly committed.

13. Can I undo a COMMIT?

• Once a COMMIT has been executed, the changes are permanent and cannot be undone.
• To undo changes, you would have to perform another DML operation (like an UPDATE or DELETE) to manually revert the changes, if needed.

14. Does Oracle automatically commit after a certain command?

• Oracle does not automatically commit after a DML statement (INSERT, UPDATE, DELETE) unless explicitly instructed.
• By default, it operates in implicit mode, where you must manually commit after making changes.

15. Can a COMMIT be part of a batch of operations?

• Yes, multiple DML operations can be included in a single transaction, and a single COMMIT will save all changes made in that transaction. 
• However, if an error occurs before the commit, none of the changes will be saved unless specifically rolled back.

16. Is it necessary to commit after every transaction in Oracle?

• No, it's not strictly necessary to commit after each transaction.
• You can group multiple DML statements within a single transaction and commit them at once.
• This is often more efficient.
• However, failing to commit means your changes will be lost once the session ends or if you issue a rollback.

17. Can I commit without a transaction?

• No, you must have an active transaction to issue a COMMIT statement. 
• A transaction starts as soon as you begin making changes (e.g., executing an INSERT, UPDATE, or DELETE statement), and a COMMIT finalizes and saves the changes.

18. What is the difference between COMMIT and SAVEPOINT?

• A SAVEPOINT is used to mark a specific point in a transaction so that you can roll back to that point without affecting the entire transaction. 
• A COMMIT, on the other hand, permanently saves all changes made since the beginning of the transaction.