• Add Website and Increase Page Rank

Transaction Control Statements in Oracle

Transaction control statements are ussued to either save the modified data or to undo the changes if they were made in error. Until the data has been permanently saved to the table, no other users will be able to view any of the changes you have made. A transaction is a term used to describe a group of DML statements representing data actions that logically should be performed together.

COMMIT and ROLLBACK Command

A COMMIT command , either implicitly or explicitly issued, permanently saves the DML statements previously issued. An explicit COMMIT occurs when the command is explicitly issued by entering a COMMIT; statement. The COMMIT command can implicitly occur at two instances.

• The user exits SQL PLus.
• When a DDL command such as CREATE or ALTER TABLE is issued.

If the user adds several record to a table and then creates a new table, the records that were added before the DDL command was issued are automatically committed. In Oracle 10g, a transaction consists of a series of statements that have been issued and not committed. The duration of the transaction is defined by when a commit implicitly or explicitly occurs.

ROLLBACK Command

A DML operation can be undone by issuing the ROLLBACK command. Command such as CREATE TABLE, TRUNCATE TABLE and ALTER TABLE can not be rolled back because they are DDL commands, and a COMMIT occurs automatically when they are executed.