<peter.koch.larsen@gmail.com> wrote in message
news:1147208979.810778.181060@j73g2000cwa.googlegr oups.com...
>
> 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
>

You shouldn't be creating objects in the middle of a transaction. Start or
end the transaction before creating objects.
Jim

Jim Kennedy skrev:

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

Sybrand Bakker skrev:

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

Excuse me, but this is simply false. If you simply must come with
personal attacks instead of giving evidence to support your cause you
really should reserve that "Mickey Mouse" adjective to describe your
own mindset.

/Peter


>
> --
> Sybrand Bakker, Senior Oracle DBA

<peter.koch.larsen@gmail.com> wrote in message
news:1147252750.112378.67450@i40g2000cwc.googlegro ups.com...
>
> 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.
>
Pardon my ignorance, but is it really possible to rollback a
CREATE TABLE in MS SQL Server??? I am not sure
how DDL affects your current transaction in MSSQL,
maybe it runs DDL autonomously, but afaik you can't
rollback DDL in MSSQL, too.

--
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com

"Vladimir M. Zakharychev" <bob--nospam--@dynamicpsp.com> wrote in message
news:e3shfa$23hn$1@hypnos.nordnet.ru...
>
> Pardon my ignorance, but is it really possible to rollback a
> CREATE TABLE in MS SQL Server??? I am not sure
> how DDL affects your current transaction in MSSQL,
> maybe it runs DDL autonomously, but afaik you can't
> rollback DDL in MSSQL, too.
>
Actually it looks like I'm wrong and it's doable within
BEGIN TRAN..END TRAN block. Amazing. I wonder
how they manage to rollback an ALTER TABLE of
a big existing table....

--
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com

Vladimir M. Zakharychev wrote:
> "Vladimir M. Zakharychev" <bob--nospam--@dynamicpsp.com> wrote in message
> news:e3shfa$23hn$1@hypnos.nordnet.ru...
> >
> > Pardon my ignorance, but is it really possible to rollback a
> > CREATE TABLE in MS SQL Server??? I am not sure
> > how DDL affects your current transaction in MSSQL,
> > maybe it runs DDL autonomously, but afaik you can't
> > rollback DDL in MSSQL, too.
> >
> Actually it looks like I'm wrong and it's doable within
> BEGIN TRAN..END TRAN block.Hi Vladimir

Thank you for testing this for me. It is as I expected, but saves me
from verifying.

> Amazing. I wonder
> how they manage to rollback an ALTER TABLE of
> a big existing table....

I do not really see the problem. They must be able to do a roll back if
a failure happens during the statement, so what particular problem do
you expect the "full monty" to bring?

While you could perhaps improve performance if you know that ALTER
TABLE will be the only statement that should be processed, you could do
that optimisation by deferring the execution of the statement until you
see the COMMIT. If the subsequent statement is not a COMMIT (well - you
could optimise this part), you simply perform the ALTER TABLE the slow
and more easily roll-backable way.

Kind regards
Peter
>
> --
> Vladimir M. Zakharychev
> N-Networks, makers of Dynamic PSP(tm)
> http://www.dynamicpsp.com

On 10.05.2006 13:30 Vladimir M. Zakharychev wrote:
> Actually it looks like I'm wrong and it's doable within
> BEGIN TRAN..END TRAN block. Amazing. I wonder
> how they manage to rollback an ALTER TABLE of
> a big existing table....

Well actually there *is* an Oracle database that requires a COMMIT for a
DDL: rdb

At least it was implemented that way when it still was called rdb/VMS
(or was it VAX/rdb?) and was still owned by Digital Equipment.

Thomas


--
It's not a RootKit - it's a Sony

<peter.koch.larsen@gmail.com> wrote in message
news:1147252750.112378.67450@i40g2000cwc.googlegro ups.com...
>
> Jim Kennedy skrev:
>
> > <peter.koch.larsen@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 ..."

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)

Jim

Jim Kennedy wrote:
> <peter.koch.larsen@gmail.com> wrote in message
> news:1147252750.112378.67450@i40g2000cwc.googlegro ups.com...
> >
> > Jim Kennedy skrev:
> >
> > > <peter.koch.larsen@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

Jim Kennedy wrote:
> <peter.koch.larsen@gmail.com> wrote in message
> news:1147252750.112378.67450@i40g2000cwc.googlegro ups.com...
> >
> > Jim Kennedy skrev:
> >
> > > <peter.koch.larsen@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