Re: Oracle transactions and DDL statements.

From: <peter.koch.larsen_at_gmail.com>
Date: 10 May 2006 07:30:25 -0700
Message-ID: <1147271425.366434.56620@i40g2000cwc.googlegroups.com>

Jim Kennedy wrote:
> <peter.koch.larsen_at_gmail.com> wrote in message
> news:1147252750.112378.67450_at_i40g2000cwc.googlegroups.com...
> >
> > Jim Kennedy skrev:
> >
> > > <peter.koch.larsen_at_gmail.com> wrote in message
> > > > 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.
> > > > >
> >
> > [snip]
> >
> > > You shouldn't be creating objects in the middle of a transaction. Start
> or
> > > end the transaction before creating objects.
> > > Jim
> >
> > But what if my transaction requires the creation or destruction of ten
> > tables? This is what I wrote above, this is what the software does and
> > this what caused it to fail: you can't abort it after having created
> > the first five tables as those table alerady have been created.
> >
> > /Peter
> >
> That's my point it is a very bad practice. The "requirement" is not a
> requirement it is a solution. Business cases usually don't say create
> temporary tables ... They usually say "The ATM withdraw transaction needs
> to check the balance of the account and if the balance is >= the withdraw
> amount then ..."

My application is NOT ATM-like and the tables are not temporary. Once created, they'll likely live "forever". The problem is that we do not at delivery know the tables to use in our application, and we do not know all the sourcecode. The code and its corresponding tables are created as the system evolves.

>

> Creating objects on the fly is a very unscalable practice and it is rare
> that one ever needs to do so. (other RDBMS's sometimes use them as a
> strategy due to their locking models eg MS SQLServer, Oracle doesn't need
> them for that purpose) Oracle does have global temporary tables. You
> create the global temporary tables in advance and indicate if you want the
> rows in the table to live in a transaction or in a session. (see docs for
> syntax)

I am perfectly aware of this practice, but it is not applicable in my case.

>

> I would look at using global temporary tables. Your solution will scale
> much better, and you won't have this transaction problem. It would actually
> simplify your code a lot. (just get rid of the object creation and object
> drops)
>
> Jim
Received on Wed May 10 2006 - 09:30:25 CDT