测试APPEND INSERT是否产生UNDO信息的过程

原创

lhdz__bj 2024-07-23 09:59:47 博主文章分类：Oracle ©著作权

文章标签 oracle append insert undo SQL 文章分类 JavaScript 前端开发

©著作权归作者所有：来自51CTO博客作者lhdz__bj的原创作品，请联系作者获取转载授权，否则将追究法律责任

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