Comment below.
HJR
--
----------------------------------------------
Resources for Oracle: http://www.hjrdba.com
===============================
"Frédéric Bachelier" <fbacheli_at_capgemini.fr> wrote in message
news:a6sm4d$cmb$1_at_s1.read.news.oleane.net...
> Hi Tom,
>
> I must say I am surprised by your answer. I thought, and many other people
> around me (we are all developers), that an alteration (of a table in my
> question) is done directly without any rollback possible.
> Maybe the rollback is not possible for a user to invoke it, and only the
> Oracle system may use it in case of a crash.
>
Altering a table is a DDL command, so there is an implied commit both before
and after the command. So no, it's not possible to rollback the alteration.
Ordinarily, DDL commands imply only DML on the data dictionary tables, and
such DML produces a relatively small amount of rollback in the system
rollback segment. And yes, that rollback is generated regardless of the
fact that *you* can't issue a rollback *command*, because -as Tom pointed
out- rollback is used for rather more than just the rolling back of your
transactions (read-consistent image preparation is the big one most people
forget about).
In your case, the DDL you are performing happens also to require a large
amount of DML to be performed to each of your existing records in the table,
and that will generate a rather large amount of rollback in non-system
rollback segments (as well as the system rollback segment, which is still
handling the implied DML on the data dictionary required to effect the
addition of a new column to the table). Again, that rollback is required
for read-consistency purposes, and its existence will not give you the
ability to roll the alteration of the table back, because of the implied
commit that DDL brings with it.
Regards
HJR
> Anyway, since a rollback is possible, there is a risk about the size of
the
> rollback segments, isn't there ? Now, this is my problem. The table is so
> large, that we don't have spare space, and no possibility of adding new
> disks. How large would it be necessary for the rollback segments ? I ask
> only in our situation where we want to add a new column varchar(4) with
the
> default value '0000'.
>
>
> Thank you,
>
> Frédéric Bachelier
>
>
>
Received on Fri Mar 15 2002 - 12:37:04 CST
