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.
 in SQL){kind=link}
0 Comments