10 Managing Undo Data
一、Objectives1、Describe the purpose of undo data2、Implement Automatic Undo Management 3、Create and configure undo segment4、obtain undo segment information二、undo segmentUndo 和Trancation 相关在进行Trancation时候,将原有数据保存的地方称之为undo segment,如果Traincation失败,在将保存在undo segment的数据还原回来undo segment 是可以循环使用的三、read consistency 读一致性如果一个用户在做trancation时,在提交之前另外一个用户,在查看同一个表示,他不应该看到第一个用户所做的修改，只有commit或rollback才可以read consistency 在多用户是非常重要的每个select 都会分配一个scn 在查询数据时,如果数据的scn大于select 的scn则,select 去 undo segment 中读取原来的数据,这样就保证了数据的读一致性四、Redo & undo1、执行一条insert redo 和 undo 的操作①、insert将数据存在内存中②、产生一条redo log ③、产生一个undo ④、undo 本身也会产生一个与之对应的redo log⑤、加上表中有一个索引,索引也会产生一个redo log⑥、假设现在断电,我们不需要进行任何操作,因为undo 和 redo log 都在内存中操作的,断电后都消失了⑦、先写redo数据(lgwr),然后在在写数据(dbwr)2、执行update语句3、执行delete语句4、执行commit语句①、将redo log buffer 中的数据写到redo log file中②、如果此时断电,oracle重启的时候会重redo log file读取记录并恢复五、Types of undo Segment1、system:used for objects in the system tablespace2、non-system:Used for objects in other tablespace ***3、Deferred:Used when tablespaces are taken offline immediate ,temporary,or for recovery六、Auto Undo Management :config1、Configure two paramenters in the initalization file:--undo_management--undo_tablespace2、Create at least one undo tablespace3、下面两个参数很重要UNDO_MANAGEMENT=AUTOUNDO_TABLESPACE=undo tablespaces Nameundo_retention=900 单位秒(undo数据在undo空间保存的时间)4、switching undo tablespaces(切换undo tablespaces)alter system set undo_tablespace=New Undo TableSpace Name5、删除drop tablespace undoTableSpace Name6、创建undo tablesplacecreate undo tablespace myundotbs datafile '/u01/app/oracle/product/10.2.0/oradata/oamis/myundotbs1.dbf' size 10M;7、查询表空间 select * from dba_tablespaces8、切换表空间alter system set undo_tablespace=myundotbs ;9、修改参数undo_retention 默认900秒alter system set undo_retention=900 scope=both;10、Undo Data Statisticsselect end_time,begin_time,undoblks from v$undostat;七、一个极端的实验1、查询数据文件select * from dba_data_file;2、创建undo 表空间create undo tablespace smallundo datafile '/u01/app/oracle/product/10.2.0/oradata/oamis/smallundo1.dbf' size 2M autoextend off;3、切换undo 表空间alter system set undo_tablespace=smallundo;4、查看undo 参数show parameter undo;5、查看transactionselect addr,used_ublk from v$transaction;6、插入100w条数据beginfor i in 1..1000000loop insert into tt values(i,'boobooke');end loop;end; 报错信息ORA-30036: unable to extend segment by 8 in undo tablespace 'SMALLUNDO'ORA-06512: at line 57、切换到原来的表空间alter system set undo_tablespace=smallundo;8、在次查看select addr,used_ublk from v$transaction;需要8621个空间 每个空间8k9、提交commit;后在查看这是为空,undo segment 已经可以被其他人使用了select addr,used_ublk from v$transaction;八、AUM:Size an undo tablespaceDetermining a size for the undo tablespace requires three pieces of informantion1、(UR)UNDO_RETENTION in seconds2、(UPS) Number of undo data blocks generated per second3、oracle block4、计算大小的方法①、每秒钟最多的undo块数select max(undoblks/((end_time-begin_time)*24*3600)) from v$undostat;②、查看保留秒数show parameter undo ③、查看块大小(字节数)show parameter block三者相乘就是undo 所需要的大小
