Oracle Block scn/commit scn/cleanout scn说明(三)
XIDUSN NUMBER,
XIDSLOT NUMBER,
XIDSQN NUMBER
);
transaction_record transaction_record_type;
BEGIN
SELECT SYS_CONTEXT ('userenv', 'sid') INTO nsid FROM DUAL;
LOOP
INSERT INTO goon
SELECT *
FROMdba_objects
WHERE ROWNUM < 100;
SELECTXIDUSN,XIDSLOT,XIDSQN
INTOtransaction_record
FROMv$transaction a,v$session b
WHERE a.ADDR = b.TADDR AND b.SID = nsid;
IF ( transaction_record.XIDUSN=v_XIDUSN
ANDtransaction_record.XIDSLOT =v_XIDSLOT
ANDtransaction_record.XIDSQN >v_XIDSQN)
THEN
GOTOresue_end;
END IF;
COMMIT;
DELETE FROM goon;
SELECTXIDUSN,XIDSLOT,XIDSQN
INTOtransaction_record
FROMv$transaction a,v$session b
WHERE a.ADDR = b.TADDR AND b.SID = nsid;
IF ( transaction_record.XIDUSN=v_XIDUSN
AND transaction_record.XIDSLOT=v_XIDSLOT
ANDtransaction_record.XIDSQN >v_XIDSQN)
THEN
GOTOresue_end;
END IF;
COMMIT;
END LOOP;
<>
COMMIT;
END;
--调用脚本
SYS@dave2(db2)> execproc_go_break_reuse(16,18,5);
PL/SQL procedure successfully completed.
--产生延时块清除，记录相应scn号
SYS@dave2(db2)> select * from dbc;
ID
----------
8
9
--发生延时块清除时候的大概scn
SYS@dave2(db2)> selecttimestamp_to_scn(systimestamp) from dual;
TIMESTAMP_TO_SCN(SYSTIMESTAMP)
------------------------------
--最后一次itl 上的 commit scn
SYS@dave2(db2)> selectdbms_rowid.rowid_block_number(rowid) block,dbms_rowid.rowid_relative_fno(rowid) fileno, ora_rowscn from dbc;
BLOCK FILENO ORA_ROWSCN
---------- ---------- ----------
115346 1 2147771334
115346 1 2147771334
--dump undo header
--查看正在使用的回滚段
SYS@dave2(db2)> selectxidusn,xidslot,xidsqn,ubablk,ubafil,ubarec from v$transaction;
XIDUSN XIDSLOT XIDSQN UBABLK UBAFIL UBAREC
---------- ---------- ---------- -------------------- ----------
13 41 7 42 7 6
XIDUSN： rollback ID
UBABLK: datafile ID
但是这里我们要使用我们当时的undo块，即我们之前查询的XIDUSN=16
SYS@dave2(db2)> select usn,name fromv$rollname where usn=16;
USN NAME
---------- ------------------------------
16 _SYSSMU16$
alter system dump undo header '_SYSSMU13$';
SYS@dave2(db2)> alter system dump undoheader '_SYSSMU16$';
System altered.
SYS@dave2(db2)> oradebug setmypid
Statement processed.
SYS@dave2(db2)> oradebug tracefile_name
/u01/app/oracle/admin/dave2/udump/dave2_ora_11079.trc
TRNCTL:: seq: 0x0003 chd: 0x002a ctl: 0x0029 inc: 0x00000000 nfb: 0x0001
mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x01c0007a.0003.30 scn: 0x0000.800464f4 --control SCN
SYS@dave2(db2)> select to_number('800464f4','xxxxxxxxxxx')from dual;
TO_NUMBER('800464F4','XXXXXXXXXXX')
-----------------------------------
2147771636
Version: 0x01
FREE BLOCK POOL::
uba: 0x01c0007a.0003.30 ext: 0x2 spc: 0x572
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
uba: 0x00000000.