Re: Oracle transactions and DDL statements.

peter.koch.larsen_at_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_at_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