Re: Oracle transactions and DDL statements.

From: <peter.koch.larsen_at_gmail.com>
Date: 10 May 2006 07:52:19 -0700
Message-ID: <1147269908.307745.132660@u72g2000cwu.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 ..."

Well, my company is not writing software for ATM machines. Actually, it is not at all in that style.

>

> 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 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)

The table creation has nothing at all to do with temporary tables. The fact is that when we deliver our software we have no idea what tables there should be. If you look at my original post, you'll realise that we don't even know what the software looks like. (This is an exaggeration, but not totally ontrue ;-)). The "real" application is configured by the end-user (probably with our cooperation), and it is during this process that the tables are defined and created. They are not temporary and most likely will live "forever". Also, the application might (and almost surely will) be extended, requiring adding tables to the original system.

/Peter
>
> Jim
Received on Wed May 10 2006 - 09:52:19 CDT