Why Oracle does not allow rollback of DDL statements?
zigz...@yahoo.com
DDL statements (e.g create table …) are executed. DDL statements
simply write information (insert, delete and update) to data
dictionary, so why DDL statement cannot be rolled back. Am I missing
some thing?
I was learning SQL Server few months ago and I noticed that SQL Server
does allow rollback of DDL statements if I am not mistaken. It lloks
like there is something in Oracle architecture which prevents rollback
of DDL satements. Rollback of all staements should be allowed not just
of DML statements.
Appercaite your insight.
hpuxrac
Why don't you get Tom Kyte's 10g book the first several chapters of
architecture related material could give you the insight you are
looking for probably.
gym dot scuba dot kennedy at gmail
<zigz...@yahoo.com> wrote in message
news:57ce7648-b6db-4e34...@v16g2000prc.googlegroups.com...
Appercaite your insight.
Wanting it different than it is isn't going to change the facts. In general
in Oracle you don't execute DDL statements on the fly. (eg create and drop
tables on the fly. There are global temporary tables for that and they are
created one time) In SQL Server there is often more of a need to create a
temporary table on the fly due to locking issues (that Oracle does not
have). So SQL Server HAS to work that way or their are up the proverbial
creek without a paddle. In Oracle you shouldn't be doing DML and the DDL
that you want ot roll back. Could Oracle change the way it does that? Sure
it is technically possible. Could Oracle allow dirty reads? Sure it is
technically possible, but it goes against their philosophy.
Jim
Serge Rielau
First column storage, now transactional DDL.
You are loosing your way zigzag.. what's next, stop tithing?
You know what's waiting on the other side!
*satanicalchuckle**
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Noons
> I have always been surprised why Oracle does a implicit commit when
> DDL statements (e.g create table …) are executed.
It's called database consistency.
Only real databases have it.
> DDL statements
> simply write information (insert, delete and update) to data
> dictionary, so why DDL statement cannot be rolled back.
Oh no they most certainly don't just do that!
> Am I missing
> some thing?
A lot, actually. But it'll come.
> I was learning SQL Server few months ago and I noticed that SQL Server
> does allow rollback of DDL statements if I am not mistaken.
It does not.
> It lloks
> like there is something in Oracle architecture which prevents rollback
> of DDL satements.
No there isn't. Read about the FRA.
>Rollback of all staements should be allowed not just
> of DML statements.
Actually, it should not. There is no requirement
anywhere for such in any of the available standards
for relational database architecture or design.
> Appercaite your insight.
yuo gto ti.
Thomas Kellerer
>> I have always been surprised why Oracle does a implicit commit when
>> DDL statements (e.g create table …) are executed.
>
> It's called database consistency.
> Only real databases have it.
But creating objects in a database needs to be consistent as well. I don't see why having transactional DDL contradicts database consistency.
Actually I think it adds to consistency.
>> DDL statements
>> simply write information (insert, delete and update) to data
>> dictionary, so why DDL statement cannot be rolled back.
>
> Oh no they most certainly don't just do that!
So what else are they doing?
>> I was learning SQL Server few months ago and I noticed that SQL Server
>> does allow rollback of DDL statements if I am not mistaken.
>
> It does not.
Yes it does (Note that I do not like SQL Server at all, but that is definitely a plus - or it would be if SQL Server wasn't that awful when it comes to locking...)
>> Rollback of all staements should be allowed not just
>> of DML statements.
>
> Actually, it should not. There is no requirement
> anywhere for such in any of the available standards
> for relational database architecture or design.
There might not be a requirement, but it *is* very handy.
Just think of an database upgrade script that fails in the middle. With transactional DDL I simply do a rollback fix the cause of the error and start the script again. With non-transactional DDL I need to remove all objects that were created until then to put the DB into the state before I ran the script.
Thomas
Mladen Gogala
> But creating objects in a database needs to be consistent as well. I
> don't see why having transactional DDL contradicts database consistency.
>
> Actually I think it adds to consistency.
So, I create table, Noons creates an index to that table, in his own
schema. If I was able to roll back the table creation, what happens to
Nuno's index? Flashback table to before drop is possible with all releases
that support flashback area, since the version 7.3.6.
--
Mladen Gogala
http://mgogala.freehostia.com
Shakespeare
Of course, Noons could only create the index AFTER you commit your DDL!
So no roll back would be possible anymore.
Shakespeare
Thomas Kellerer
It's just like he can't delete a row in a table that I have inserted but not committed.
Mladen Gogala
OK, that would make things even worse, because you would have "local"
objects, visible only from the creating session. There would be no way
to meaningfully monitor the system and explain SQL as the "noncommitted"
table would not exist from the perspective of any other user.
Mladen Gogala
> Noons can't create an index on a table that I have not committed
> (because he will not see it). It's just like he can't delete a row in a
> table that I have inserted but not committed.
That would imply allowing "local" schema objects, like "local tables"
or "local temporary tables". That would make it practically impossible to
meaningfully monitor the system and run explain plan on SQL statements.
Shakespeare
Indeed. I wasn't saying it would be any better.... or any good at all.
But isn't this the case with all uncomitted transactions?
What about two users creating the same table(s) with a delayed commit?
And what about DROP TABLE statements? Should they be roll backed as
well?????
Shakespeare
Thomas Kellerer
Other RDBMS can handle that. rdb/VMS was able to handle things like that 15 years ago.
Oracle should have a look at the source code as they now own that product :)
Thomas
Noons
>
> >> I was learning SQL Server few months ago and I noticed that SQL Server
>>> does allow rollback of DDL statements if I am not mistaken.
>>
>> It does not.
> Yes it does (Note that I do not like SQL Server at all, but that is
No, it does not. Don't try that rubbish with me, I've
been breathing SS2K5 for the last 14 months. It does not.
The front end you most likely use to access it does allow that.
But that is not SQL Server.
> There might not be a requirement, but it *is* very handy.
No it is not. Try having a process that relies on dropping a
table to succeed and another that relies on a create index on
that table. Which one is supposed to end first, when?
> Just think of an database upgrade script that fails in the middle. With
> transactional DDL I simply do a rollback fix the cause of the error and
> start the script again. With non-transactional DDL I need to remove all
> objects that were created until then to put the DB into the state before
> I ran the script.
So what? Test the script first. Databases
are not to be managed like digital photography
where if you don't like something you erase it.
Databases are to be handled by supposedly responsible
folks who presumably now what they are doing.
The last thing a db needs is the
"bang-head-on-wall-until-something-gives" approach.
Noons
>> That would imply allowing "local" schema objects, like "local tables"
>> or "local temporary tables". That would make it practically impossible to
>> meaningfully monitor the system and run explain plan on SQL statements.
Or even manage space in that system. Yeah, we know: "disk is
cheap". Try that with SAN platinum disks and see how high
it flies with the CFO...
>
> Other RDBMS can handle that. rdb/VMS was able to handle things like that
> 15 years ago. Oracle should have a look at the source code as they now
> own that product :)
Given they have owned the product for that long
and haven't even bothered duplicating that one should
tell us something?
Serge Rielau
> Mladen Gogala, 10.11.2008 11:56:
>> On Mon, 10 Nov 2008 10:38:03 +0100, Thomas Kellerer wrote:
>>
>>> Noons can't create an index on a table that I have not committed
>>> (because he will not see it). It's just like he can't delete a row in a
>>> table that I have inserted but not committed.
>>
>> That would imply allowing "local" schema objects, like "local tables"
>> or "local temporary tables". That would make it practically impossible to
>> meaningfully monitor the system and run explain plan on SQL statements.
not come for free.
It's obvious that the community is perfectly happy to wrap each EXECUTE
IMMEDIATE 'CREATE OR REPLACE ....' into an autonomous transaction and
handle logical undo sripts. So.. no incentive.
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.
The locking between the index and teh table is no different than any
other update lock.
Cheers
Cristian Cudizio
I think that on Oracle Database, now, it would be technically ease to
make some DDL transactional, as create
and drop table, but it seems to me not so useful. What tecnically
seems to me a problem seems to be
something such as adding and removing columns. Especially when
removing columns Oracle has to go into each
block, it seems to me complicated and dangerous make it transactional.
Regards,
Cristian
Shakespeare
Yes you are! Suppose you drop a table (or delete a column). All data,
indices or whatever will be lost and not regained by merely rolling back
the dictionary tables....
So there must be a little bit more to it than just those tables.
I understand your need for rolling back DDL though, regarding the number
of typing errors in 11 lines of text..... <g>
Shakespeare
Cristian Cudizio
> Noons, 10.11.2008 09:45:
>
> >> I have always been surprised why Oracle does a implicit commit when
> >> DDL statements (e.g create table …) are executed.
>
> > It's called database consistency.
> > Only real databases have it.
>
> But creating objects in a database needs to be consistent as well. I don't see why having transactional DDL contradicts database consistency.
>
> Actually I think it adds to consistency.
>
> >> DDL statements
> >> simply write information (insert, delete and update) to data
> >> dictionary, so why DDL statement cannot be rolled back.
>
> > Oh no they most certainly don't just do that!
>
> So what else are they doing?
>
They allocate space on tablespaces (obviusly speaking about locally
managed tablespaces)
Regards,
Cristian
Mladen Gogala
>>
> Other RDBMS can handle that. rdb/VMS was able to handle things like that
> 15 years ago. Oracle should have a look at the source code as they now
> own that product :)
>
> Thomas
So, you are suggesting that Oracle RDBMS should learn from the RDBMS that
has failed and vanished from the market? Why would they do that? To look
more like SQL Server, a DB that Oracle has wiped the floor with? Hardly
seems like a goal worth pursuing.
Serge Rielau
> Thomas Kellerer wrote,on my timestamp of 10/11/2008 8:02 PM:
>
>>
>> >> I was learning SQL Server few months ago and I noticed that SQL
>> Server
>>>> does allow rollback of DDL statements if I am not mistaken.
>>>
>>> It does not.
>> Yes it does (Note that I do not like SQL Server at all, but that is
>
> No, it does not. Don't try that rubbish with me, I've
> been breathing SS2K5 for the last 14 months. It does not.
> The front end you most likely use to access it does allow that.
> But that is not SQL Server.
the behavior you imply? I'm also told that SQL Server (like DB2) has
transactional DDL. Even TRUNCATE table is transactional.
>> There might not be a requirement, but it *is* very handy.
> No it is not. Try having a process that relies on dropping a
> table to succeed and another that relies on a create index on
> that table. Which one is supposed to end first, when?
Oracle has to deal with that today!
No matter how you call it the CREATE statement still needs to be ATMIC.
And atomic implies that there is a statement level savepoint. If any of
the work against the db schema fails for any reason teh DDL statement
needs to be rolled back. And that CREATE INDEX statement must not be
permitted until the CREATE TABLE statement successfully COMPLETEs.
What if someone else tries to create a table with the same name? If
someone else drops the original table conflicting with the table I'm
trying to create.
Obviously the table also cannot be used by anyone until that commit happens.
Call it a lock or call it a latch, doesn't matter.
Oracle has to sort out timing today. The only difference is that it has
a statement level view on things rather than a transaction level view.
Mladen Gogala
> 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?
Mladen Gogala
> Try that with SAN platinum disks and see how high it flies with the
> CFO...
Through the window? It depends on the height of the building where your
CFO is located.
Serge Rielau
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.
Thomas Kellerer
>> Other RDBMS can handle that. rdb/VMS was able to handle things like that
>> 15 years ago. Oracle should have a look at the source code as they now
>> own that product :)
>>
>> Thomas
>
> So, you are suggesting that Oracle RDBMS should learn from the RDBMS that
> has failed and vanished from the market?
Only because something is old, doesn't mean that it didn't have good features. I think rdb had data partitioning built in (e.g by index) long before Oracle was even thinking about it.
> Why would they do that? To look more like SQL Server?
That has nothing to do with making it look like SQL Server. I'm definitely not a fan of SQL Server (in fact I hate it) but I like transactional DDL and SQL Server isn't the only one to have this.
Postgres and DB2 have that as well.
Thomas
sybr...@hccnet.nl
wrote:
>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 think it is normal to change the DDL of a running production
system?
--
Sybrand Bakker
Senior Oracle DBA
sybr...@hccnet.nl
wrote:
>Oracle has to deal with that today!
Even without you advocating lousy development practices, developers
are already creating more than enough big messes.
Could you please explain why you advocate changing production systems
on the fly?
Do you have any ideas how information systems are designed?
Or are you just a 12 year old or promoting the development strategy of
your 12 year old nephew?
Why are you making such nonsensical assertions?
Serge Rielau
do more and more while queries are in flight. E.g. replace a view while
a warehousing query on it is executing.
Serge Rielau
Sybrand,
You seem to have very little expectations and trust in your current DBMS....
Of course it is nonsensical in an environment where DDL is auto commit.
It is very sensical in one where I play in DDL as a transaction..
It's not only about having active users, even queries are expected to be
in flight. And no we didn't cook this up. Real DB2 customers demand this
level of on-line schema evolution.
You simply cannot interrupt or drain a complex warehouse query just
because you need to patch a view!
Open your mind man!
DA Morgan
> Real DB2 customers demand this level of on-line schema evolution.
> You simply cannot interrupt or drain a complex warehouse query just
> because you need to patch a view!
>
> Open your mind man!
> Serge
This may well explain why there are so few people using DB2 for
data warehouses as opposed to Oracle. <g>
We have this concepts like unit testing, integrated unit testing,
etc. If you are patching a view in a live production system then,
by definition, your testing was inadequate.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damo...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
joel garry
> I have always been surprised why Oracle does a implicit commit when
> simply write information (insert, delete and update) to data
> some thing?
>
> like there is something in Oracle architecture which prevents rollback
> of DDL satements. Rollback of all staements should be allowed not just
> of DML statements.
>
> Appercaite your insight.
Actually, Oracle does 2 commits, one each before and after the DDL.
Part of why Oracle is different than other DBMS engines is its
architecture - this is well explained in the book hpuxrac suggested.
I don't know the real reason you can't rollback DDL, but I suspect
part of it is simply the evolution of the code. By now, it would be
difficult. And there are workarounds in the modern versions.
As far as Serge's assertion that customers want to be able to change
dicks in the middle of a screw, I mean, change defs in the middle of a
view, that's as silly as some other things customers "want," like
dirty reads. There may even be a legitimate requirement, but that's
no reason to make it easy at the expense of other, more reasonable and
ordinary things. I wonder how such things don't violate Codd's
Physical Data Independence rule - they logically impair applications.
Just because there is a Codd rule that states the same language must
be used to access the online catalog doesn't mean all possilbe
language features need to apply to the catalog.
If you are going to see how sausage is made, bring along a barf bag.
jg
--
@home.com is bogus.
Awright, what dumbass DBA is pissing people off...
http://www.signonsandiego.com/news/nation/20081110-1037-baptizingthedead.html
zigz...@yahoo.com
There are many legitimate reasons you want to have transaction ddl,
once you learn database you are using does not have the feature; you
have to live with work around.
For examples, I am developing installation script to create schema
changes
for new release of system. If there was transaction DDL, I can write
all the changes and in case of error, roll them back. In absence of
transactional DDL, I have to write the script: if a schema change was
already made do not make it, otherwise make the change.
Database is the only thing I know in Computer Science, which has
concept of transaction where by a group of statements can be rolled
back in case of error. When students learn databases, they immediately
think that everything in database can be rolled back. When they find
that their database cannot rollback DDL, they are really surprised.
Having worked in Oracle for many years, I thought no relation database
provides this feature. When I started learning SQL Server, I was
pleasantly surprised that SQL Server can do it.
zigz...@yahoo.com
>
> - Show quoted text -
I am using SQL SERVER 2000. I went in Query Analyzer. I did
begin transaction
create table t1 ..
rollback transaction
Table t1 was not created.
I then did
begon transaction
create table t1
commit transaction
and table t1 was created.
I, therefore, do not undestand why people in this forum are saying
SQL Server does not allow it but tools do.
SQL Sever engine is the one which is executing SQL Satements and it is
allowing rollback.
Which part of SQL SERVER does not allow transact SQL.
Bob Jones
> the behavior you imply? I'm also told that SQL Server (like DB2) has
> transactional DDL. Even TRUNCATE table is transactional.
>
Transactional TRUNCATE? Why not use DELETE?
> Oracle has to deal with that today!
> No matter how you call it the CREATE statement still needs to be ATMIC.
> And atomic implies that there is a statement level savepoint. If any of
> the work against the db schema fails for any reason teh DDL statement
> needs to be rolled back.
Why do you think the "CREATE statement" is not "ATMIC"? Have you ever seen a
partially created table? I haven't.
> And that CREATE INDEX statement must not be permitted until the CREATE
> TABLE statement successfully COMPLETEs.
> What if someone else tries to create a table with the same name? If
> someone else drops the original table conflicting with the table I'm
> trying to create.
Correct, you cannot create an index on a table that does not exist.
> Obviously the table also cannot be used by anyone until that commit
> happens.
> Call it a lock or call it a latch, doesn't matter.
Yes, you cannot use a table that is not yet created. Sorry I am not seeing
what the issue is here.
> Oracle has to sort out timing today. The only difference is that it has a
> statement level view on things rather than a transaction level view.
>
What? Now you really lost me.
Serge Rielau
>> That's the first I hear about that. Can you point to any docs explaining
>> the behavior you imply? I'm also told that SQL Server (like DB2) has
>> transactional DDL. Even TRUNCATE table is transactional.
>>
>
> Transactional TRUNCATE? Why not use DELETE?
>
>> Oracle has to deal with that today!
>> No matter how you call it the CREATE statement still needs to be ATMIC.
>> And atomic implies that there is a statement level savepoint. If any of
>> the work against the db schema fails for any reason teh DDL statement
>> needs to be rolled back.
>
> Why do you think the "CREATE statement" is not "ATMIC"? Have you ever seen a
> partially created table? I haven't.
Then the rest of the post will make sense I hope.
Michael Austin
> I have always been surprised why Oracle does a implicit commit when
> DDL statements (e.g create table …) are executed. DDL statements
> simply write information (insert, delete and update) to data
> dictionary, so why DDL statement cannot be rolled back. Am I missing
> some thing?
>
> I was learning SQL Server few months ago and I noticed that SQL Server
> does allow rollback of DDL statements if I am not mistaken. It lloks
> like there is something in Oracle architecture which prevents rollback
> of DDL satements. Rollback of all staements should be allowed not just
> of DML statements.
>
> Appercaite your insight.
One could only hope that Oracle would adopt such a strategy (no implicit
commits on DDL). Their other db engine Oracle/Rdb (OpenVMS platform
only) does require explicit commits on DDL. They also have the ability
with one command to rollback a complete upgrade with one command.
(example running 9i upgraded to 10g need to rollback to 9i).
Noons
> That's the first I hear about that. Can you point to any docs explaining
> the behavior you imply? I'm also told that SQL Server (like DB2) has
> transactional DDL. Even TRUNCATE table is transactional.
Instead of reading marketing materials, try using the product.
I'm stuck with having to use it, so rather than reading "tickmarks"
from
a marketing "competitive" list, I aqctually have to deal with the
blessed thing.
It is supposed to *be* there. However, it is so inconsistent as to
make it essentially unusable.
For example: Try to drop a table that is not there - a common example
of a
"oops" statement in an upgrade script. It produces an error, but does
NOT
cause a rollback to take place or lock other scripts out.
Now, try to select form an incorrect table name - another common
example
os a "oops" statement in an u[grade script. It produces an error, and
DOES
put the transaction in a suspended state that is neither rollback nor
commit:
if you are running the script interactively, you then have to manually
take a
decision on which way it goes.
Do a search around the forums to see what folks are using to run
upgrade
scripts - the example given to "reinforce how useful it is".
*NO* one recommends that transactional DDL be used. Exactly and
precisely
because it is not consistent in how it handles errors and what causes
an auto
or manual rollback of a transaction in error. Let's not get into
logfile overheads...
Which makes it, to me, essentially the equivalent of "not there":
couldn't
care less what the marketing nonsense says.
> Oracle has to sort out timing today. The only difference is that it has
> a statement level view on things rather than a transaction level view.
Yup. I can live with that.
Noons
> > Try that with SAN platinum disks and see how high it flies with the
> > CFO...
>
> Through the window? It depends on the height of the building where your
> CFO is located.
24th floor. The last one who tried to convince him "disks were
cheap",
got audited. I love the man! ;)
Cristian Cudizio
Yes, this demonstrates that you can rollback a creation of a table.
I'm just curious to
know the implementation and the impact on the system of such a
beautiful feature.
Does it support the correct isolation level? What appens when you add
or remove
a column from a table with a lot of data and then you rollback??
You do it on live system (and that, as said here by others is a
questionable thing)
because you cannot stop a live system for the upgrade but ... ooops
you've made
and error and you lock the system for a lot of time waiting a
rollback ...
MS SQL Server introduced the possibility to not lock readers for read
committed
isolation level with 2005 version, so i'm sceptic about the efficent
implementation
of transactional DDL.
Maybe my mind is to closed.
Regards,
Cristian
steph
Any other transaction could affect a lot of data. What's the point
here?
Mladen Gogala
> 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.
That is true. I usually have to do a little downtime for the applications
when applying DDL. That doesn't seem to be a problem. IT departments that
I worked in usually have downtime procedures and schedules. Each major DB
has scheduled "maintenance time", some are admittedly very odd, like
"every 2nd weekend in the month, between 02:00 AM and 04:00 AM on the
night between Saturday and Sunday" but nevertheless, such policies
usually do exist.
Editions are meant exactly for that but are largely undocumented.
In the reference manual, the column EDITIONING_VIEW in the DBA_VIEWS view
is described as "RFU". Dan Morgan has described the SQL interface here:
http://www.psoug.org/reference/editions.html
How exactly is that supposed to be used, I don't know. I am not at all
sure that transactional DDL would be a good thing. It would deprive me
of much of the insight in other users sessions that I now have.
Mladen Gogala
> You think it is normal to change the DDL of a running production system?
Like in a rolling upgrade?
Cristian Cudizio
Yes in fact i consider massive changes of data as part of deployment
procedure (but now
i'm doing and example of my experience with an OLTP system) that
needs
a scheduled downtime.
Regards,
Cristian
Serge Rielau
> On Nov 10, 11:06 pm, Serge Rielau <srie...@ca.ibm.com> wrote:
>
>> That's the first I hear about that. Can you point to any docs explaining
>> the behavior you imply? I'm also told that SQL Server (like DB2) has
>> transactional DDL. Even TRUNCATE table is transactional.
>
> Instead of reading marketing materials, try using the product.
(lawyers...). So I'm stuck with asking questions. :-)
> It is supposed to *be* there. However, it is so inconsistent as to
> make it essentially unusable.
> For example: Try to drop a table that is not there - a common example
> of a
> "oops" statement in an upgrade script. It produces an error, but does
> NOT
> cause a rollback to take place or lock other scripts out.
Why would is cause a rollback? If you are doing DML (let's move to safer
ground) and you are getting an error - say a check constraint violation
- do you expect teh whole transaction to roll back? Typically I'd expect
the statement to fail (statement atomicity) and then an exception
handler can decide whether the problem as serious enough to fail the
transaction or recover from it somehow.
That's what those WHENEVER exceptions are there fore.
> Now, try to select form an incorrect table name - another common
> example
> os a "oops" statement in an u[grade script. It produces an error, and
> DOES
> put the transaction in a suspended state that is neither rollback nor
> commit:
> if you are running the script interactively, you then have to manually
> take a
> decision on which way it goes.
Interesting. In my world again the statement would fail (and roll back)
but the transaction remains intact. An exception handler initiates
rollback if desired.
Shakespeare
Bob Jones
"Serge Rielau" <sri...@ca.ibm.com> wrote in message
news:6nscut...@mid.individual.net...
> Bob Jones wrote:
>>> That's the first I hear about that. Can you point to any docs explaining
>>> the behavior you imply? I'm also told that SQL Server (like DB2) has
>>> transactional DDL. Even TRUNCATE table is transactional.
>>>
>>
>> Transactional TRUNCATE? Why not use DELETE?
>>
>>> Oracle has to deal with that today!
>>> No matter how you call it the CREATE statement still needs to be ATMIC.
>>> And atomic implies that there is a statement level savepoint. If any of
>>> the work against the db schema fails for any reason teh DDL statement
>>> needs to be rolled back.
>>
>> Why do you think the "CREATE statement" is not "ATMIC"? Have you ever
>> seen a partially created table? I haven't.
> This is where we went apart. I stated that CREATE is indeed ATOMIC.
>
If it is already atomic, why does it "still need to be atomic"?
> Then the rest of the post will make sense I hope.
>
You mean about rolling back a DDL statement? Yes, but rather pointless.
Serge Rielau
> Editions are meant exactly for that but are largely undocumented.
> In the reference manual, the column EDITIONING_VIEW in the DBA_VIEWS view
> is described as "RFU". Dan Morgan has described the SQL interface here:
> http://www.psoug.org/reference/editions.html
folks dissing rolling upgrades now will claim it's the best thing since
sliced bread. Always depends where the message is coming from. :-)
> How exactly is that supposed to be used, I don't know. I am not at all
> sure that transactional DDL would be a good thing. It would deprive me
> of much of the insight in other users sessions that I now have.
There seems to be an assumption that transactional DDL means temporary DDL.
In real life you will see what your users are up to just fine because
they will commit their DDL change (assuming you let them do DDL to begin
with...).
Serge Rielau
There are mechanics and engineers in this profession.
Both are needed.
Serge
joel garry
You might want to google for ORA-00054, then note
http://www.oracle.com/technology/deploy/availability/htdocs/online_ops.htm
Once again, it boils down to, there's lots going on, you can't assume
everyone wants their DDL to be invalidated in the middle of what they
are doing, or that everyone is doing the same thing.
Not knowing whether it is going to work in SQL Server, is worse.
jg
--
@home.com is bogus.
Always mount a scratch monkey. http://www.signonsandiego.com/uniontrib/20081109/news_1n9russub.html
DA Morgan
Hear hear!
DA Morgan
> For examples, I am developing installation script to create schema
> changes
> for new release of system. If there was transaction DDL, I can write
> all the changes and in case of error, roll them back. In absence of
> transactional DDL, I have to write the script: if a schema change was
> already made do not make it, otherwise make the change.
Your premise is faulty and, it appears, you are trying to reinvent the
wheel. Use CREATE SCHEMA and if it all doesn't succeed it all is rolled
back.
http://www.psoug.org/reference/schema.html
Or perhaps you should look at the DBMS_REDEFINITION built-in package.
http://www.psoug.org/reference/dbms_redefinition.html
which has been around since 9.0.1.
Bob Jones
>I give up.
>
> There are mechanics and engineers in this profession.
> Both are needed.
> Serge
I am afraid what's lacking here is not mechanics and engineers but
understanding.
zigz...@yahoo.com
> zigzag...@yahoo.com wrote:
> > For examples, I am developing installation script to create schema
> > changes
> > for new release of system. If there was transaction DDL, I can write
> > all the changes and in case of error, roll them back. In absence of
> > transactional DDL, I have to write the script: if a schema change was
> > already made do not make it, otherwise make the change.
>
> Your premise is faulty and, it appears, you are trying to reinvent the
> wheel. Use CREATE SCHEMA and if it all doesn't succeed it all is rolled
>
> Or perhaps you should look at the DBMS_REDEFINITION built-in package.http://www.psoug.org/reference/dbms_redefinition.html
> which has been around since 9.0.1.
> --
> Daniel A. Morgan
> Oracle Ace Director & Instructor
> University of Washington
> Puget Sound Oracle Users Groupwww.psoug.org
Create schema works only for create table, create views and grants. It
won't let you do alter statements, create triggers…
Dbms_redef appears only for redefining tables. Its purpose is on-line
redefinition of tables. It has too much overhead: requires lots of
additional storage depending on size of your tables.
Both methods do not provide all changes, which are done in schema
upgrade scripts. Work around which I have been using in absence of
transaction ddl is much better.
I doubt Oracle will provide transaction based ddl because of its
architecture and all exiting base of code; however, this does not mean
that this feature is not useful.
As I pointed databases are the only thing I know of which provide
transactions. Sure it will be nice if transaction is not limited to
DML.
sybr...@hccnet.nl
>
>I doubt Oracle will provide transaction based ddl because of its
>architecture and all exiting base of code; however, this does not mean
>that this feature is not useful.
The feature is not useful at all. Schemas changes need to be done in
maintenance windows. Databases are not garbage cans, like you seem to
think.
Evidently you, rather being a professional hacker, instead of a
database administrator, would like to have that. This does not mean it
is desirable.
Schema changes are planned. The environment is known. Properly planned
schema changes do NOT fail. Schema changes run by people who don't
know what they are doing, like you, do fail.
--
Sybrand Bakker
Senior Oracle DBA
Laurenz Albe
>> I have always been surprised why Oracle does a implicit commit when
>
> I don't know the real reason you can't rollback DDL, but I suspect
> part of it is simply the evolution of the code. By now, it would be
> difficult. And there are workarounds in the modern versions.
>
> As far as Serge's assertion that customers want to be able to change
> dicks in the middle of a screw, I mean, change defs in the middle of a
> view, that's as silly as some other things customers "want," like
> dirty reads. There may even be a legitimate requirement, but that's
> no reason to make it easy at the expense of other, more reasonable and
> ordinary things. I wonder how such things don't violate Codd's
> Physical Data Independence rule - they logically impair applications.
>
> Just because there is a Codd rule that states the same language must
> be used to access the online catalog doesn't mean all possilbe
> language features need to apply to the catalog.
I think that it is legitimate that Oracle handles things that way,
particularly if you consider that it is software that has been around
for a long time and carries a lot of legacy cruft.
It would probably be really difficult to change things now.
But I don't think that, just because of that, it is necessary or helpful
to conjure up proofs and arguments why transactional DDL is a Bad Thing.
In the ugly case that someone tries to change data definitions while
concurrent users are using the data, a transactional DDL system will not
behave so much different from Oracle anyway, because any sane approach
will prevent concurrent data modification.
Where I find that transactional DDL (as for example implemented by
PostgreSQL) is very useful and a Good Thing is for DDL scripts.
That way you can pack all DDL for a script that updates your application
(during a maintenance window of course) in a single transaction, and
it will be applied all-or-nothing.
Otherwise, if the script fails halfway through, you will either
have to figure out where it went wrong and continue from that point,
or (as Oracle does it) you have to write all your DDL scripts so that
they are idempotent, i.e. it does not matter if you apply them twice.
Yours,
Laurenz Albe
Serge Rielau
>> There are mechanics and engineers in this profession.
> understanding.
But yes. What is lacking is understanding.
DA Morgan
> Dbms_redef appears only for redefining tables. Its purpose is on-line
> redefinition of tables. It has too much overhead: requires lots of
> additional storage depending on size of your tables.
Incorrect. Look at the demo in Morgan's Library. You can change
constraints, triggers, etc.
> I doubt Oracle will provide transaction based ddl because of its
> architecture and all exiting base of code; however, this does not mean
> that this feature is not useful.
A bottle of scotch can be useful. But I generally don't carry one
with me while driving a car. I would make the same argument with
respect to production databases. It might be nice to have in dev
but anyone using it in test or prod should be shown the door.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damo...@x.washington.edu (replace x with u to respond)
zigz...@yahoo.com
> zigzag...@yahoo.com wrote:
> > Dbms_redef appears only for redefining tables. Its purpose is on-line
> > redefinition of tables. It has too much overhead: requires lots of
> > additional storage depending on size of your tables.
>
> Incorrect. Look at the demo in Morgan's Library. You can change
> constraints, triggers, etc.
>
> > I doubt Oracle will provide transaction based ddl because of its
> > architecture and all exiting base of code; however, this does not mean
> > that this feature is not useful.
>
> A bottle of scotch can be useful. But I generally don't carry one
> with me while driving a car. I would make the same argument with
> respect to production databases. It might be nice to have in dev
> but anyone using it in test or prod should be shown the door.
> --
> Daniel A. Morgan
> Oracle Ace Director & Instructor
> University of Washington
> Puget Sound Oracle Users Groupwww.psoug.org
It seems you are drunk in Oracle Scotch that you have lost objectivity
to anything else. It is shame that you are a University Professor.
Profressors should have broader midset instead of being salesman for a
company. No one can doubt your knowledge of Oracle, but at the same
everyone in this forum knows you lack objectivity.
When one database dominates, innovations stop.
ddf
On Nov 12, 2:35 pm, zigzag...@yahoo.com wrote:
> On Nov 12, 2:38 pm, DA Morgan <damor...@psoug.org> wrote:
>
>
>
>
>
> > zigzag...@yahoo.com wrote:
> > > Dbms_redef appears only for redefining tables. Its purpose is on-line
> > > redefinition of tables. It has too much overhead: requires lots of
> > > additional storage depending on size of your tables.
>
> > Incorrect. Look at the demo in Morgan's Library. You can change
> > constraints, triggers, etc.
>
> > > I doubt Oracle will provide transaction based ddl because of its
> > > architecture and all exiting base of code; however, this does not mean
> > > that this feature is not useful.
>
> > A bottle of scotch can be useful. But I generally don't carry one
> > with me while driving a car. I would make the same argument with
> > respect to production databases. It might be nice to have in dev
> > but anyone using it in test or prod should be shown the door.
> > --
> > Daniel A. Morgan
> > Oracle Ace Director & Instructor
> > University of Washington
> > damor...@x.washington.edu (replace x with u to respond)
> > Puget Sound Oracle Users Groupwww.psoug.org
>
> It seems you are drunk in Oracle Scotch that you have lost objectivity
> to anything else.
Not necessarily.
> It is shame that you are a University Professor.
I disagree. In my mind it's a shame you're behaving in such a manner.
> Profressors should have broader midset instead of being salesman for a
> company.
When did Daniel try to sell you anything?
> No one can doubt your knowledge of Oracle, but at the same
> everyone in this forum knows you lack objectivity.
>
This IS an Oracle forum ... have you forgotten that?
> When one database dominates, innovations stop.- Hide quoted text -
Yet I haven't witnessed that in over 20 years in the arena. You're
welcome to prove otherwise.
>
> - Show quoted text -
As I see it differences make the world an interesting place, and there
are plenty of them in this thread.
Any 'good' feature of a database is open to misuse and abuse,
regardless of the intent of the developers of the database code. I
agree with Laurenz that transactional DDL can be beneficial when used
to upgrade applications during a scheduled outage as all changes will
be successfully applied or they'll all be rolled back. No 'piecemeal'
modifications to code/objects that would need to be 'sifted through'
to find where to restart the process.
Then, I also agree with Daniel and Joel as anyone using transactional
DDL on a live production system, in the middle of a work day when
everyone and his cat is 'hitting' the database, should be considered a
fool. Yet, to implement transactional DDL for the first purpose opens
the floodgates for those with more 'horse' than 'sense' to do the
latter; it's a double-edged sword.
I'm used to Oracle and its ways (after over 20 years in the trenches)
and would not have thought of implementing transactional DDL in a
database. Certainly there are good reasons to have it (Laurenz listed
a very good one) just as there are good reasons to *not* have it. I
tend to weigh in on the 'it's not broken, so why fix it' side; I don't
want to have the need to ride herd on developers, programmers or
(gasp) end users creating DDL nightmares waiting to be committed in a
running production system. But, we're all different, and it would be
a terribly boring place if we weren't.
It's an interesting discussion, and I side with the currently
implemented DDL mechanisms Oracle provides. Others don't, and that
makes for a lively interchange of ideas. Just remember this is a
technical forum, not a pulpit. You can believe anything you want, and
I'll respect your right to believe it, as long as you don't try to
convert me in the process.
David Fitzjarrell
sybr...@hccnet.nl
>It seems you are drunk in Oracle Scotch that you have lost objectivity
>to anything else. It is shame that you are a University Professor.
>Profressors should have broader midset instead of being salesman for a
>company. No one can doubt your knowledge of Oracle, but at the same
>everyone in this forum knows you lack objectivity.
Hear, hear. It's the old accusation again!
Are you objective? What a joke!
As soon as you are contradicted you start flaming!
You may wish you have only 1 percent of the knowledge of Daniel, so
you don't need to go around everywhere, and crosspost every silly
question to Usenet *and* OTN Forums, using an anonymous handle.
zigz...@yahoo.com
I defintely have lot more objectivity than you and DA. My knowlefege
of Oracle is lot more than yours. People in all Oracle forums dislike
you, just search on internet you will get the facts.
Aya the Vampire Slayer
<snip>
>It's an interesting discussion, and I side with the currently
>implemented DDL mechanisms Oracle provides. Others don't, and that
>makes for a lively interchange of ideas. Just remember this is a
>technical forum, not a pulpit. You can believe anything you want, and
>I'll respect your right to believe it, as long as you don't try to
>convert me in the process.
I find that while I have no problem with the way Oracle is currently
implemented, I wouldn't mind them adding the "feature" of transactional
DDL. I don't think Oracle is less of a RDBMS without it, I don't think
that SQL Server is less of an RDBMS with it. If it's there, I will use
it, if it's not there, I won't. I tend to lean toward the opinion of: if
it's easy to add the feature, why not just go ahead and add it.
On the topic of idiots abusing such a feature: well, I suppose that
idiots can abuse anything at all, but it seems like if they don't learn
better quickly they will find themselves out of a job. DBA mistakes are
often very visible mistakes, for better or worse.
--
"Care must be exorcised when handring Opiticar System as it is apts to
be sticked by dusts and hand-fat." --Japanese Translators
"Keep your fingers off the lens." --Elton Byington, English Translator
Bob Jones
>> I am afraid what's lacking here is not mechanics and engineers but
>> understanding.
> Funny - we don't even get each others metaphors.
> But yes. What is lacking is understanding.
>
Yep, it's just basic RDBMS concept.
DA Morgan
> On Nov 12, 2:38 pm, DA Morgan <damor...@psoug.org> wrote:
>> zigzag...@yahoo.com wrote:
>>> Dbms_redef appears only for redefining tables. Its purpose is on-line
>>> redefinition of tables. It has too much overhead: requires lots of
>>> additional storage depending on size of your tables.
>> Incorrect. Look at the demo in Morgan's Library. You can change
>> constraints, triggers, etc.
>>
>>> I doubt Oracle will provide transaction based ddl because of its
>>> architecture and all exiting base of code; however, this does not mean
>>> that this feature is not useful.
>> A bottle of scotch can be useful. But I generally don't carry one
>> with me while driving a car. I would make the same argument with
>> respect to production databases. It might be nice to have in dev
>> but anyone using it in test or prod should be shown the door.
>> --
>> Daniel A. Morgan
>> Oracle Ace Director & Instructor
>> University of Washington
>> damor...@x.washington.edu (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org
>
>
> It seems you are drunk in Oracle Scotch that you have lost objectivity
> to anything else.
Nothing I posted above has anything to do with Oracle or any other
company's product. It is a totally generic statement about DDL and
production databases with respect to governance and change management.
> It is shame that you are a University Professor.
I feel the same way but I'm a lousy dancer.
> Profressors should have broader midset instead of being salesman for a
> company.
I really appreciate you saying this. It has been growing harder lately
to find people willing to set ethical boundaries for others to adhere
to. I've no doubt others will appreciate your contribution as much as I do.
> No one can doubt your knowledge of Oracle, but at the same
> everyone in this forum knows you lack objectivity.
This is a forum not a university classroom. I am under no obligation
to be objective here. This may amaze you but those of us that teach
are real people, with real lives, and real opinions and we are free
to express them.
> When one database dominates, innovations stop.
Let's see the Oracle database dominates. It has just a fraction under
50% of the market. So I guess Mark Townsend is out of a job. Hey Mark,
come on up and have some cognac with me and Mogens next week. We can
celebrate my lack of objectivity, your lack of innovation in the 10g
and 11g databases, and Mogens lack of a red vest.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damo...@x.washington.edu (replace x with u to respond)
Cristian Cudizio
I totally agree with David and i will add an example: also not having
type checking in programming languages as it is in Java may be useful
but experience has demonstrated that type checking prevents a lot of
problems.
This example in my opinion relates to your statement about usefulness
of
transactional DDL. Yes used in some manner DDL is userful, but in
other ways,
used by unwise people is dangerous.
however i think i found this discussion very interesting.
Regards,
Cristian
Noons
>> For example: Try to drop a table that is not there - a common example
>> of a
>> "oops" statement in an upgrade script. It produces an error, but does
>> NOT
>> cause a rollback to take place or lock other scripts out.
> Why would is cause a rollback? If you are doing DML (let's move to safer
> ground) and you are getting an error - say a check constraint violation
> - do you expect teh whole transaction to roll back?
That is PRECISELY what was suggested as an advantage
of the transactional DDL.
In SS's case, I'll make it clear:
if you MISTYPE the name of a table on an upgrade script - the
example of applicability of said feature - you will end up
with an incorrect script executed to the end, without so
much as a rollback.
Transactional DDL is not something that is triggered
or not depending on what is "convenient" to implement.
Either it works CONSISTENTLY and UNIFORMLY for ALL
DDL statements or it is simply a joke, to be put right
up there with the others - such as "web 2.0" and SOA.
> Typically I'd expect
> the statement to fail (statement atomicity) and then an exception
> handler can decide whether the problem as serious enough to fail the
> transaction or recover from it somehow.
> That's what those WHENEVER exceptions are there fore.
And that's precisely what is missing from the
SS implementation of transactional DDL.
Or at least a consistent way of implementing ALL
exceptions the same way, be it by default or by code.
Otherwise, don't even mention upgrade scripts as an example
of need for transactional DDL!
> Interesting. In my world again the statement would fail (and roll back)
> but the transaction remains intact. An exception handler initiates
> rollback if desired.
Exactly. I'd expect that to be the case, whatever
the conditions. With SS, it's only if you code explicitly
to cause that to happen: the default condition will bypass
all that in inconsistent ways.
That makes the feature unusable for that purpose.
Mladen Gogala
> Well Oracle made it vanish from the market to begin with.
Nope. Market forces made it vanish from the market. If RDB was generating
enough profit, DEC would still be alive and well. You're talking to an old
VMS hack who was, at one point in his life, a VMS system admin and teaching
others how to tune VMS 5.5-2 and earlier versions. I was a DEC admirer and
was attending DECUS in Cannes, France every year until 1992. DEC was, at one
point, number 2 in the computer industry, second only to the mighty IBM. It
vanished like a puff of smoke, devoured not by Compaq but by market forces.
Sad truth was that a PC with 66 MHZ i486 was running circles around VAX
4200, a $50,000 machine. Then there were stories with VAXbi I/O bus,DSSI,
the DEC version of SCSI, and closing the XMI bus. People invested a lot of
money into MIPS based DECSystem and then DEC told them to scrap that and
buy alpha with OSF. It didn't fly. DEC has hit the ground because of its
own stupidity and lack of marketing strategy. DEC stupidity has sunk many
other companies which have centered their marketing strategy around Q22 bus
and VAXbi. Emulex and Sabre were among them. Gazillion of companies that
were making VT420 and VT520 compatibles also went under. Worst of all, EVE,
by far the best editor that I've ever worked with, vanished from the market.
>
> Only because something is old, doesn't mean that it didn't have good
> features. I think rdb had data partitioning built in (e.g by index) long
> before Oracle was even thinking about it.
>
>> Why would they do that? To look more like SQL Server?
> That has nothing to do with making it look like SQL Server. I'm definitely
> not a fan of SQL Server (in fact I hate it) but I like transactional DDL
> and SQL Server isn't the only one to have this. Postgres and DB2 have that
> as well.
I have never worked with DB2 so I can't make any comments about it.
PostgreSQL is a great little database, seriously lacking debugging, tuning
and profiling tools as well as the methods to influence performance, like
hints or toying with the optimizer. PostgreSQL is miles behind Oracle
RDBMS. It would at least need wait interface to become really useful. PgSQL
is useful for small packaged applications for really small shop. If the
data model is OK and the application is well written, it will provide
excellent performance. It also has the ability to skip the first N records
in the fetch and start fetching from an offset in the cursor. That ability
is worth its weight in gold when it comes to producing web pages. Oracle
still cannot do that. If, on the other hand, the application is generated
hastily, using some code generator and an inadequate data model, it is
exceptionally hard to make it right because of the lack of debugging,
profiling and tuning tools. In other words, PostgreSQL is not there yet.
Serge Rielau
TSQL did (I think that is a statement of the past) suffer from a lack of
modern exception handling.
However the problem of a lost/unhandled error condition appears to be
orthogonal to what is actually lost.
I.e. assume SQL Server did auto commit its DDL (would that be what you
get if you do not state BEGIN TRANSACTION??) and a statement failed. The
script would continue just the same without the error being handled,
correct?
So I think we cannot blame transactional DDL for lacking of exception
handlers. ANSI SQL/PSM does provide these handlers btw.
Cheers
Serge
PS: When porting SQL Server apps to another DBMS (be it Oracle or DB2)
the problem of lost errors is a major issue in general since TSQL
developers have this nasty tendency to only do error checking wherher
they think an error could appear (in SQL Server). Have spent oodles of
time fixing these (and writing tools to track them)
DA Morgan
> It also has the ability to skip the first N records
> in the fetch and start fetching from an offset in the cursor. That ability
> is worth its weight in gold when it comes to producing web pages. Oracle
> still cannot do that.
I'm not so sure about this though it might take a very bit of a kludge.
Consider this syntax:
FORALL <index_name> IN INDICES OF <collection>
[BETWEEN <lower_boundary> AND <upper_boundary>]
<sql_statement>;
With a global temporary table you should be able to accomplish the goal.
And yes it would not be quite as elegant.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damo...@x.washington.edu (replace x with u to respond)
Shakespeare
> On Mon, 10 Nov 2008 10:02:08 +0100, Thomas Kellerer wrote:
>
>> But creating objects in a database needs to be consistent as well. I
>> don't see why having transactional DDL contradicts database consistency.
>>
>> Actually I think it adds to consistency.
>
> So, I create table, Noons creates an index to that table, in his own
> schema. If I was able to roll back the table creation, what happens to
> Nuno's index? Flashback table to before drop is possible with all releases
> that support flashback area, since the version 7.3.6.
>
Flashback won't do. Flashback is impossible after performing DDL on a
table that changes some of its structure. See this article:
http://dgielis.blogspot.com/2008/11/flashback-table-data-prevented-by.html
Shakespeare
DA Morgan
Depends on which flashback. Flashback database will work ... though
doing so has its own implications.