Oracle Block scn/commit scn/cleanout scn说明(二)
d_block_number(rowid),ora_rowscn from fcc;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)ORA_ROWSCN
----------------------------------------------
305906 7262675
305906 7262675
--刚才插入的2条记录都存放在block305906里，并且ora_rowscn也一样。
对fcc 表进行update：
SYS@anqing2(rac2)> update fcc set id=3where id=1;
1 row updated.
SYS@anqing2(rac2)> update fcc set id=4where id=2;
1 row updated.
SYS@anqing2(rac2)> selectdbms_rowid.rowid_block_number(rowid),ora_rowscn from fcc;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)ORA_ROWSCN
----------------------------------------------
305906 7262675
305906 7262675
--ora_rowscn 没有变化
SYS@anqing2(rac2)> commit;
Commit complete.
SYS@anqing2(rac2)> selectdbms_rowid.rowid_block_number(rowid),ora_rowscn from fcc;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)ORA_ROWSCN
----------------------------------------------
305906 7262794
305906 7262794
--ora_rowscn 已经改变，这个在ora_rowscn 那篇blog里有说明，这个ora_rowscn是从block header SCN 里读取的。
2.2 delayed block cleanout
--.创建一个小undo表空间.
SYS@dave2(db2)> create undo tablespaceundotbs2 datafile '/u01/app/oracle/oradata/dave2/undotbs02.dbf' size 1M;
Tablespace created.
SYS@dave2(db2)> alter system setundo_tablespace='undotbs2';
System altered.
--创建测试表并insertdata
SYS@dave2(db2)> create table dbc(idnumber);
Table created.
SYS@dave2(db2)> insert into dbcvalues(1);
1 row created.
SYS@dave2(db2)> insert into dbc values(2);
1 row created.
SYS@dave2(db2)> commit;
Commit complete.
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 2147768913
115346 1 2147768913
--update table
SYS@dave2(db2)> update dbc set id=8where id=1;
1 row updated.
SYS@dave2(db2)> update dbc set id=9where id=2;
1 row updated.
--得到XIDUSN，XIDSLOT的使用情况，后续发布重用脚本
SYS@dave2(db2)> selectxidusn,xidslot,xidsqn from v$transaction;
XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
16 18 5
--刷新buffer cache，如果出现事务递交前modified block就被flush回硬盘，那么将发生delayed block cleanout。
SYS@dave2(db2)> alter system flush buffer_cache;
System altered.
SQL> commit;
Commit complete
--大概的commit scn
SYS@dave2(db2)> selecttimestamp_to_scn(systimestamp) from dual;
TIMESTAMP_TO_SCN(SYSTIMESTAMP)
------------------------------
2147770572
--使用如下脚本，重用XIDUSN 16 XIDLOT 18
/* Formatted on 2011/8/4 15:47:15(QP5 v5.163.1008.3004) */
CREATE TABLE goon
AS
SELECT *
FROMdba_objects
WHERE 1 = 2;
/* Formatted on 2011/8/4 15:45:12(QP5 v5.163.1008.3004) */
CREATE OR REPLACE PROCEDUREproc_go_break_reuse (v_XIDUSN NUMBER,
v_XIDSLOT NUMBER,
v_XIDSQN NUMBER)
/* ————————————————–
Description:It’s used to maketransaction slot reused
—————————————————*/
AS
nsid NUMBER;
TYPEtransaction_record_type IS RECORD
(