Re: Oracle transactions and DDL statements.

From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 10 May 2006 16:10:17 -0700
Message-ID: <1147302617.571537@bubbleator.drizzle.com>

peter.koch.larsen_at_gmail.com wrote:

>>  > So far as I guess,
>>> our solution will not differ from e.g. large accounting system such as
>>> SAP where customisation is a major part of the product.
>> Performing manual customization in a product like Oracle EBS,
>> PeopleSoft, JD Edwards, Siebel, etc. is done in my experience prior
>> to the product going into production.

>
> What happens with our system is that there is a testbed isolated from
> the production system. The system is tested here before being installed
> in the production system - well, what do you expect? We're not
> lunatics.

I'm not the only one that was considering otherwise. But given this then there there are no transactions taking place when you perform DDL which takes me back to what I recall was your original concern ... commits in DDL. With the above in mind ... what's the issue?

> Of course not. The "upgrade" takes place perhaps three times a year. I
> have not had any mention of the frequency. Still, it is most annoying
> should the upgrade fail. Particularly on the testbed, where these
> changes are made perhaps several times a day. Also, when playing with
> the system and making an error that causes the system to not upgrade
> (that is the database or other parts of the system aborts), you have to
> stop the system and manually remove the tables that should not have
> been installed. This is a pain in you-know-what for the developers (but
> of course not a show-stopper).

Go to $ORACLE_HOME/rdbms/admin and look at Oracle's upgrade scripts. The simple solution is to do what Oracle does.

>> You can not implement capabilities such as Fine Grained Auditing and
>> Fine Grained Access Control on tables you don't know exist. Neither can
>> you implement many other forms of auditing and security.

>
> Reading this post makes it evident that you (and perhaps others) simply
> haven't understood our product. To repeat, we do not in a production
> system create new tables on a daily basis, but rather perhaps three
> times a year (and an upgrade would not necesarrily require new
> DDL-statements in our transaction).

You are correct. I think everyone misunderstood what you intended. That said the solution to the upgrades is to duplicate what Oracle does when upgrading say from 9i to 10g.

Take a look at a0902000.sql and c0902000.sql.

> I've reread the entire thread in search for a post that could be
> understod as if we would ever do so on a "daily" basis. Of course not -
> and how could you ever assume so?

Many of us have so go figure. If it was just me I'd apologize for having had a wee bit too much scotch. But I clearly am not alone.

> The inability by Oracle to abort DDL-transaction is still a major
> nuisance for the users of our testbed.

Have you looked at the CREATE SCHEMA reference I gave you? It appears not so here it is again.

www.psoug.org
Click on Morgan's Library
Click on SCHEMA

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

Received on Wed May 10 2006 - 18:10:17 CDT