--************UNDO表空间
Select * From dba_tablespaces
Select * From dba_data_files
Select * From dba_temp_files
Select * From dba_segments Where tablespace_name='UNDOTBS2' --查看UNDO表空间段 回滚段
Select * From dba_extents Where segment_name = 'TT'
Select * From dba_tables Where Table_name = 'TT'
--创建UNDO表空间
Create Undo Tablespace undotbs2 Datafile 'C:\oracle\product\10.2.0\oradata\undotbs02.dbf' Size 10m;
Insert Into t Values(1,'a');
Insert Into t Values(2,'b')
Update t Set Name='c' Where Id = 1 --未提交
Select * From t --本窗口看到 1 c sysdate
重打开一个cmd窗口:
cd \ --操作系统验证
sqlplus / As Sysdba --操作系统验证后,不需要密码即可登录sys用户
Select * From system.t --新窗口看到 1 a sysdate
Select t.*,Rowid From t --id=1 rowid=AAAC8LAABAAAHNSAAA
AAAC8L AAB AAAHNS AAA
12043 1 29522 0
Alter System Dump Datafile 1 Block 29522 --将文件1的第29522个块写入dump文件中
show parameter User --查看用户dump文件的路径 C:\ORACLE\PRODUCT\10.2.0\ADMIN\TESTDB\UDUMP
Select sid From v$mystat --session id 142
Select PADDR From v$session Where sid=142 --PADDR 1FE4C60C
Select SPID From V$PROCESS Where ADDR='1FE4C60C' --SPID 628 **所以UDUMP文件夹下,文件名为testrid_ora_628.trc,11G增加专门列trace_file
/*
**************
Start dump data blocks tsn: 0 file#: 1 minblk 29522 maxblk 29522
buffer tsn: 0 rdba: 0x00407352 (1/29522)
scn: 0x0000.0006f180 seq: 0x01 flg: 0x04 tail: 0xf1800601
frmt: 0x02 chkval: 0x9dd8 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x07A08400 to 0x07A0A400
**************
内存数据省略
**************
Block header dump: 0x00407352
Object id on Block? Y
seg/obj: 0x2f0b csc: 0x00.6f140 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0008.02e.000000ce 0x008000a1.006e.0d C--- 0 scn 0x0000.0006f13b
0x02 0x0007.01b.0000009e 0x008002bd.005e.0a ---- 1 fsc 0x0000.00000000
data_block_dump,data header at 0x7a0845c
===============
tsiz: 0x1fa0
hsiz: 0x16
pbl: 0x07a0845c
bdba: 0x00407352
76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f90
avsp=0x1f78
tosp=0x1f78
0xe:pti[0] nrow=2 offs=0
0x12:pri[0] offs=0x1f98
0x14:pri[1] offs=0x1f90
block_row_dump:
tab 0, row 0, @0x1f98
tl: 8 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 02
col 1: [ 1] 63
tab 0, row 1, @0x1f90
tl: 8 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 03
col 1: [ 1] 62
end_of_block_dump
End dump data blocks tsn: 0 file#: 1 minblk 29522 maxblk 29522
*/
2行数据,第一行,
tab 0, row 0, @0x1f98
tl: 8 fb: --H-FL-- lb: 0x2 cc: 2 --lb:lock byte 0x0代表未锁定 0x2代表锁定,查看块头上的itl(事务列表)
col 0: [ 2] c1 02 --第一列,id列,存放的是1的16进制数,select utl_raw.cast_to_number('c102') from dual
col 1: [ 1] 63 --第二列,name列,存放的是小写c的16进制 select dump('c',16) from dual
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0008.02e.000000ce 0x008000a1.006e.0d C--- 0 scn 0x0000.0006f13b
0x02 0x0007.01b.0000009e 0x008002bd.005e.0a ---- 1 fsc 0x0000.00000000
/*
Flag ---- 表示块上有事务
Lck 1 表示锁定了1行
Uba:UNDO BLOCK ADDRESS 修改前的地址保存在该地址008002bd ;事务列表数0a
*/
select to_number('008002bd','xxxxxxxx') from dual 8389309 --008002bd 转成10进制
select DBMS_UTILITY.data_block_address_file(8389309) FROM DUAL --查看在哪个数据文件,结果2号文件
select DBMS_UTILITY.data_block_address_block(8389309) FROM DUAL --701块上
Select * From dba_data_files where file_id = 2 --查看file_id=2的数据文件是UNDOTBS01.DBF
在另一个session中
Alter System Dump Datafile 2 Block 701 --根据session id确定文件是testrid_ora_3268.trc
在testrid_ora_3268.trc中找到事务0a
/*
*-----------------------------
* Rec #0xa slt: 0x1b objn: 12043(0x00002f0b) objd: 12043 tblspc: 0(0x00000000)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
uba: 0x008002bd.005e.08 ctl max scn: 0x0000.0006ea0e prv tx scn: 0x0000.0006ea74
txn start scn: scn: 0x0000.0006f036 logon user: 5
prev brb: 8389305 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
op: Z
Array Update of 1 rows:
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 8
ncol: 2 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x00407352 hdba: 0x00407351
itli: 2 ispac: 0 maxfr: 4863
vect = 3
col 1: [ 1] 61
*/
可以看到值是16进制的61,select dump('a',16) from dual 即小写a
Select * From v$transaction --UBAFIL=2 UBABLK=701 UBASQN=94 UBAREC=10 即Uba所代表的值0x008002bd.005e.0a;文件号2,701块,UBA序列号为94,UBA在block中的位置10
/*
ADDR XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS START_TIME START_SCNB START_SCNW START_UEXT START_UBAFIL START_UBABLK START_UBASQN START_UBAREC SES_ADDR FLAG SPACE RECURSIVE NOUNDO PTX NAME PRV_XIDUSN PRV_XIDSLT PRV_XIDSQN PTX_XIDUSN PTX_XIDSLT PTX_XIDSQN DSCN-B DSCN-W USED_UBLK USED_UREC LOG_IO PHY_IO CR_GET CR_CHANGE START_DATE DSCN_BASE DSCN_WRAP START_SCN DEPENDENT_SCN XID PRV_XID PTX_XID
1 1EA18758 7 27 158 2 701 94 10 ACTIVE 12/30/13 21:29:02 454971 0 3 2 701 94 10 1FF2B2DC 3587 NO NO NO NO 0 0 0 0 0 0 0 0 1 1 41 3 2297 0 2013/12/30 21:29:02 0 0 454971 0 07001B009E000000 0000000000000000 0000000000000000
XIDUSN:回滚段UNDO SEGMENT Number select * from dba_rollback_segs where segment_id=7 --查看UNDO SEGMENT select * from v$rollstat --监控在线的undo段,只有USN=7的段,XACTS=1代表有1个事务
XIDSLOT:事务槽号
XIDSQN:事务序列号
UBAFIL:UBA所在文件号
UBABLK:UBA所在块号
UBASQN:UBA序列号
UBAREC:UBA在Block中的位置
*/
v$undostat
Select * From dba_tablespaces
Select * From dba_data_files
Select * From dba_temp_files
Select * From dba_segments Where tablespace_name='UNDOTBS2' --查看UNDO表空间段 回滚段
Select * From dba_extents Where segment_name = 'TT'
Select * From dba_tables Where Table_name = 'TT'
--创建UNDO表空间
Create Undo Tablespace undotbs2 Datafile 'C:\oracle\product\10.2.0\oradata\undotbs02.dbf' Size 10m;
Insert Into t Values(1,'a');
Insert Into t Values(2,'b')
Update t Set Name='c' Where Id = 1 --未提交
Select * From t --本窗口看到 1 c sysdate
重打开一个cmd窗口:
cd \ --操作系统验证
sqlplus / As Sysdba --操作系统验证后,不需要密码即可登录sys用户
Select * From system.t --新窗口看到 1 a sysdate
Select t.*,Rowid From t --id=1 rowid=AAAC8LAABAAAHNSAAA
AAAC8L AAB AAAHNS AAA
12043 1 29522 0
Alter System Dump Datafile 1 Block 29522 --将文件1的第29522个块写入dump文件中
show parameter User --查看用户dump文件的路径 C:\ORACLE\PRODUCT\10.2.0\ADMIN\TESTDB\UDUMP
Select sid From v$mystat --session id 142
Select PADDR From v$session Where sid=142 --PADDR 1FE4C60C
Select SPID From V$PROCESS Where ADDR='1FE4C60C' --SPID 628 **所以UDUMP文件夹下,文件名为testrid_ora_628.trc,11G增加专门列trace_file
/*
**************
Start dump data blocks tsn: 0 file#: 1 minblk 29522 maxblk 29522
buffer tsn: 0 rdba: 0x00407352 (1/29522)
scn: 0x0000.0006f180 seq: 0x01 flg: 0x04 tail: 0xf1800601
frmt: 0x02 chkval: 0x9dd8 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x07A08400 to 0x07A0A400
**************
内存数据省略
**************
Block header dump: 0x00407352
Object id on Block? Y
seg/obj: 0x2f0b csc: 0x00.6f140 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0008.02e.000000ce 0x008000a1.006e.0d C--- 0 scn 0x0000.0006f13b
0x02 0x0007.01b.0000009e 0x008002bd.005e.0a ---- 1 fsc 0x0000.00000000
data_block_dump,data header at 0x7a0845c
===============
tsiz: 0x1fa0
hsiz: 0x16
pbl: 0x07a0845c
bdba: 0x00407352
76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f90
avsp=0x1f78
tosp=0x1f78
0xe:pti[0] nrow=2 offs=0
0x12:pri[0] offs=0x1f98
0x14:pri[1] offs=0x1f90
block_row_dump:
tab 0, row 0, @0x1f98
tl: 8 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 2] c1 02
col 1: [ 1] 63
tab 0, row 1, @0x1f90
tl: 8 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 03
col 1: [ 1] 62
end_of_block_dump
End dump data blocks tsn: 0 file#: 1 minblk 29522 maxblk 29522
*/
2行数据,第一行,
tab 0, row 0, @0x1f98
tl: 8 fb: --H-FL-- lb: 0x2 cc: 2 --lb:lock byte 0x0代表未锁定 0x2代表锁定,查看块头上的itl(事务列表)
col 0: [ 2] c1 02 --第一列,id列,存放的是1的16进制数,select utl_raw.cast_to_number('c102') from dual
col 1: [ 1] 63 --第二列,name列,存放的是小写c的16进制 select dump('c',16) from dual
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0008.02e.000000ce 0x008000a1.006e.0d C--- 0 scn 0x0000.0006f13b
0x02 0x0007.01b.0000009e 0x008002bd.005e.0a ---- 1 fsc 0x0000.00000000
/*
Flag ---- 表示块上有事务
Lck 1 表示锁定了1行
Uba:UNDO BLOCK ADDRESS 修改前的地址保存在该地址008002bd ;事务列表数0a
*/
select to_number('008002bd','xxxxxxxx') from dual 8389309 --008002bd 转成10进制
select DBMS_UTILITY.data_block_address_file(8389309) FROM DUAL --查看在哪个数据文件,结果2号文件
select DBMS_UTILITY.data_block_address_block(8389309) FROM DUAL --701块上
Select * From dba_data_files where file_id = 2 --查看file_id=2的数据文件是UNDOTBS01.DBF
在另一个session中
Alter System Dump Datafile 2 Block 701 --根据session id确定文件是testrid_ora_3268.trc
在testrid_ora_3268.trc中找到事务0a
/*
*-----------------------------
* Rec #0xa slt: 0x1b objn: 12043(0x00002f0b) objd: 12043 tblspc: 0(0x00000000)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
uba: 0x008002bd.005e.08 ctl max scn: 0x0000.0006ea0e prv tx scn: 0x0000.0006ea74
txn start scn: scn: 0x0000.0006f036 logon user: 5
prev brb: 8389305 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
op: Z
Array Update of 1 rows:
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 8
ncol: 2 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x00407352 hdba: 0x00407351
itli: 2 ispac: 0 maxfr: 4863
vect = 3
col 1: [ 1] 61
*/
可以看到值是16进制的61,select dump('a',16) from dual 即小写a
Select * From v$transaction --UBAFIL=2 UBABLK=701 UBASQN=94 UBAREC=10 即Uba所代表的值0x008002bd.005e.0a;文件号2,701块,UBA序列号为94,UBA在block中的位置10
/*
ADDR XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS START_TIME START_SCNB START_SCNW START_UEXT START_UBAFIL START_UBABLK START_UBASQN START_UBAREC SES_ADDR FLAG SPACE RECURSIVE NOUNDO PTX NAME PRV_XIDUSN PRV_XIDSLT PRV_XIDSQN PTX_XIDUSN PTX_XIDSLT PTX_XIDSQN DSCN-B DSCN-W USED_UBLK USED_UREC LOG_IO PHY_IO CR_GET CR_CHANGE START_DATE DSCN_BASE DSCN_WRAP START_SCN DEPENDENT_SCN XID PRV_XID PTX_XID
1 1EA18758 7 27 158 2 701 94 10 ACTIVE 12/30/13 21:29:02 454971 0 3 2 701 94 10 1FF2B2DC 3587 NO NO NO NO 0 0 0 0 0 0 0 0 1 1 41 3 2297 0 2013/12/30 21:29:02 0 0 454971 0 07001B009E000000 0000000000000000 0000000000000000
XIDUSN:回滚段UNDO SEGMENT Number select * from dba_rollback_segs where segment_id=7 --查看UNDO SEGMENT select * from v$rollstat --监控在线的undo段,只有USN=7的段,XACTS=1代表有1个事务
XIDSLOT:事务槽号
XIDSQN:事务序列号
UBAFIL:UBA所在文件号
UBABLK:UBA所在块号
UBASQN:UBA序列号
UBAREC:UBA在Block中的位置
*/
v$undostat
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15810196/viewspace-1130232/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/15810196/viewspace-1130232/