Re: Oracle transactions and DDL statements.

peter.koch.larsen@xxxxxxxxx wrote:
peter.koch.larsen@xxxxxxxxx skrev:

Hi all


We've run into yet another problem regarding Oracle (10g -but I doubt
this matters), namely that the execution of a DDL statement causes a
transaction commit. Is there any way to make this not happen? It's a
real showstopper as we use Oracle in a distributed transaction and this
transaction contains several DDL statements.

Kind regards
Peter

Hi all

I've been asked why DDL-statements need to be part of a transaction -
I'll try to answer that here.
The application is a very flexible, distributed program with its own
language and compiler that allows you to "change the application" on
the fly. Part of that change involves creating the code that runs
together with the data, which might be distributed on more than one
database system, but even if there is only one DBMS, the code that is
distributed in the system takes part in the transaction (a commit
causing the old code to be discarded and the new code to be run) and
the compiler can abort the transaction as well,
The system has been in production for a very long time (more than ten
years), and has been ported to e.g. Microsoft SQL Server, Sybase,
Ingres and Teradata database system (and possible others I don't know -
I am relatively new in my job). Also, I believe that similar code has
been run on Oracle before - apparantly without discovering that Oracle
commits no-matter-what after a DDL statement.


Kind regards
Peter

TTBOMK it is impossible to perform DDL in Oracle with two commits
being issued. DDL is wrapped in the following structure before it
is executed.

BEGIN
COMMIT;
-- your DDL statement here;
COMMIT;
END;
/

What you didn't explain, as I read what you posted, is why the
commit is a problem. Why do you have uncommited transactional
data, when changing the data model?

You need to appreciate that Oracle's concepts and architecture
are quite different from that of the other products you mention
and if you want to work in Oracle you need to work the way
Oracle works.
--
Daniel A. Morgan
University of Washington
damorgan@xxxxxxxxxxxxxxxx
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
.

Relevant Pages

  • Re: Oracle transactions and DDL statements.
    ... We've run into yet another problem regarding Oracle (10g -but I doubt ... real showstopper as we use Oracle in a distributed transaction and this ... Ingres and Teradata database system (and possible others I don't know - ... commits no-matter-what after a DDL statement. ...
    (comp.databases.oracle.misc)
  • Oracle transactions and DDL statements.
    ... We've run into yet another problem regarding Oracle (10g -but I doubt ... namely that the execution of a DDL statement causes a ... transaction commit. ... real showstopper as we use Oracle in a distributed transaction and this ...
    (comp.databases.oracle.misc)
  • Re: Oracle transactions and DDL statements.
    ... We've run into yet another problem regarding Oracle (10g -but I doubt ... real showstopper as we use Oracle in a distributed transaction and this ... transaction contains several DDL statements. ... You cannot stop a commit from being performed with a DDL statement. ...
    (comp.databases.oracle.misc)
  • Re: Is Interbase still thriving?
    ... short of reengineering the whole SQL and database structure around ... But, considering, as I said, that DB2, Sybase ASE and MS SQL shares ... this approach to transaction consistency, do you really think it could ... Oracle DB. ...
    (borland.public.delphi.non-technical)
  • Re: serializable isolation level behavior question
    ... Should Oracle generate a error? ... Is the OP actually in a transaction? ... Is a serializable transaction protected against truncate? ... Oracle claims that "The SERIALIAZABLE setting specifies serializable ...
    (comp.databases.oracle.server)