What do rollback and commit

When we COMMIT, all that is left to happen is the following:
• An SCN is generated for our transaction. In case you are not familiar with it, the SCN is
a simple timing mechanism Oracle uses to guarantee the ordering of transactions and
to enable recovery from failure. It is also used to guarantee read-consistency and check-
pointing in the database. Think of the SCN as a ticker; every time someone COMMITs, the
SCN is incremented by one.
• LGWR writes all of our remaining buffered redo log entries to disk and records the SCN in
the online redo log files as well. This step is actually the COMMIT. If this step occurs, we
have committed. Our transaction entry is “removed” from V$TRANSACTION—this shows
that we have committed.
• All locks recorded in V$LOCK held by our session are released, and everyone who was
enqueued waiting on locks we held will be woken up and allowed to proceed with their
work.
When we ROLLBACK,
• We undo all of the changes made. This is accomplished by reading the data back from
the undo segment, and in effect, reversing our operation and then marking the undo
entry as applied. If we inserted a row, a ROLLBACK will delete it. If we updated a row, a
rollback will reverse the update. If we deleted a row, a rollback will re-insert it again.
• All locks held by our session are released, and everyone who was enqueued waiting on
locks we held will be released.
A COMMIT, on the other hand, just flushes any remaining data in the redo log buffers. It
does very little work compared to a ROLLBACK. The point here is that you don’t want to roll back
unless you have to. It is expensive since you spend a lot of time doing the work, and you’ll also
spend a lot of time undoing the work. Don’t do work unless you’re sure you are going to want
to COMMIT it. This sounds like common sense—of course I wouldn’t do all of the work unless I
wanted to COMMIT it. Many times, however, I’ve seen a situation where a developer will use a
“real” table as a temporary table, fill it up with data, report on it, and then roll back to get rid
of the temporary data. In the next section, we’ll talk about true temporary tables and how to
avoid this issue.