undo系列学习之深入剖析一个事务的操作流程
undo系列学习之深入剖析一个事务的操作流程
开篇我们先用一个实验引出事务:
- sys@ORCL> select xid,xidusn,xidslot,xidsqn,ubablk,ubafil from v$transaction;
- no rows selected
没找到相应的事务信息,用hr发起一条事务:
- hr@ORCL> select * from p;
- ID TEST
- ---------- -----
- 2 f
- 3 g
- 1 a
- hr@ORCL> update p set test='w' where id=1;
- 1 row updated.
再次查看事务信息:
- sys@ORCL> /
- XID XIDUSN XIDSLOT XIDSQN UBABLK UBAFIL
- ---------------- ---------- ---------- ---------- ---------- ----------
- 02000800E2010000 2 8 482 3339 2
查看hr发起事务所对应的回滚段:
- sys@ORCL> select xidusn,sid,username from v$transaction t,v$session s where t.ses_addr=s.saddr;
- XIDUSN SID USERNAME
- ---------- ---------- ------------------------------
- 2 141 HR
- 9 158 SCOTT
查看2号回滚段的段名:
- sys@ORCL> select * from v$rollname;
- USN NAME
- ---------- ------------------------------
- 0 SYSTEM
- 1 _SYSSMU1$
- 2 _SYSSMU2$
- 3 _SYSSMU3$
- 4 _SYSSMU4$
- 5 _SYSSMU5$
- 6 _SYSSMU6$
- 7 _SYSSMU7$
- 8 _SYSSMU8$
- 9 _SYSSMU9$
- 10 _SYSSMU10$
- 11 rows selected.
然后把2号回滚段的段头dump出来,到udump目录下去找:
- sys@ORCL> select header_block,header_file from dba_segments where segment_name='_SYSSMU2$';
- HEADER_BLOCK HEADER_FILE
- ------------ -----------
- 41 2
- sys@ORCL> alter system dump undo header '_SYSSMU2$';
- System altered.
- 查看当前会话的server process的进程编号:
- sys@ORCL> select spid from v$process where addr in (select paddr from v$session where sid=(select sid from v$mystat where rownum=1));
- SPID
- ------------
- 5446
|
评论暂时关闭