Path: utzoo!utgpu!news-server.csri.toronto.edu!bonnie.concordia.ca!uunet!spool2.mu.edu!sdd.hp.com!usc!snorkelwacker.mit.edu!ira.uka.de!smurf!subnet.sub.net!altger!doitcr!hthoene
From: hthoene@doitcr.doit.sub.org (Hermann Thoene)
Newsgroups: comp.databases
Subject: Re: Rollback in ORACLE
Message-ID: <1575@doitcr.doit.sub.org>
Date: 17 Jan 91 23:05:39 GMT
References: <1991Jan10.160300.8839@cs.umn.edu>
Organization: DOITCR / FLOENZ1, Public Access Unix Munich/Germany
Lines: 32

In article <1991Jan10.160300.8839@cs.umn.edu> peng@cs.umn.edu (Lim Ee Peng) writes:
>
>I used Oracle Sqlplus recently and found out that the rollback command
>doesn't affect 'create table' operation at all.  All the while, I thought
>that 'create table' is simply an update to the system tables and rollback
>should also work.

All DDL-commands (data definition language) in Oracle do an implicit commit
and therefore rollback does not work. This is no bug but a feature and is
will documented in the Oracle documentation. These commands are for example

create table....
create index...
alter table...
drop table...
etc.

You must espatially take care if you use temporary tables for example in
'c'-programs (with PRO*C). If such a program makes modifications in some
tables, than creates a temporary table for storing some temporary results,
this create table-statement will commit also **all** changes done
previously in this transaction and rollback will no longer be possible !!!

So if you really must use temporary tables in programs, be sure to create
them **before** your transaction begins and drop them **after** successful
completion of the transaction or rollback.


Servus from Germany

Hermann Thoene,    Muenchen,    Bavaria <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
                                                  hthoene@doitcr.doit.sub.org