DDL操作产生UNDO和REDO的示例

长平狐 发布于 2013/09/17 15:12
阅读 739
收藏 0
 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
原文链接:http://blog.csdn.net/q947817003/article/details/11353873
加载中
OSCHINA
登录后可查看更多优质内容
返回顶部
顶部