Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Muhammad Aamir .

Asked: February 23, 2002 - 11:03 pm UTC

Last updated: July 22, 2011 - 12:44 pm UTC

Version: 8.1.5

Viewed 10K+ times! This question is

You Asked

When we drop a column is there any rollback info generated because alter table drop column is DDL.If not what is purpose of
checkpoint (no of rows)in drop column command

and Tom said...

why do you (mistakenly) believe DDL doesn't generate rollback?

It does -- if the machine crashed in the middle of doing DDL, we need to be able to undo that operation when we recover.

DDL is protected by redo and undo like everything else in the database.

Rating

  (7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

please explain

abeginner2001, February 24, 2002 - 2:33 pm UTC

youn said "DDL is protected by redo and undo like everything else in the database." though ddl cannot be rollbacked.

Tom Kyte
February 24, 2002 - 3:45 pm UTC

DDL is processed like this (conceptually)


begin
COMMIT; -- any outstanding work
begin
DDL statement;
COMMIT; -- the DDL statement
exception
when others then
ROLLBACK; -- any work done by the DDL
RAISE; -- reraise the exception back to the client
end;
end;


DDL tends to do things to the data dictionary -- the data dictionary DRIVES the rest of the entire system. The transaction against the data dictionary must be as short as possible (to avoid bring the system to a total screeching halt). Therefore, we commit immediately before the DDL statement (so a failed DDL statement will not rollback your work) and we commit or rollback immediately after so as to have the transaction affecting the dictionary take as little time as possible.

DDL cannot be rolled back under YOUR control, this is true -- it either commits or rollsback automagically -- but it CAN and does in fact rollback.



ddl And rollback

muhammad Aamir Siddique, February 25, 2002 - 12:14 am UTC

great

DDL cannot be rolled back under YOUR control

Lelio, February 01, 2007 - 11:39 am UTC

Yoy said 'DDL cannot be rolled back under YOUR control', but you can force a DDL rollback raising an exception inside a DDL trigger (for example BEFORE ALTER).

Do you know if there is a way to rollback a DDL silently without raising en exception?

I need a way to avoid a DDL (for example a DROP TABLE) to be committed without receiving an error.
Tom Kyte
February 01, 2007 - 1:23 pm UTC

ahh, the silent "let us not have an error"

no, it'll not happen. it will fail, you need to do it differently.

system rollback segs or undo tabespace in alter database drop column..

jiang huang zheng, July 02, 2008 - 11:38 am UTC

Hello Tom

I would like to know that if I issue:
alter table drop column xxx, but xxx is blob column, I think I should plan my undo to fit that ,saying blob column contains 4g data.
under 9ir2 AUM mode, is system rollback segment used or the undo tablespace I used when this command issues? I think that since that command is a ddl , it should use system rollback system , right?
Thanks for your time!
Tom Kyte
July 07, 2008 - 10:16 am UTC

but.... blobs are not managed in undo - blobs manage their undo in their own segment.

there is

a) the lob locator, a relatively small attribute in the table.
b) the lob index - a segment separate and distinct from the table itself.
c) the lob segment - same as b


things in (a) - use undo as normal.
things in (b) - usually use undo, but this would be different since you are getting rid of the column.
things in (c) - do not use undo, they version themselves in the log segment.


You will find the drop column for a lob to be more undo friendly than a drop of a varchar2!



<b>
create table t ( x int, y varchar2(4000), z clob );

declare
    l_data long default rpad('*',32760,'*');
begin
    insert into t values ( 1, rpad('*',4000,'*'), l_data );
    for i in 1 .. 17
    loop
        insert /*+ append */ into t select * from t;
        commit;
    end loop;
end;
/

</b>


ops$tkyte%ORA10GR2> desc t
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 X                                                 NUMBER(38)
 Y                                                 VARCHAR2(4000)
 Z                                                 CLOB

ops$tkyte%ORA10GR2> connect /
Connected.

ops$tkyte%ORA10GR2> set timing on
ops$tkyte%ORA10GR2> @mystat "undo change"
ops$tkyte%ORA10GR2> set echo off

NAME                                            VALUE
------------------------------------------ ----------
undo change vector size                             0

Elapsed: 00:00:00.01
ops$tkyte%ORA10GR2> alter table t drop column z;

Table altered.

Elapsed: 00:03:22.49
ops$tkyte%ORA10GR2> @mystat "undo change"
ops$tkyte%ORA10GR2> set echo off

NAME                                            VALUE
------------------------------------------ ----------
undo change vector size                      16261868

<b>about 15mb of undo.... pretty small given what we just dropped off there..</b>

Elapsed: 00:00:00.01


ops$tkyte%ORA10GR2> connect /
Connected.
ops$tkyte%ORA10GR2> @mystat "undo change"
ops$tkyte%ORA10GR2> set echo off

NAME                                            VALUE
------------------------------------------ ----------
undo change vector size                             0

Elapsed: 00:00:00.00
ops$tkyte%ORA10GR2> alter table t drop column y;

Table altered.

Elapsed: 00:08:18.62
ops$tkyte%ORA10GR2> @mystat "undo change"
ops$tkyte%ORA10GR2> set echo off

NAME                                            VALUE
------------------------------------------ ----------
undo change vector size                     536936460

Elapsed: 00:00:00.00

<b>about 512mb - a bit larger, even though it was technically 1/8th the data volume!</b>




and the system rollback segment is not used for DDL, it is used for very special error like conditions (to hold undo from an offlined undo segment that might still be needed or for distributed transaction in an error state)

Rollback a DDL Operation

Purvesh, July 14, 2011 - 1:16 am UTC

Hi Tom,

We have a set of Dynamic SQL for creating/Altering tables;
In due course of execution of the Dynamic SQL in an Anonymous Block/Stored Procedure, if there is an error then any other successfully executed DDL Statements (Dynamic SQL) should be rolled back.

Example:

BEGIN
EXECUTE IMMEDIATE 'create table test_table_1 (col1 number not null)';
BEGIN
EXECUTE IMMEDIATE 'create test_table_2 (col1 number not null)';
EXCEPTION
WHEN OTHERS THEN
null;
END;
end;

In the above scenario, since the Statement No. 2 is malformed it would generate any error, however the Statement No. 1 is successfully executed and Table has been created.

Is there a way to Rollback the Successfully executed statements in that block other than dynamically reverting the executed statements?

Tom Kyte
July 15, 2011 - 9:01 am UTC

All DDL is executed like this:

begin
    COMMIT; -- implicit commit to end any existing transaction
    begin
       do the ddl;
       COMMIT; -- implicit commit to finish the DDL
    exception
    when others
    then
        rollback;
        RAISE;
    end;
end;




All DDL is implicitly commits upon successful completion, or rolled back upon failure.

Rollback of DDL

Purvesh, July 20, 2011 - 1:31 am UTC

Thanks Tom for your Reply.

The problem we face is "If any Malformed DDL statements are executed after 1 or few successful execution of DDL, is there any way to rollback the successfully executed DDL's?"

As we can think, 1 way is to Descending order Reversion of the DDL's. This however would be a difficult approach; Hence, Please suggest a better approach!!!
Tom Kyte
July 22, 2011 - 12:44 pm UTC

Short of using flashback database - to revert the entire database back to the way it was before the DDL, there is no other way.

Oracle is stupid

Min, January 25, 2012 - 5:57 am UTC

how can you deal with the situation (on a production environment), that you have a set of master DDL statements in a script, if the first 10 DDL statements succeeded, but the rest failed;

I could see two manual options after fixing the problematic DDL statement instead of one perfect option:
the two manual options are: a) manually revert/rollback the DDL statement by dropping the object or modifying it back to whatever it was before one by one, and then run the whole script again; b) split the script from the failure point, and run the 'uncommitted' part again and you may have to repeat this process a number of times.
the perfect option which SQL SERVER has is: allow users to control their session commitment, i.e. if any of the DDL statement in the script fails, user can roll back all DDL or DML statements ==> this way, user can just simply fix up the problematic DDL statement, then simply run the whole script again ==> it does not need any other manual job.

Regards.