[20150518]关于块转储问题2.txt

--我自己在学习oracle有时候使用块转储时,发现转储的内容跟我自己的想象不一样.
--正好前一阵子ITPUB有人也遇到类似的问题,自己做一个简单探究,参考链接如下:

http://blog.itpub.net/267265/viewspace-1655497/

-- 我前面提到块转储alter system dump datafile 4 block 1523;,仅仅从数据文件读取.无论在何种情况下.
-- 昨天看了相关文档,可以使用如下:

ALTER SESSION SET EVENTS 'immediate trace name buffer level n';

-- n为某个指定block的rdba，该命令可以转储某个block在buffer中的所有版本。

--自己做一个测试:

SCOTT@test> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.3.0     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SCOTT@test> select rowid,depty.* from depty ;
ROWID                  DEPTNO DNAME          LOC
------------------ ---------- -------------- -------------
AABKCeAAEAAAAXzAAA         10 ACCOUNTING     NEW YORK
AABKCeAAEAAAAXzAAB         20 RESEARCH       DALLAS1
AABKCeAAEAAAAXzAAC         30 SALES          CHICAGO
AABKCeAAEAAAAXzAAD         40 OPERATIONS     BOSTON
AABKCeAAEAAAAXzAAE         50 MARKETING      LONDON

SCOTT@test> @ lookup_rowid AABKCeAAEAAAAXzAAE
    OBJECT       FILE      BLOCK        ROW DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
    303262          4       1523          4 4,1523               alter system dump datafile 4 block 1523

BBED> set dba 4,1523
        DBA             0x010005f3 (16778739 4,1523)

$ cat convrdba.sql
select
TO_CHAR (dbms_utility.make_data_block_address(&1,&2), 'xxxxxxxxxxxxx') rdba16,
dbms_utility.make_data_block_address(&&1,&&2) rdba
from dual;

SCOTT@test> @convrdba.sql 4 1523
RDBA16               RDBA
-------------- ----------
       10005f3   16778739

--可以知道rdba地址是16778739.

SCOTT@test> update depty set loc=lower(loc) where deptno=50 ;
1 row updated.
--不提交.

SCOTT@test> ALTER SESSION SET EVENTS 'immediate trace name buffer level 16778739';
Session altered.

SCOTT@test> ALTER SESSION SET EVENTS 'immediate trace name buffer level 0x010005f3';
Session altered.

WARNING: set_tsn_p1 event has not been called.
Using stale value for tablespace number - 0
Dump of buffer cache at level 10 for tsn=0 rdba=16778739

*** 2015-05-18 10:34:51.437
WARNING: set_tsn_p1 event has not been called.
Using stale value for tablespace number - 0
Dump of buffer cache at level 10 for tsn=0 rdba=16778739

--tsn=0  不对啊,google看看相关文档.

SCOTT@test> select TS#,name from sys.ts$ where name='USERS';
       TS# NAME
---------- --------------------
         4 USERS

SCOTT@test> alter session set events 'immediate trace name set_tsn_p1 level 4';
Session altered.

SCOTT@test> ALTER SESSION SET EVENTS 'immediate trace name buffer level 0x010005f3';
Session altered.
--
*** 2015-05-18 10:40:19.911
Dump of buffer cache at level 10 for tsn=3 rdba=16778739
---tsn还给加1.

SCOTT@test> alter session set events 'immediate trace name set_tsn_p1 level 5';
Session altered.

SCOTT@test> ALTER SESSION SET EVENTS 'immediate trace name buffer level 0x010005f3';
Session altered.

        Repeat 493 times
099069F80 00000000 00000000 0203022C 4D0933C1  [........,....3.M]
099069F90 454B5241 474E4954 6E6F6C06 2C6E6F64  [ARKETING.london,]
099069FA0 C1020300 504F0A29 54415245 534E4F49  [....).OPERATIONS]
099069FB0 534F4206 2C4E4F54 C1020300 4153051F  [.BOSTON,......SA]
099069FC0 0753454C 43494843 2C4F4741 C1020300  [LES.CHICAGO,....]
099069FD0 45520815 52414553 44074843 414C4C41  [..RESEARCH.DALLA]
099069FE0 002C3153 0BC10203 4343410A 544E554F  [S1,......ACCOUNT]
099069FF0 08474E49 2057454E 4B524F59 D8E30601  [ING.NEW YORK....]
Block header dump:  0x010005f3
Object id on Block? Y
seg/obj: 0x4a09e  csc: 0x02.f479d8c4  itc: 3  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x10005f0 ver: 0x01 opc: 0
     inc: 0  exflg: 0

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0003.01c.00007e90  0x00c005e1.26ea.26  C---    0  scn 0x0002.f479d6b4
0x02   0x0003.016.00007e85  0x00c005e3.26ea.05  ----    1  fsc 0x0000.00000000
0x03   0x0006.01e.00008d29  0x00c03b78.272f.01  C---    0  scn 0x0002.f4739d4d
bdba: 0x010005f3
data_block_dump,data header at 0x9906807c
===============
tsiz: 0x1f80
hsiz: 0x1c
pbl: 0x9906807c
     76543210
flag=--------
ntab=1
nrow=5
frre=-1
fsbo=0x1c
fseo=0x1f0c
avsp=0x1ef0
tosp=0x1ef0
0xe:pti[0]  nrow=5  offs=0
0x12:pri[0] offs=0x1f66
0x14:pri[1] offs=0x1f4f
0x16:pri[2] offs=0x1f3b
0x18:pri[3] offs=0x1f23
0x1a:pri[4] offs=0x1f0c
block_row_dump:
tab 0, row 0, @0x1f66
tl: 26 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 2]  c1 0b
col  1: [10]  41 43 43 4f 55 4e 54 49 4e 47
col  2: [ 8]  4e 45 57 20 59 4f 52 4b
tab 0, row 1, @0x1f4f
tl: 23 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 2]  c1 15
col  1: [ 8]  52 45 53 45 41 52 43 48
col  2: [ 7]  44 41 4c 4c 41 53 31
tab 0, row 2, @0x1f3b
tl: 20 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 2]  c1 1f
col  1: [ 5]  53 41 4c 45 53
col  2: [ 7]  43 48 49 43 41 47 4f
tab 0, row 3, @0x1f23
tl: 24 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 2]  c1 29
col  1: [10]  4f 50 45 52 41 54 49 4f 4e 53
col  2: [ 6]  42 4f 53 54 4f 4e
tab 0, row 4, @0x1f0c
tl: 23 fb: --H-FL-- lb: 0x2  cc: 3
col  0: [ 2]  c1 33
col  1: [ 9]  4d 41 52 4b 45 54 49 4e 47
col  2: [ 6]  6c 6f 6e 64 6f 6e
end_of_block_dump

--如果要了解块在内存的情况,使用如下:

alter session set events 'immediate trace name set_tsn_p1 level m';
ALTER SESSION SET EVENTS 'immediate trace name buffer level rdba';

--m = ts#+1, rdba是块地址.