DA Morgan skrev:

> peter.koch.larsen@gmail.com wrote:
> > DA Morgan skrev:
> >
[snip]
> >>>> --
> >>>> Sybrand Bakker, Senior Oracle DBA
> >> Actually Peter my instinct here is to agree with Sybrand. Everything
> >> you've written flies in the face of good practice in an Oracle database.
> >
> > If I were to develop a new application, naturally I would take the
> > idiosyncracies of the target platform into consideration.. Instead I am
> > porting an existing one that runs on a wide range of platforms -
> > including several databases of which I've already mentioned a few.
> > This should be evident had you read the first post on this thread.
> >
> > /Peter
>
> It is evident. It should be evident to you from my response and those of
> others that have be involved in this thread that we are all consider
> your products design, at least as you have presented it, as far from
> being best practice.

Right. Certainly you have been reading between the lines, making dead
wrong assumptions about our software.

> And, further, that Oracle will not change its
> concepts and architecture to make what you are trying to do work as you
> seemingly wish.

I never had that in mind. I just hoped that Oracle had grown up and
enabled some "a transaction really is a transaction" somewhere in its
product, but it obviously has not.

>
> If you want to work with Oracle. And you want to engage in best
> practices rather than using duct tape you will need to re-examine the
> underlying design decisions.

I see you are an employee of the University of Washington. I've had a
brief look at your Universitys homepage and see that there is a
Database group there (interesting research, some of it that i will look
into later).
I will recommend that you have a talk with one of the people from that
group (I assume you are not part of it) and ask one from the staff
there for a motivation to have a transaction that contains
DDL-statements not to be a transaction (at least it violates at least
one of the ACID rules). You will find there is none - except perhaps
for a pragmatic "it is to difficult to implement" or "it is
historically so" argument. (Those arguments will come from Oracle, of
course).

Kind regards
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

Anurag Varma skrev:

> This might not help you ... but 10gR2 has a concept of flashback
> database
> and creating restore points. Before you start your "transaction", you
> can create
> a restore point which if time comes you can restore the whole database
> back
> to using this feature.
> 10gR1 also has the concept of flashback database.
>
Hi Anurag.

Thankyou for your help. This is a solution we might look into.
Unfortunately it will be unusable in a production environment - as a
restore of the database likely will lose some transactions. For testing
this is better than nothing.

/Peter
> That is the closest you can get to regarding undoing the upgrade.
> Works on a database level though ..
>
> see (oracle docs at tahiti.oracle.com ...):
> http://download-east.oracle.com/docs...7.htm#sthref74
>
> Anurag

G Quesnel skrev:

> Just a few simple thoughts...
> Can we step back, I'd like to know why doing a commit on creating a
> table becomes an issue.
> We often use 'execute immediate' to release different version of our
> software, providing the flexibility to conditionally create/drop/alter
> database object definitions.
> Our software upgrade process also includes sections to
> load/update/delete data in configuration tables.

We essentially share problems here.
>
> We have a requirement that the whole database be backed up before the
> upgrade process is initiated, so the database can always be brought
> back to the image prior to starting the upgrade.

Right. You could that. But then you would not run for the time it takes
to upgrade. This could be a major problem or it could be a small
nuisance.

> On a few rare occasion we have also choosen to create a backup of a
> specific table (which could be done with something like ...
> Select count(*) in my_variab from user_objects
> where object_name = 'SPEC_TAB';
> if my_variab > 0 then
> execute immediate 'Create table SPEC_TAB_BAK as select * from
> SPEC_TAB';
> execute immediate 'Drop table SPEC_TAB cascade constraints';
> execute immediate 'Create table SPEC_TAB ( ...
> end if;
>

I appreciate that. I believe our customer must simply go off-line when
upgrading. This is probably just a small prize to pay - consider what
he already spent on Oracle! ;-)
Sincerely, in practice and for most applications it is not a problem.
It is not a show-stopper in our case.

> If later on a condition occurs that requires that you roll back a
> number of the changes,
> it can all be done programatically (execute immediate 'Drop table ...')
>
> The short answer to 'can you rollback to a point partially through the
> installation' - no - not automatically.
>
> This does not cause us a major issue, since we typically do the
> complete installation, or in a disaster case
> roll back everything, using a backup.
>
The problem for us is with the non-production system. You are allowed
to play here, and in our case we do what corresponds to a "compile"
whenever we are ready to test new ideas. This normally finishes in the
order of a minute or so (surely less than fifteen minutes), so there
could be a tendency to just try it. When you fail and then manually
have to delete the tables and procedures and so on that were created
you do get somewhat annoyed.

> HTH.

Kind regards
Peter

peter.koch.larsen@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@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

peter.koch.larsen@gmail.com wrote:
> DA Morgan skrev:
>
>> peter.koch.larsen@gmail.com wrote:
>>> DA Morgan skrev:
>>>
> [snip]
>>>>>> --
>>>>>> Sybrand Bakker, Senior Oracle DBA
>>>> Actually Peter my instinct here is to agree with Sybrand. Everything
>>>> you've written flies in the face of good practice in an Oracle database.
>>> If I were to develop a new application, naturally I would take the
>>> idiosyncracies of the target platform into consideration.. Instead I am
>>> porting an existing one that runs on a wide range of platforms -
>>> including several databases of which I've already mentioned a few.
>>> This should be evident had you read the first post on this thread.
>>>
>>> /Peter
>> It is evident. It should be evident to you from my response and those of
>> others that have be involved in this thread that we are all consider
>> your products design, at least as you have presented it, as far from
>> being best practice.
>
> Right. Certainly you have been reading between the lines, making dead
> wrong assumptions about our software.
>
>> And, further, that Oracle will not change its
>> concepts and architecture to make what you are trying to do work as you
>> seemingly wish.
>
> I never had that in mind. I just hoped that Oracle had grown up and
> enabled some "a transaction really is a transaction" somewhere in its
> product, but it obviously has not.
>
>> If you want to work with Oracle. And you want to engage in best
>> practices rather than using duct tape you will need to re-examine the
>> underlying design decisions.
>
> I see you are an employee of the University of Washington. I've had a
> brief look at your Universitys homepage and see that there is a
> Database group there (interesting research, some of it that i will look
> into later).
> I will recommend that you have a talk with one of the people from that
> group (I assume you are not part of it) and ask one from the staff
> there for a motivation to have a transaction that contains
> DDL-statements not to be a transaction (at least it violates at least
> one of the ACID rules). You will find there is none - except perhaps
> for a pragmatic "it is to difficult to implement" or "it is
> historically so" argument. (Those arguments will come from Oracle, of
> course).
>
> Kind regards
> 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

I run my own program here. Contact me off-line if you wish.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

peter.koch.larsen@gmail.com wrote:
> DA Morgan skrev:
>
[]
> I will recommend that you have a talk with one of the people from that
> group (I assume you are not part of it) and ask one from the staff
> there for a motivation to have a transaction that contains
> DDL-statements not to be a transaction (at least it violates at least
> one of the ACID rules). You will find there is none - except perhaps
> for a pragmatic "it is to difficult to implement" or "it is
> historically so" argument. (Those arguments will come from Oracle, of
> course).

It may be a pragmatic decision, but ORACLE is not the only DBMS to do
it this way, contrary to MS SQL and others that do not follow this
decision.

Even if DDL could be mixed with DML, I would not create applications
that way. The potential for errors seems too great.

IIRC, yours was a distributes update. I know from earlier work I've
done that one model to use is called a two phase commit. (ORACLE uses
this internally for some distributed transactions.) All of your DB
applications would have to follow this protocol, but the idea is: stage
the changes at each node, when all nodes confirm that they have met all
conditions to successfully complete the transaction, only then does a
final commit go out to all nodes. I've obviously left a lot of details
out (I last looked in detail at this about 10 years ago.) But it should
give you a more robust, flexible approach.

HTH,
ed