Re: Oracle transactions and DDL statements.

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 Received on Wed May 10 2006 - 18:00:09 CDT