What is a COMMIT in Oracle?

In Oracle Database, a COMMIT is a SQL statement used to make all changes made during the current transaction permanent. Once you issue a COMMIT, the changes to the database (such as inserts, updates, or deletes) become visible to other users and cannot be rolled back.

 Key Points about COMMIT in Oracle:

1. Makes Changes Permanent:
• All the DML operations (INSERT, UPDATE, DELETE) performed in the transaction are saved permanently to the database.
2. Releases Locks:
• After a COMMIT, any locks held on the affected rows/tables are released, allowing other transactions to access them.
3. Ends the Transaction:
• COMMIT marks the successful end of a transaction. After it, a new transaction begins automatically.
4. Visible to Other Sessions:
• Changes are now visible to all other users and sessions once the COMMIT is executed.

Syntax:

COMMIT;

Example:

UPDATE employees

SET salary = salary * 1.10

WHERE department_id = 50;

COMMIT;

• The salary update will be permanently stored in the database after the COMMIT.
• Before the COMMIT, other users won’t see the changes, and you could still roll them back using ROLLBACK.

Auto-Commit Behavior:

• In some tools (like SQL Developer or SQL*Plus with autocommit on), every DML statement may be automatically committed.
• In PL/SQL blocks or transactions without autocommit, you need to explicitly use COMMIT.

Comparison: COMMIT vs ROLLBACK

Feature	COMMIT	ROLLBACK
Purpose	Save changes permanently	Undo all changes since last COMMIT
Reversibility	Irreversible	Reversible
Locks	Released	Released