[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是块地址.