select * from location;
LOC NAME STATE
--- ------------------------------ ------------------------------
CHI Chicago Illinois
NY New York New York
LV Las Vegas Nevada
DET Detroit Michigan
Using the dbms_rowid package we can locate the block that holds the row of
LOCATIONID = 'CHI'
select rowid,locationid,
dbms_rowid.rowid_relative_fno(rowid) fileno,
dbms_rowid.rowid_block_number(rowid) blockno,
dbms_rowid.rowid_row_number(rowid) rowno,
rownum
from location
where locationid = 'CHI';
ROWID LOC FILENO BLOCKNO ROWNO ROWNUM
------------------ --- ---------- ---------- ---------- ----------
AAAa4VAAEAADxa+AAA CHI 4 988862 0 1
1 row selected.
Now we can dump the contents of this block to udump dest.
alter system dump datafile 4 block 988862;
System altered.
Open the trace file in a editor shows
First Section:
scn = block last change system change number
rdba = data block address constituted by a datafile number and a data block number
file# = datafile number
minblk / maxblk = number of blocks dumped
Start dump data blocks tsn: 4 file#:4 minblk 988862 maxblk 988862
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4, rdba=17766078
BH (0x72fb05f8) file#: 4 rdba: 0x010f16be (4/988862) class: 1 ba: 0x727d6000
set: 5 pool 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,25
dbwrid: 0 obj: 110101 objn: 110101 tsn: 4 afn: 4 hint: f
hash: [0xdda52128,0xdda52128] lru: [0x8cfb1190,0x62fc43f0]
ckptq: [NULL] fileq: [NULL] objq: [0x8cfb11b8,0xd7a18158]
st: XCURRENT md: NULL tch: 2
flags: only_sequential_access
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
cr pin refcnt: 0 sh pin refcnt: 0
Block dump from disk:
buffer tsn: 4 rdba: 0x010f16be (4/988862)
scn: 0x0000.03d68a0c seq: 0x01 flg: 0x06 tail: 0x8a0c0601
frmt: 0x02 chkval: 0x9f43 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Block Header Dump section:
itc = number of itl's
Block header dump: 0x010f16be
Object id on Block? Y
seg/obj: 0x1ae15 csc: 0x00.3d68810 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x10f16b8 ver: 0x01 opc: 0
inc: 0 exflg: 0
ITL section:
Shows the transaction slots and Interested Transaction list.
xid = transaction id
uba = Undo block address
Flag = ---- = uncommitted,
C--- = Committed,
--U- = Commited +delayed clean out has not occured
Lck = rows in the flag state
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x001e.012.00001c1c 0x00c4a237.1336.0b --U- 4 fsc 0x0000.03d68a0c
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x010f16be
Data Header section:
ntab = Number of tables, usually 1 cluster tables might have more
nrows = Number of rows in block
frre = First free row index entry
avsp = Available block space
tosp = Space available after commit
data_block_dump,data header at 0x2ab8bb3e2a64
===============
tsiz: 0x1f98
hsiz: 0x1a
pbl: 0x2ab8bb3e2a64
76543210
flag=--------
ntab=1
nrow=4
frre=-1
fsbo=0x1a
fseo=0x1f39
avsp=0x1f1f
tosp=0x1f1f
0xe:pti[0] nrow=4 offs=0
0x12:pri[0] offs=0x1f80
0x14:pri[1] offs=0x1f68
0x16:pri[2] offs=0x1f51
0x18:pri[3] offs=0x1f39
Row data Section:
tab 0 = Only 1 table in block (location table)
row # = Row number
col # = Column value in Hex
@hex = Offset from header
tl = Total bytes of row plus the header
lb = lock byte
cc = columns
block_row_dump:
tab 0, row 0, @0x1f80
tl: 24 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 3] 43 48 49
col 1: [ 7] 43 68 69 63 61 67 6f
col 2: [ 8] 49 6c 6c 69 6e 6f 69 73
tab 0, row 1, @0x1f68
tl: 24 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] 4e 59
col 1: [ 8] 4e 65 77 20 59 6f 72 6b
col 2: [ 8] 4e 65 77 20 59 6f 72 6b
tab 0, row 2, @0x1f51
tl: 23 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] 4c 56
col 1: [ 9] 4c 61 73 20 56 65 67 61 73
col 2: [ 6] 4e 65 76 61 64 61
tab 0, row 3, @0x1f39
tl: 24 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 3] 44 45 54
col 1: [ 7] 44 65 74 72 6f 69 74
col 2: [ 8] 4d 69 63 68 69 67 61 6e
end_of_block_dump
The ROWNO for LOCATIONID = 'CHI' is 0
LOCATIONID is the first column (col 0)
All column data types are varchar2
Converting the following from HEX to CHAR
tab 0, row 0, @0x1f80
tl: 24 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 3] 43 48 49 -- C H I
col 1: [ 7] 43 68 69 63 61 67 6f -- C h i c a g o
col 2: [ 8] 49 6c 6c 69 6e 6f 69 73 -- I l l i n o i s
******************************************
keywords: alter dump block
******************************************
rdbms version: 11g
******************************************
No comments:
Post a Comment