DDL操作产生产生UNDO和REDO。原因是:
delete 需要把每个有数据的数据块读出来,写上delete标记, 另外还要把delete的内容拷一份到undo,还要把它的动作记到redo。
而truncate , 就是在系统表(或者tablespace bitmap)里面,把相关的extent 的 标记未未使用,另外把dba_objects(obj$)里的data_object_id换一下。
1.ddl所产生的undo量视乎其所要维护数据字典的操作类型和操作量.DDL执行失败也产生少量UNDO,因为执行少量递归操作后,Oracle发现所要drop的对象并不存在,将会rollback之前的"部分"递归dml操作。
2.DDL操作产生的REDO是因为DDL修改的字典表和一些段头信息产生的redo。
SQL> select name,value as bytes from (select b.name,a.value from v$mystat a,v$statname b where a.STATISTIC#=b.statistic#) where name='redo size' or name like 'undo change%';
NAME BYTES
---------------------------------------------------------------- ----------
redo size 5628
undo change vector size 2212
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
SYS_TEMP_FBT TABLE
TEST TABLE
TEST1 TABLE
SQL>
drop table test;
Table dropped
SQL> select name,value as bytes from (select b.name,a.value from v$mystat a,v$statname b where a.STATISTIC#=b.statistic#) where name='redo size' or name like 'undo change%';
NAME BYTES
---------------------------------------------------------------- ----------
redo size 12608
undo change vector size 4216
SQL> select * from cat;
TABLE_NAME TABLE_TYPE
------------------------------ -----------
BIN$eSjr2ednQKavapT1EPZ/aw==$0 TABLE
SYS_TEMP_FBT TABLE
TEST1 TABLE
SQL> select 12608-5628 as redo_size_drop_change,4216-2212 as undo_size_dorp_change from dual;
REDO_SIZE_DROP_CHANGE UNDO_SIZE_DORP_CHANGE
--------------------- ---------------------
6980 2004