简单了解数据在Oracle文件中的存储
2010-02-13
目的：
1、 了解数据块转储
2、 简单认证数据在ORACLE文件的存储
测试环境：
VM+Win2003+Oracle11g
DB_BLOCK_SIZE 8k
C:\Documents and Settings\Administrator>sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on 星期一 2月 13 21:03:38 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
SQL> conn / as sysdba
已连接。
SQL> create tablespace test1 datafile 'E:\app\Data\orcl\test1' size 20M ;
表空间已创建。
SQL> Create user firefox identified by qwe123 temporary tablespace temp default tablespace test1 quota unlimited on test1;
用户已创建。
SQL> select extent_management,allocation_type,segment_space_management from dba_tablespaces where tablespace_name='TEST1';
EXTENT_MAN ALLOCATIO SEGMEN
---------- --------- ------
LOCAL SYSTEM AUTO
SQL> grant create session to firefox;
授权成功。
SQL> grant Create table to firefox;
授权成功。
SQL> grant insert any table to firefox;
授权成功。
SQL> conn firefox/qwe123
已连接。
SQL> create table firefoxTest1(id number(5) not null ,testVchar varchar2(20),testChar char(2));
表已创建。
SQL> insert into firefoxTest1(id,testVchar,testchar) values (1,'我是一个中国人！','Do');
已创建 1 行。
SQL> commit;
提交完成。
SQL> conn / as sysdba
已连接。
SQL> select owner,segment_name,tablespace_name,file_id,extent_id,blocks from dba_extents where segment_type='TABLE' and segment_name='FIREFOXTEST1';
OWNER SEGMENT_NAME TABLESPACE_NAME FILE_ID EXTENT_ID BLOCK_ID BLOCKS
--------------- -------------------- ------------------------------ ---------- ---------- ---------- ----------
FIREFOX FIREFOXTEST1 TEST1 6 0 128 8
新建表只有一条记录，故从上面的查询可以确认，数据保存在128的Block到135 Block中的一个块中。
SQL> alter system dump datafile 6 block min 128 block max 135;
系统已更改。
Start dump data blocks tsn: 6 file#:6 minblk 128 maxblk 135
Block dump from cache:
Dump of buffer cache at level 4 for tsn=6, rdba=25165952
BH (0x353E7CCC) file#: 6 rdba: 0x01800080 (6/128) class: 8 ba: 0x350E2000
set: 3 pool 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 407,28
dbwrid: 0 obj: 73149 objn: 73149 tsn: 6 afn: 6 hint: f
hash: [0x44374E78,0x44374E78] lru: [0x353E7CA4,0x353E7E4C]
ckptq: [NULL] fileq: [NULL] objq: [0x41AA9154,0x353E7CBC]
st: XCURRENT md: NULL tch: 1
flags: block_written_once redo_since_read
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]
cr pin refcnt: 0 sh pin refcnt: 0
Block dump from disk:
……..
Block dump from cache:
Dump of buffer cache at level 4 for tsn=6, rdba=25165955
BH (0x353E836C) file#: 6 rdba: 0x01800083 (6/131) class: 1 ba: 0x350F2000
set: 3 pool 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 407,28
dbwrid: 0 obj: 73149 objn: 73149 tsn: 6 afn: 6 hint: f
hash: [0x3A7F1258,0x443C6D08] lru: [0x353E8344,0x353E84EC]
ckptq: [NULL] fileq: [NULL] objq: [0x353E7BE8,0x353E8504]
st: XCURRENT md: NULL tch: 3
flags: block_written_once redo_since_read
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]
cr pin refcnt: 0 sh pin refcnt: 0
Block dump from disk:
buffer tsn: 6 rdba: 0x01800083 (6/131)
scn: 0x0000.000d1ac2 seq: 0x01 flg: 0x06 tail: 0x1ac20601
frmt: 0x02 chkval: 0x9ccc type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0DA52200 to 0x0DA54200
DA52200 0000A206 01800083 000D1AC2 06010000 [................]
DA52210 00009CCC 00000001 00011DBD 000D1AC0 [................]
DA52220 00000000 0032F802 01800080 00030003 [......2.........]
DA52230 00000310 00C001D3 00190119 00002001 [............. ..]
DA52240 000D1AC2 00000000 00000000 00000000 [................]
DA52250 00000000 00000000 00000000 00000000 [................]
DA52260 00000000 00010100 0014FFFF 1F6A1F7E [............~.j.]
DA52270 00001F6A 1F7E0001 00000000 00000000 [j.....~.........]
DA52280 00000000 00000000 00000000 00000000 [................]
Repeat 501 times
DA541E0 012C0000 02C10203 CAD2CE10 B8BBD2C7 [..,.............]
DA541F0 B9D0D6F6 A3CBC8FA 6F4402A1 1AC20601 [..........Do....]
Block header dump: 0x01800083
Object id on Block? Y
seg/obj: 0x11dbd csc: 0x00.d1ac0 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1800080 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0003.003.00000310 0x00c001d3.0119.19 --U- 1 fsc 0x0000.000d1ac2
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x01800083
data_block_dump,data header at 0xda52264
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x0da52264
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f7e
avsp=0x1f6a
tosp=0x1f6a
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f7e
block_row_dump:
tab 0, row 0, @0x1f7e
tl: 26 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] c1 02
col 1: [16] ce d2 ca c7 d2 bb b8 f6 d6 d0 b9 fa c8 cb a3 a1
col 2: [ 2] 44 6f
end_of_block_dump
当然可以通过以下的也可以简便快速的导出。
SQL> select dbms_rowid.rowid_relative_fno(rowid) file_id,dbms_rowid.rowid_block_number(rowid) block_id from firefox.firefoxtest1;
FILE_ID BLOCK_ID
---------- ----------
6 131
SQL> alter system dump datafile 6 block 131;
系统已更改。
计算 131*8K ====> 1073152=0x106000
76+(itc-1)*24=100 ====> 64
0x1f7e+64 =0x1fe2
0x106000+0x1fe2=0x107FE2
验证数据是否在Test1.dbf那个位置。
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL>
用文件打开该数据文件，数据确实保存在该位置。
SQL> create tablespace testmanual datafile 'E:\app\Data\orcl\testmanual' size 20M segment space management manual;
表空间已创建。
SQL> alter user firefox default tablespace testmanual;
用户已更改。
SQL> alter user firefox quota unlimited on testmanual;
用户已更改。
SQL> conn firefox/qwe123
已连接。
SQL> create table testM(id number(2),testmanaul varchar2(20));
表已创建。
SQL> insert into testm(id,testmanaul) values (1,'上海');
已创建 1 行。
SQL> commit;
提交完成。
SQL> select dbms_rowid.rowid_relative_fno(rowid) file_id,dbms_rowid.rowid_block_number(rowid) block_id from TestM;
FILE_ID BLOCK_ID
---------- ----------
7 129
SQL> alter system dump datafile 7 block 129;
alter system dump datafile 7 block 129
*
第 1 行出现错误:
ORA-01031: 权限不足
SQL> conn / as sysdba
已连接。
SQL> alter system dump datafile 7 block 129;
系统已更改。
*** 2012-02-13 22:34:47.472
Start dump data blocks tsn: 7 file#:7 minblk 129 maxblk 129
Block dump from cache:
Dump of buffer cache at level 4 for tsn=7, rdba=29360257
BH (0x37FE3C34) file#: 7 rdba: 0x01c00081 (7/129) class: 1 ba: 0x37C46000
set: 3 pool 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 242,28
dbwrid: 0 obj: 73157 objn: 73157 tsn: 7 afn: 7 hint: f
hash: [0x363E639C,0x43BB0458] lru: [0x37FE3F5C,0x37FE3C0C]
ckptq: [NULL] fileq: [NULL] objq: [0x37FE42C4,0x4123CD4C]
st: XCURRENT md: NULL tch: 2
flags: block_written_once redo_since_read
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]
cr pin refcnt: 0 sh pin refcnt: 0
Block dump from disk:
buffer tsn: 7 rdba: 0x01c00081 (7/129)
scn: 0x0000.000d343e seq: 0x05 flg: 0x06 tail: 0x343e0605
frmt: 0x02 chkval: 0xb60b type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x08982200 to 0x08984200
8982200 0000A206 01C00081 000D343E 06050000 [........>4......]
8982210 0000B60B 00000001 00011DC5 000D3438 [............84..]
8982220 00000000 00030002 00000000 00000002 [................]
掠过………….
8984170 00100000 00740000 00120002 0025000B [......t.......%.]
8984180 C676FEBF 823F0021 0003002D 009D0000 [..v.!.?.-.......]
8984190 0021007D 00960006 7937001C 00A40105 [}.!.......7y....]
89841D0 0021DC40 006C0400 02B00089 32041A67 [@.!...l.....g..2]
89841E0 00284A04 00000007 00000017 00210010 [.J(...........!.]
89841F0 02012C40 0402C102 A3BACFC9 343E0605 [@,............>4]
Block header dump: 0x01c00081
Object id on Block? Y
seg/obj: 0x11dc5 csc: 0x00.d3438 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0002.000.00000320 0x00c0022c.0083.1f --U- 1 fsc 0x0000.000d343e
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x01c00081
data_block_dump,data header at 0x898225c
===============
tsiz: 0x1fa0
hsiz: 0x14
pbl: 0x0898225c
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f95
avsp=0x1f81
tosp=0x1f81
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f95
block_row_dump:
tab 0, row 0, @0x1f95
tl: 11 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02
col 1: [ 4] c9 cf ba a3
end_of_block_dump
End dump data blocks tsn: 7 file#: 7 minblk 129 maxblk 129
SQL> select extent_management,segment_space_management, allocation_type from dba_tablespaces where tablespace_name='TESTMANUAL';
EXTENT_MAN SEGMEN ALLOCATIO
---------- ------ ---------
LOCAL MANUAL SYSTEM
计算 129*8K ====> 1056768=0x102000
MSSM:20+48+(itc-1)*24=92 ====> 5C
0x1f95+5c =0x1ff1
0x102000+0x1ff1=0x103FF1
了解MSSM和ASSM block内部offset的base的计算方法区别，请查阅Eygle博客日志http://www.eygle.com/rss/20100209.html
