Re: Oracle transactions and DDL statements.

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 09 May 2006 09:37:40 -0700
Message-ID: <1147192668.139726@bubbleator.drizzle.com>

peter.koch.larsen_at_gmail.com wrote:
> peter.koch.larsen_at_gmail.com 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_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Tue May 09 2006 - 11:37:40 CDT