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

I don't think that your series of statements constitutes a proper
transaction, as DDL has always been a transaction on it's own, and
probably always will be. Otherwise Oracle is going to compromise
database integrity.

Why do you need DDL in your 'transaction'? in Oracle you don't need
temporary tables.

--
Sybrand Bakker
Senior Oracle DBA

peter.koch.larsen@gmail.com wrote:
> 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
>

You cannot stop a commit from being performed with a DDL statement.
That's just the way Oracle works.

The first thing I would suggest is that you post an example of what you
are trying to do. Typically, one does not need to perform DDL in their
transaction, provided they have engineered the thing correctly. DDL are
expensive operations that can lead to locking issues which can severely
hamper applications with a high degree of concurrency. So DDL in the
transaction should be avoided if at all possible.

If you absolutely must have DDL in your transaction, then consider
putting the DDL in a stored procedure and make the stored proc run as an
autonomous transaction.

HTH,
Brian




--
================================================== =================

Brian Peasland
oracle_dba@nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

Peter, Brian and Sybrand are correct. The board needs to know what you
wish to accomplish and how your current process works. Also please
define where the current process runs (SQL Server, mySQL, etc...) and
what version of Oracle you are porting it to.

HTH -- Mark D Powell --

peter.koch.larsen@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

Peter wrote:

>been run on Oracle before - apparantly without discovering that Oracle
>commits no-matter-what after a DDL statement.

And before, too!

You might not notice the problem if no other users happen to be looking
at the half-committed data, or unless you try to rollback.

The autonomous transaction Brian mentioned is one way around this...
but still has pros and cons:
http://asktom.oracle.com/~tkyte/autonomous/index.html

jg
--
@home.com is bogus.
Since she's sorority she still seriously studies stats$sysstat so some
silly short-sighted scientist sometimes softly sighs "shared sort
segments."

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

DA Morgan skrev:

> peter.koch.larsen@gmail.com wrote:
[sn]
>
> >
> > Kind regards
> > Peter
> ip
> 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?
I do not have uncommitted data per se, but when I change the datamodel,
this change involves several tables and triggers and might span more
than one database (only one of them being from Oracle), There are also
non-database subsystems that participate in the distributed
transaction. The problem occurs if e.g. the non-database transaction
must abort the transaction for one reason or the other.
>
> 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.
Right. But thus time it is not a feature, but a limitation that is not
as i see it is not in any way based on some requirement for logical
consistency - more like the opposite if anything.

/Peter
> --
> Daniel A. Morgan
> University of Washington
> damorgan@x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org

On 9 May 2006 14:09:39 -0700, peter.koch.larsen@gmail.com wrote:

>Right. But thus time it is not a feature, but a limitation that is not
>as i see it is not in any way based on some requirement for logical
>consistency - more like the opposite if anything.

It rather seems you have a basic lack of understanding of relational
and transactional concepts. You probably would better stick to your
Mickey Mouse products.

--
Sybrand Bakker, Senior Oracle DBA

This might not help you ... but 10gR2 has a concept of flashback
database
and creating restore points. Before you start your "transaction", you
can create
a restore point which if time comes you can restore the whole database
back
to using this feature.
10gR1 also has the concept of flashback database.

That is the closest you can get to regarding undoing the upgrade.
Works on a database level though ..

see (oracle docs at tahiti.oracle.com ...):
http://download-east.oracle.com/docs...7.htm#sthref74

Anurag