Re: Why Oracle does not allow rollback of DDL statements?
Date: Mon, 10 Nov 2008 07:24:58 -0500
Message-ID: <6nqngrFajpcU1@mid.individual.net>
Mladen Gogala wrote:
> On Mon, 10 Nov 2008 06:52:42 -0500, Serge Rielau wrote:
>
>> Other RDBMS which do have transactional DDL can very well monitor their >> system, even explain a query on DDL that isn't committed so long as the >> one explaining is the session that did the DDL, just like their own >> modifications to regular data. The schema catalog is just another set of >> tables after all.
>
> Hmmm, I am not sure that any Oracle DBA worth his salt would like to see
> that. As a DBA, I am frequently running explain plan on other sessions.
> What would be gained by transactional DDL? Why is it so significant?
>
OK, let's walk through a simple example. Assume a table with a column c1 and a view on that table. CREATE TABLE T(c1 NUMBER);
CREATE OR REPLACE VIEW V AS SELECT c1 AS X FROM T;
You application is up and running with various users referring to V. Now want to rename c1 to c2 (or change the type or whatever):
ALTER TABLE T RENAME COLUMN c1 TO C2;
The view is now invalidated (it depends on T.c1).
Immediately your other users will start receiving errors from the view.
It can't recompile. It's broken.
Of course you as the DBA knew that (and you did test your DDL change).
So:
CREATE OR REPLACE VIEW V AS SELECT C2 AS X FROM T;
All is fine now, your users will be happy.
With transactional DDL your users would never have seen the invalid view.
The ALTER TABLE and the CRATE OR REPLACE VIEW are a semantic unit. They
should be executed together.
Note that there very well may have been an impact to the users already
depending on the actions taken on the table. All that happens here is
that a bit more is packed onto the same atomic unit to preserve
consistency of the schema.
If I understand recent posts and comments Mark made to me at IOD correctly "editions" in 11gR2 are meant to solve this problem (and presumably others).
Hope that brings the discussion down to a more technical level again.
Cheers
Serge
-- Serge Rielau DB2 Solutions Development IBM Toronto LabReceived on Mon Nov 10 2008 - 06:24:58 CST