Generally we use COMMIT after the completion of DML activity to make the changes permanently to disk.
The COMMIT statement ends a transaction successfully.
Before the COMMIT statement is issued, the changes may not be visible to other transactions.
SQL> COMMIT;
Before Commit:
SQL> COMMIT;
Before Commit:
1. As we know when ever data maiplulation happens by DML queries, old values will be stored in undo tablespace
2. Oracle generates redo log entries in the redo log buffers in the SGA.
3. Oracle modifies the database buffers in the SGA.
AFTER Commit:
1.The transaction tables in the redo records are tagged with the unique system change number
(SCN) of the committed transaction.
2.The log writer writes the redo log information for the transaction from the redo log buffer to
the redo log files on disk, along with the transaction’s SCN. This is the point at which a commit
is considered complete in Oracle.
3. Oracle modifies the database buffers in the SGA.
AFTER Commit:
1.The transaction tables in the redo records are tagged with the unique system change number
(SCN) of the committed transaction.
2.The log writer writes the redo log information for the transaction from the redo log buffer to
the redo log files on disk, along with the transaction’s SCN. This is the point at which a commit
is considered complete in Oracle.
3.Any locks that Oracle holds are released, and Oracle marks the transaction as complete.