peng@cs.umn.edu (Lim Ee Peng) (01/11/91)
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. I attached a script below to demonstrate my point. Can someone who knows Oracle well explain to me why does the rollback work this way? Thanks. peng (I can be reached by email : peng@umn-cs.cs.umn.edu) --------------------------------------------------------------- SQL*Plus: Version 2.1.11 - Production on Thu Jan 10 09:55:25 1991 Copyright (c) 1986, Oracle Corporation, California, USA. All rights reserved. Enter user-name: system Enter password: Connected to: ORACLE V5.1.22.3 - Production SQL> create table temp(attrib char(10)); Table created. SQL> rollback; rollback complete SQL> select * from temp; no records selected SQL> quit Disconnected from ORACLE V5.1.22.3 - Production
gunnar@imf.unit.no (Gunnar Taraldsen) (01/11/91)
Is it possible to get Clipper and/or dBase source file libraries via the nett? I read in article 5284 (2272mdb@abcom.ATT.COM, 3 Jan 91) in comp.databases that Rettig's Clipper library is now public domain. The problem is that I can't reach bulletin boards like Compuserve or Genie. Can anyone help me with an FTP adress? I'm a student so I can't afford to one of the many libraries that do exist like ARTFUL.LIB ("Dynamics of Clipper", ISBN 1-55623-131-8), NETLIB (Neil Weicher), <noname.lib> ("Programming in Clipper", ISBN 0-201-14583-9) etc.. There should also be some general interest conserning these questions. Please reply to comp.databases or gunnar@imf.unit.no. Help would be much velcomed. Gunnar Taraldsen NTH, Norway
cdm@gem-hy.Berkeley.EDU (Dale Cook) (01/11/91)
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. |> |> I attached a script below to demonstrate my point. |> Can someone who knows Oracle well explain to me why does the rollback |> work this way? |> |> [Example deleted...] It's not the ROLLBACK that's not working the way you think it should, it's the CREATE command. If you look up COMMIT in the SQL*PLUS command ref., it gives the list of commands that issue a COMMIT as part of their processing. CREATE is one of them. So are most other commands that alter the actual database structure (not just modify the data). Once a COMMIT is issued, of course, ROLLBACK has no effect on everything up to the COMMIT. Why? I suspect it has to do with internal issues within Oracle, probably to simplify an extremely complex circumstance. |> Thanks. |> Welcome. ---Dale Cook cdm@inel.gov ========== long legal disclaimer follows, press n to skip =========== ^L Neither the United States Government or the Idaho National Engineering Laboratory or any of their employees, makes any warranty, whatsoever, implied, or assumes any legal liability or responsibility regarding any information, disclosed, or represents that its use would not infringe privately owned rights. No specific reference constitutes or implies endorsement, recommendation, or favoring by the United States Government or the Idaho National Engineering Laboratory. The views and opinions expressed herein do not necessarily reflect those of the United States Government or the Idaho National Engineering Laboratory, and shall not be used for advertising or product endorsement purposes.
mdeltoro@druco.ATT.COM (DeltoroMA) (01/11/91)
in article <1991Jan10.160300.8839@cs.umn.edu>, peng@cs.umn.edu (Lim Ee Peng) says: > > > 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. > > I attached a script below to demonstrate my point. > Can someone who knows Oracle well explain to me why does the rollback > work this way? ORACLE's rollback command does not work with DDL statements (e.g. create or alter table, etc.). This command only works with DML statements (e.g. update, delete, etc.). -- ********************************************************************** Michael 'DT' Del Toro 303-538-1764 AT+T Bell Labs, 120th + Huron, Denver, CO mdeltoro@druco.ATT.COM
dafuller@sequent.UUCP (David Fuller) (01/14/91)
In article <1991Jan10.234950.19957@inel.gov> cdm@gem-hy.Berkeley.EDU (Dale Cook) writes: >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. The role of begin tx/commit is unspecified in the ANSI specifications and so you can't rely on the effects of transactions to the schema. This is too bad. I'd always like to be able to roll back schema changes; for example, Tandem's NonStop SQL handles pretty much any change to the distributed schema as long as you observe the limits of the transaction monitoring facility's ability to span long-lived transactions. Dave -- Dave Fuller Sequent Computer Systems Think of this as the hyper-signature. (708) 318-0050 (humans) It means all things to all people. dafuller@sequent.com
hthoene@doitcr.doit.sub.org (Hermann Thoene) (01/18/91)
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