Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

   
 

ss

Oracle Tips by Burleson 

Transactions

A transaction is a logically grouped set of INSERTs, UPDATEs and DELETEs that should all succeed or fail as a group.  We just found a new author that has already written a book.  We want to enter the data into our PUBS database.  Because we normalized our schema, we have to enter the data into three different tables; author, book_author and book.  We want all three inserts to either succeed or fail as a group; otherwise, we will have some data in some tables but not a complete record of the new author and his book.  We log onto the database and insert the three rows of data.

SQL> INSERT INTO AUTHOR
  2  VALUES ('A11l', 'john',
  3          'garmany', '123-345-4567',
  4          '1234 here st', 'denver',
  5          'CO','90204', '9999'); 

1 row created.

SQL> INSERT INTO BOOK_AUTHOR VALUES ('A111', 'B130', .20);

1 row created.

SQL> INSERT INTO BOOK
  2  VALUES ('B130', 'P002', 'easy oracle sql',
  3          'miscellaneous', 9.95, 1000, 15, 0, '',
  4          to_date ('02-20-2005','MM-DD-YYYY'));

1 row created.

SQL> commit;

Commit complete.

All three of my SQL statements succeeded, so I committed the changes.  Once I commit the changes, they are permanently changed in the database and the change cannot be undone.  Let’s say that one of the three inserts failed.  Since I don’t want half the information in the database, I can issue a ROLLBACK command and the changes since the last commit (or since log on) are removed from the database and the original state is recreated. 

A database transaction is all the changes that take place between a commit or rollback; beginning with the first SQL statement and ending with the subsequent commit or rollback.  A commit can be issued as a command or happen because of another statement.  Anytime we execute a data definition language  (DDL) command, we issue an implicit commit.  In the example below, I INSERT a duplicate row with the author key A111.  I then create a table (DDL) and rollback.

SQL> insert into book_author values ('A111', 'B130', .20); 

1 row created.

SQL> create table t1 as
  2  select
  3    store_name,
  4    avg(quantity) qty
from
  5    6    store join sales using (store_key)
  7  group by store_name; 

Table created.

SQL> rollback;

Rollback complete.

SQL> select author_key from book_author;
AUTHOR_KEY
-----------
A111
A111
A101
A102
A103
A104

A101
A109

27 rows selected.

Notice that the duplicate row remains after the rollback.  That is because I created a table that issued an implicit commit.  The duplicate row was committed by the CREATE TABLE command.   

Before a commit or rollback is executed, I have the ability to recover any data changes in the transaction.  If I select data that I have changed but not committed, I will get back the changed data.  If another user selects data that I have changed but not committed, they will see the original data (a consistent view  will be discussed next).     

A commit is issued when the user commits, when any DDL statement is executed, or when the user logs off normally.  If you make a number of changes and you do not want the changes to be permanent, you must issue a rollback before you log off.  Logging off commits the changes.


The above book excerpt is from:

Easy Oracle SQL

Get Started Fast writing SQL Reports with SQL*Plus

ISBN 0-9727513-7-8

John Garmany 

http://www.rampant-books.com/book_2005_1_easy_sql.htm

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster



Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks