update一条语句的来龙去脉
update一条语句的来龙去脉在翻电脑资料的时候看到了如下一个图,随即兴致盎然的在一次研究了一下内部过程。容易混淆的知识点:uba=0x00800055.02de.3f0x00800055代表数据的前镜像seq:代表是顺序号3f,是undo记录的开始地址(irb信息)xid=0x0006.018.000036ce该事务指向6号回滚段,slot好为0x17,wrap#为36cexid=undo.segment.number+transaction.table.slot.number+wrap;dba:包含这个事务的前镜像的数据块地址:转换为二进制,然后转换为前22为块后10数据文件号bdba:0x00405c5a 记录了更改数据块的地址。session 1:首先切换一下redo,保证新产生的redo条目存在于干净的redo file中:SQL> alter system switch logfile;System altered.SQL> select * from v$log;GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------1 1 364 52428800 512 1 NO CURRENT 5294094 24-OCT-13 2.8147E+142 1 362 52428800 512 1 YES INACTIVE 5293624 24-OCT-13 5294000 24-OCT-133 1 363 52428800 512 1 YES ACTIVE 5294000 24-OCT-13 5294094 24-OCT-13SQL> conn rhys/amyConnected.SQL> select * from emp;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ----------7500 AMY CLERK 7902 17-DEC-80 800 207499 amy SALESMAN 7698 20-FEB-81 1600 300 307521 WARD SALESMAN 7698 22-FEB-81 1250 500 307566 JONES MANAGER 7839 02-APR-81 2975 207654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 307698 BLAKE MANAGER 7839 01-MAY-81 2850 307782 CLARK MANAGER 7839 09-JUN-81 2450 107788 SCOTT ANALYST 7566 19-APR-87 3000 207839 KING PRESIDENT 17-NOV-81 5000 107844 TURNER SALESMAN 7698 08-SEP-81 1500 0 307876 ADAMS CLERK 7788 23-MAY-87 1100 20EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ----------7900 JAMES CLERK 7698 03-DEC-81 950 307902 FORD ANALYST 7566 03-DEC-81 3000 207934 MILLER CLERK 7782 23-JAN-82 1300 1014 rows selected.更新一条数据:SQL> update emp set ename='Rhys' where empno=7500;1 row updated.查看当前用户信息:SQL> select sid,serial#,username from v$session where username='RHYS';SID SERIAL# USERNAME---------- ---------- ------------------------------1 7 RHYSSQL>session 2:在另一个会话中转储redo file内容:SQL> set linesize 200SQL> col name for a60SQL> alter system dump logfile '/opt/app/oracle/oradata/RHYS/redo01.log';SQL> col value for a60SQL> r1* select * from v$diag_info where name='Default Trace File'INST_ID NAME VALUE---------- ------------------------------------------------------------ ------------------------------------------------------------1 Default Trace File /opt/app/oracle/diag/rdbms/rhys/RHYS/trace/RHYS_ora_6532.trcSQL>session 3:SQL> select addr,xidusn,xidslot,xidsqn,ubafil,ubablk,ubasqn,status from v$transaction;select usn,latch,extents,rssize,xacts,gets,optsize,wraps,status from v$rollstat where xacts>0;select * from v$rollname;ADDR XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN STATUS<上一个:查看哪些表被锁和怎样解锁
下一个:oracle redo opcode