一个数据块包含一个数据块头、空闲空间、已用空间和可用空间，最后数据块的尾部包含一个校验码。
我们现在做一个演示来加深对数据块的理解：
SQL> create tablespace my_tbs datafile '/oracle/app/oracle/oradata/orcl/my_tbs.dbf' size 100m;
SQL> create table my_tab tablespace my_tbs as select * from dba_objects;
SQL> select header_file,header_block,bytes,blocks,extents from dba_segments where segment_name='MY_TAB';
HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS
----------- ------------ ---------- ---------- ----------
7 130 9437184 1152 24
header_block代表第130块包含了此段信息
BLOCKS (dba_segments) * DB_BLOCK_SIZE (parameter file) = BYTES (dba_segments)
896*1152=9437184
同样查询dba_extents会得到相同的信息：
SEGMENT_NAME EXTENT_ID BLOCK_ID BLOCKS BYTES
------------------------------ ---------- ---------- ---------- ----------
MY_TAB 0 128 8 65536
MY_TAB 1 136 8 65536
MY_TAB 2 144 8 65536
MY_TAB 3 152 8 65536
MY_TAB 4 160 8 65536
MY_TAB 5 168 8 65536
MY_TAB 6 176 8 65536
MY_TAB 7 184 8 65536
MY_TAB 8 192 8 65536
MY_TAB 9 200 8 65536
MY_TAB 10 208 8 65536
MY_TAB 11 216 8 65536
MY_TAB 12 224 8 65536
MY_TAB 13 232 8 65536
MY_TAB 14 240 8 65536
MY_TAB 15 248 8 65536
MY_TAB 16 256 128 1048576
MY_TAB 17 384 128 1048576
MY_TAB 18 512 128 1048576
MY_TAB 19 640 128 1048576
MY_TAB 20 768 128 1048576
MY_TAB 21 896 128 1048576
MY_TAB 22 1024 128 1048576
MY_TAB 23 1152 128 1048576
dump file默认是在udump目录下(user_dump_dest)，我们可以用TRACEFILE_IDENTIFIER设置自定义的trace文件
SQL> alter session set tracefile_identifier = db_block_dump;
SQL> alter system dump datafile 7 block 130;
SQL> select value from v$diag_info where name like 'De%';
VALUE
--------------------------------------------------------------------------------
/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_713_DB_BLOCK_DUMP.trc
$cat /oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_713_DB_BLOCK_DUMP.trc|more
*** 2015-06-13 15:55:07.741
*** SESSION ID:(1225.521) 2015-06-13 15:55:07.741
*** CLIENT ID:() 2015-06-13 15:55:07.741
*** SERVICE NAME:(SYS$USERS) 2015-06-13 15:55:07.741
*** MODULE NAME:(sqlplus@single (TNS V1-V3)) 2015-06-13 15:55:07.741
*** ACTION NAME:() 2015-06-13 15:55:07.741
Start dump data blocks tsn: 9 file#:7 minblk 130 maxblk 130
Block dump from cache:
Dump of buffer cache at level 4 for tsn=9 rdba=29360258
BH (0x117fe7378) file#: 7 rdba: 0x01c00082 (7/130) class: 4 ba: 0x117d66000
set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 140,28
dbwrid: 0 obj: 75949 objn: 75949 tsn: 9 afn: 7 hint: f
hash: [0x149269820,0x149269820] lru: [0x117fefe20,0x117fe63c0]
ckptq: [NULL] fileq: [NULL] objq: [0x117fe63e8,0x117fe6fc8] objaq: [0x635ea080
,0x117fe63f8]
st: XCURRENT md: NULL fpin: 'ktswh03: ktscts' tch: 6
flags: block_written_once redo_since_read
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]
Block dump from disk:
buffer tsn: 9 rdba: 0x01c00082 (7/130)
scn: 0x0000.0014de7f seq: 0x01 flg: 0x04 tail: 0xde7f2301
frmt: 0x02 chkval: 0x82f3 type: 0x23=PAGETABLE SEGMENT HEADER
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F89AA8E2200 to 0x00007F89AA8E4200
7F89AA8E2200 0000A223 01C00082 0014DE7F 04010000 [#...............]
7F89AA8E2210 000082F3 00000000 00000000 00000000 [................]
7F89AA8E2220 00000000 00000018 00000480 00000A9C [................]
7F89AA8E2230 00000017 00000041 00000080 01C004C1 [....A...........]
7F89AA8E2240 00000000 00000017 00000000 00000441 [............A...]
..................SKIP....................
................SKIP.......................
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 24 #blocks: 1152
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x01c004c1 ext#: 23 blk#: 65 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 1089
mapblk 0x00000000 offset: 23
Unlocked
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x01c00081
Last Level 1 BMB: 0x01c00481
Last Level II BMB: 0x01c00081
Last Level III BMB: 0x00000000
Map Header:: next 0x00000000 #extents: 24 obj#: 75949 flag: 0x10000000
Inc # 0
Extent Map
-------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------
Start dump data blocks tsn: 9 file#:7 minblk 130 maxblk 130
这句话的意思是此次dump的记录是从7号文件的130号块起到130号块为止
* rdba is the Data Block Address which is internal representation of the block address.
rdba是数据库内部用于表达数据块地址
0x01c00082=29360258=00000001110000000000000010000010
rdba的前10位是代表着文件号 7，后22位代表着块号 130，可以使用oracle提供的过程DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE和DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK来转换rdba，
SQL> select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(29360258) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(29360258)
----------------------------------------------
7
SQL> select DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(29360258) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(29360258)
-----------------------------------------------
130
可以使用DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS来转换文件号和块号为rdba
SQL> select DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS(7,130) from dual;
DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS(7,130)
-------------------------------------------
29360258
scn – is the SCN value of the block which defines when it was last modified.
scn是block中的一个值，用于定义块最后被变更的时间，如上所示scn: 0x0000.0014de7f，把scn转换为10进制为：
SQL> select to_number('0014de7f','xxxxxxxx') from dual;
TO_NUMBER('0014DE7F','XXXXXXXX')
--------------------------------
1367679
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1387575
tail: 0xde7f2301
tail用于跟踪数据块中的开始系统块和结束系统块的一致性
frmt: 0x02
这个代表的是这个数据块时oracle8或者以上版本的数据块，0×01是代表oracle7的数据块。
chkval: 0x82f3
这个用于oracle数据块校验数据块的一致性和有效性的，数据块读入buffer和写回数据文件之前都要做检查计算，如果计算值与数据块中记录的计算值不匹配就会标记该块是坏块.
type: 0x23=PAGETABLE SEGMENT HEADER
type用于表示块类型，块类型分类如下：
Decimal Hex Type
1 0x01 undo segment header
11 0x0b data file header
12 0x0c data segment header with FLG blocks
14 0x0e unlimited undo segment header
15 0x0f unlimited save undo segment header
16 0x10 unlimited data segment header
17 0x11 unlimited data segment header with FLG blocks
18 0x12 extent map block
23 0x17 bitmapped segment header
29 0x1d bitmapped file space header
32 0x20 first level bitmap block
33 0x21 second level bitmap block
34 0x22 third level bitmap block
35 0x23 Pagetable segment header block
36 0x24 Pagetable extent map block
37 0x25 System Managed Undo Extent Map Block
Extent Header:: spare1: 0 spare2: 0 #extents: 24 #blocks: 1152
这个部分显示区间的信息
Highwater:: 0x01c004c1 ext#: 23 blk#: 65 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 1089
mapblk 0x00000000 offset: 23
Unlocked
这个部分显示高水位的信息
高水位线在第23个区间，第65个块上，这个区间包含128个块，这个高水位线下有1089个块(高水位理解一下)
总块数是1152，而在高水位线一下是1089个块，索引free blocks为1152-1089=63个块，这63个块是在高水位之上的，也可以这样查看，目前高水位是在第65个块上，而此区间总计有128个块，即128-65=63个块是free block，而free space为8192*63=504Kb
Map Header:: next 0x00000000 #extents: 24 obj#: 75949 flag: 0x10000000
SQL> select obj#, owner#, name, status, ctime, type# from sys.obj$ where obj#=75949;
OBJ# OWNER# NAME STATUS CTIME TYPE#
---------- ---------- -------------------- ---------- ---------- ----------
75949 0 MY_TAB 1 13-6? -15 2
So the owner id is 0 (which is SYS user) and type is 2 (which means table)
SQL> select username from dba_users where user_id=0;
USERNAME------------------------------
SYS
以下语句显示用户下所拥有的对象及其类型：
select USERNAME,
count(decode(o.TYPE#, 2,o.OBJ#,'')) Tabs,
count(decode(o.TYPE#, 1,o.OBJ#,'')) Inds,
count(decode(o.TYPE#, 5,o.OBJ#,'')) Syns,
count(decode(o.TYPE#, 4,o.OBJ#,'')) Views,
count(decode(o.TYPE#, 6,o.OBJ#,'')) Seqs,
count(decode(o.TYPE#, 7,o.OBJ#,'')) Procs,
count(decode(o.TYPE#, 8,o.OBJ#,'')) Funcs,
count(decode(o.TYPE#, 9,o.OBJ#,'')) Pkgs,
count(decode(o.TYPE#,12,o.OBJ#,'')) Trigs,
count(decode(o.TYPE#,10,o.OBJ#,'')) Deps
from obj$ o,
dba_users u
where u.USER_ID = o.OWNER# (+)
group by USERNAME
order by USERNAME；
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30291162/viewspace-1718364/ ，如需转载，请注明出处，否则将追究法律责任。
