Whenever DML operation is main in an oracle table,
the transaction never be saved unless or until save command is issued or a
discard is issued. TCL sublanguage is responsible data to do so TCL provide the
following statement.
--> Commit
(save)
--> Rollback
(discard)
--> Save
point
TCL mechanism:
Whenever DML operation is made in oracle table
just before a temporary memory is created in side in the sever technical is
known as ROLLBACK SEGEMENT and the table on which the DML operation is going to
be made is copied temporary to the rollback segment is known as snapshot. If
commit command is issued from a sql prompt a rollback segment along with the
snapshot gets deleted and the records transfer to main table will be permanent.
If rollback command is issued from after DML
operation a snapshot available inside the rollback segment again recopied to
the original location with the old data and rollback segment gets deleted.
NOTE:
Data one committed cannot be rollback and rollback
cannot be committed.
NOTE:
The basic difference between buffer memory and
rollback segment is
Buffer is client side memory it holds statement
for a Temporary period on other hand rollback segment is a sever side Temporary
memory, which holds a table in the form of snapshot for a Temporary period.
SQL>
INSERT;
SQL>commit;
(save)
SQL>INSERT;
SQL>rollback;
(discard)Auto commit & Auto rollback:
In some case data are automatically committed and
automatically rollback by the system defines which is technically known as auto
commit and auto rollback.
Auto commit:
1.
Whenever a user exit
out of SQL*PLUS normally after performing DML operation.
2.
Whenever DCL or DDL
command issued after any DML operation.
Auto rollback:
1.
Whenever a user exit
out of SQL*PLUS abnormally after performing DML operation.
2.
Dirty system crash.
Working with set auto commit on:
Save auto commit is a SQL*PLUS command once it is
on, it will save the transaction immediately after DML operation is made. The
default behaviour of auto commit is on.
SQL>
set auto commit on;
Save point is logical pointer given to individual
transaction or group of transaction made by user. Advantage of save point is
responsible to keep track on individual transaction save point for rollback propose rollback to a particular point is excluding of that point
rollback a particular point doesn’t mean above transaction as commit.
SQLl>insert;
SQL>save
point A;
SQL>
INSERT;
SQL>save
point B;
SQL>INSERT;
SQL>save
point C;
If
you want second command rollback then
SQL>rollback
to B;
NOTE:
Disadvantage of this command is takes random
rollback.
