A Transaction ensures that the action of a group of statements is atomic.A transaction is a logical unit of work that contains one or more SQL statements.The effects of all the SQL statements in a transaction can be either all committed or all rolled back.

SQL>  create table MyTable (name varchar2(100),address varchar2(500),id number);
Table created
SQL>

Let us start with a simple transaction example.Here the transaction inserts a row of record to MyTable.

-- Created on 11/4/2008 by Globinch PLSQLTIPS
declare
  -- Local variables here
begin
  -- Test statements here
  insert into MyTable(name,address,id) values('Abcd','address1',1);
  commit;
end;

The above piece of code represents a single transaction.The row inserted into MyTable became permanent after the COMMIT statement.
Let us query MyTable.

SQL> select * from Mytable;
NAME                       ADDRESS                  ID
Abcd                        address1                     1
SQL>

The following example show two simple transactions

-- Created on 11/4/2008 by Globinch PLSQLTIPS
declare
-- Local variables here
begin
  -- Test statements here
  insert into MyTable(name,address,id) values('Abcd2','address2',2);
  insert into MyTable(name,address,id) values('Abcd4','address2',3);
  commit;
  delete from MyTable where id = 2;
  commit;
end;

SQL> select * from MyTable;
NAME                ADDRESS             ID
Abcd4               address2             3
SQL>

A transaction ends when any of the following occurs:
1.A user issues a COMMIT or ROLLBACK statement without a SAVEPOINT clause.
2.A user runs a DDL statement such as CREATE, DROP, RENAME, or ALTER.
3.A user disconnects from Oracle and the current transaction is committed.
4.A user process terminates abnormally then the current transaction is rolled back.

So What is SAVEPOINT? read it here
Savepoints In SQL Transactions. – SAVEPOINT

Also read,

• Savepoints In SQL Transactions. – SAVEPOINT
• Oracle Tablespace, and Datafiles – Introduction
• Formatting date in SQL (Oracle) -Simple date forma…
• FINDING database objects, finding valid and INVALI…
• Oracle Data types

Technorati Tags:
SQL, SQL Transactions, transaction management, databse transactions, transactions, DML statements, DDL statements, INSERT, UPDATE, DELETE, create table