测试APPEND INSERT是否产生UNDO信息的过程
D:\>sqlplus test/test
SQL*Plus: Release 11.1.0.6.0 – Production on 星期三 06月 29 19:46:41 2016
Copyright (c) 1982, 2007, Oracle. All rights reserved.
连接到:
Oracle Database 10g Express Edition Release 10.2.0.1.0 – Production
SQL>
SQL> commit; –提交
提交完成。
SQL> alter system flush buffer_cache; –清缓冲
系统已更改。
SQL> get undo5
1 select n.name,v.value from v$sesstat v,v$statname n
2 where v.statistic#=n.statistic#
3 and n.name in(‘undo change vector size’,’redo size’,’DBWR undo block writes’,’redo blocks written’)
4* and v.sid=(select distinct sid from v$mystat)
–获取当前会话的(‘undo change vector size’,’redo size’,’DBWR undo block writes’,’redoblocks written’
SQL> colu name for a30
SQL> /
NAME VALUE
—————————— ———-
DBWR undo block writes 0
redo size 0
redo blocks written 0
undo change vector size 0
SQL> get undo_redo
1 select * from v$sysstat where name in(‘DBWR undo block writes’,
2* ‘redo blocks written’)
–获取当前系统的’DBWR undo block writes’,’redoblocks written’
SQL> /
STATISTIC# NAME CLASS VALUE STAT_ID
———- —————————— ———- ———- ———-
75 DBWR undo block writes 8 214 111270822
139 redo blocks written 2 3183 2391431605
SQL> insert /*+ append*/ into &tab_new select * from &tab_old; –执行direct path INSERT
输入 tab_new 的值: bb
输入 tab_old 的值: aa
原值 1: insert /*+ append*/ into &tab_new select * from &tab_old
新值 1: insert /*+ append*/ into bb select * from aa
已创建4行。
SQL> select XIDUSN,xidslot,xidsqn,ubafil,ubablk,ubasqn,ubarec,USED_UBLK
2 from v$transaction; –获取当前事务回滚段
XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC
———- ———- ———- ———- ———- ———- ———-
USED_UBLK
———-
7 4 196 0 0 0 0
1
SQL> select name from v$rollname where usn=&usnn; –获取当前事务回滚段名
输入 usnn 的值: 7
原值 1: select name from v$rollname where usn=&usnn
新值 1: select name from v$rollname where usn=7
NAME
——————————
_SYSSMU7$
SQL> alter system dump undo header “&un”; –导出当前事务提交前回滚段头信息
输入 un 的值: _SYSSMU7$
原值 1: alter system dump undo header “&un”
新值 1: alter system dump undo header “_SYSSMU7$”
系统已更改。
SQL> COMMIT; –提交事务
提交完成。
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE; –刷新缓冲
系统已更改。
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE; –刷新缓冲
系统已更改。
SQL> GET UNDO5
1 select n.name,v.value from v$sesstat v,v$statname n
2 where v.statistic#=n.statistic#
3 and n.name in(‘undo change vector size’,’redo size’,’DBWR undo block writes’,’redo blocks written’)
4* and v.sid=(select distinct sid from v$mystat)
–获取事务执行并提交后的’undo change vector size’,’redo size’,’DBWR undo block writes’,’redo blocks written’信息
SQL> /
NAME VALUE
—————————— ———-
DBWR undo block writes 0
redo size 4264
redo blocks written 0
undo change vector size 1064
–注意：事务执行前undo change vector size：0，执行direct path INSERT后，其值：1064，前后差值：1064
SQL> GET UNDO_REDO
1 select * from v$sysstat where name in(‘DBWR undo block writes’,
2* ‘redo blocks written’)
–获取当前系统DBWR undo block writes’,’redo blocks written’信息
SQL> /
STATISTIC# NAME CLASS VALUE STAT_ID
———- —————————— ———- ———- ———-
75 DBWR undo block writes 8 216 111270822
139 redo blocks written 2 3195 2391431605
–注意：事务执行前DBWR undo block writes：214 ，执行direct path INSERT后，其值：216，前后差值：2
SQL> alter system dump undo header “&un”; –事务提交后再次导出事务使用回滚段头信息
输入 un 的值: _SYSSMU7$
原值 1: alter system dump undo header “&un”
新值 1: alter system dump undo header “_SYSSMU7$”
系统已更改。
SQL> ALTER SYSTEM DUMP DATAFILE 2 BLOCK MIN 5391 BLOCK MAX 5393;
–由导出事务提交后回滚段头信息确定，事务提交后xid对应的回滚段数据文件及块号：0x00801510 –>对应文件号（二进制前十位）：2 块号（二进制后22位）：5392
系统已更改。
SQL> select object_name from dba_objects where object_id=13697; –由导出回滚段块中，相应事务XID对应的undo信息中，objn=13697,获取此UNDO信息对应的数据库对象为表BB,这正是我们在其上执行direct path INSERT操作的表；
OBJECT_NAME
——————————————————————————–
BB