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)