Oracle rdba和 dba 说明

一. DB（Data block） A data block is the smallest unit of storage in an Oracle database. Every database has a default block size （specified when the database is created）, although blocks in different tablespaces may have different block sizes. An extent consist of one or more contiguous Oracle data blocks. A block determines the finest level of granularity of where data can be stored. One data block corresponds to a specific number of bytes of physical space on disk. Information about data blocks can be retrieved from the data dictionary views USER_SEGMENTS and USER_EXTENTS. These views show how many blocks are allocated for database object and how many blocks are available（free） in a segment/extent. 1.1 Dumping data blocksStart by getting the file and block number to dump. Example:SQL> SELECT 2 dbms_rowid.rowid_relative_fno（rowid） REL_FNO, 3 dbms_rowid.rowid_block_number（rowid） BLOCKNO, 4 dbms_rowid.rowid_row_number（rowid） ROWNO, 5 empno, ename 6 FROM emp WHERE empno = 7369; REL_FNO BLOCKNO ROWNO EMPNO ENAME---------- ---------- ---------- ---------- ---------- 4 20 0 7369 SMITHDump the block:SQL> alter system dump datafile 4 block 20;System altered. Look for the newly created dump file in your UDUMP directory. -- dump 多个blocksUse the following syntax to dump multiple blocks:ALTER SYSTEM dump datafile <file_id> block min <block_id> block max <block_id+blocks-1>; 1.2 Analyzing data block dumpsFrom the above block dump:block_row_dump:tab 0, row 0, @0x1d49tl: 38 fb: --H-FL-- lb: 0x0 cc: 8col 0: [ 3] c2 4a 46col 1: [ 5] 53 4d 49 54 48col 2: [ 5] 43 4c 45 52 4bcol 3: [ 3] c2 50 03col 4: [ 7] 77 b4 0c 11 01 01 01col 5: [ 2] c2 09col 6: *NULL*col 7: [ 2] c1 15 Converting back to table values:Col 0 （EMPNO）SQL> SELECT utl_raw.cast_to_number（replace（""c2 4a 46"","" ""）） value FROM dual;VALUE---------- 7369Col 2 （ENAME） - simply convert the hex values to ascii - 53 4d 49 54 48 -> SMITH. Alternatively:SQL> SELECT utl_raw.cast_to_varchar2（replace（""53 4d 49 54 48"","" ""）） value FROM dual;VALUE---------SMITH 二. DBA（Data Block Address） A Data Block Address （DBA） is the address of an Oracle data block for access purposes. DBA一般指绝对数据块地址. rowid用来表示一行的物理地址，一行唯一确定一个rowid，并且在使用中一般不会改变，除非rowid之后在行的物理位置发生改变的情况下才会发生变化。在rowid 中，就有一段是来表示DBA的。有关rowid的内容，参考我的Blog： Oracle Rowid 介绍 http://www.linuxidc.com/Linux/2011-07/37998.htm 2.1 Find the DBA for a given rowStart by getting the file and block number of the row. Example:SQL> SELECT 2 dbms_rowid.rowid_relative_fno（rowid） REL_FNO, 3 dbms_rowid.rowid_block_number（rowid） BLOCKNO, 4 empno, ename 5 FROM emp WHERE empno = 7369; REL_FNO BLOCKNO EMPNO ENAME---------- ---------- ---------- ---------- 4 20 7369 SMITH 2.2 convert the file and block numbers to a DBA address:SQL> variable dba varchar2（30）SQL> exec :dba := dbms_utility.make_data_block_address（4, 20）;PL/SQL procedure successfully completed.SQL> print dbaDBA--------------------------------16777236 2.3 Convert a DBA back to file and block numbersExample:SQL> SELECT dbms_utility.data_block_address_block（16777236） "BLOCK", 2 dbms_utility.data_block_address_file（16777236） "FILE" 3 FROM dual; BLOCK FILE---------- ---------- 20 4 三. RDBA（Tablespace relative database block address）在讲RDBA 之前，要先了解下rowid的组成。关于rowid的内容，参考我的blog： Oracle Rowid 介绍 http://blog.csdn.net/tianlesoftware/archive/2009/12/16/5020718.aspx RDBA是相对数据块地址，是数据字典（表空间及一些对象定义）所在块的地址。 oracle 8以后，rowid的存储空间扩大到了10个字节（32bit object#+10bit rfile#+22bit block#+16bit row#）。rdba就是rowid中的rfile#+block#。 SYS@anqing1（rac1）> SELECTrowid,dbms_rowid.rowid_relative_fno（rowid） REL_FNO,dbms_rowid.rowid_block_number（rowid） BLOCKNO,dbms_rowid.rowid_row_number（rowid） ROWNO,empno, enameFROM scott.emp WHERE empno = 7521; ROWID REL_FNO BLOCKNO ROWNO EMPNO ENAME------------------ ---------- ---------- ---------- ---------- ----------AAAMfMAAEAAAAAgAAA 4 32 0 7369 SMITH rowid = AAAMfMAAEAAAAAgAAABlockNo= 4rowno =0 把这个block dump到trace:SYS@anqing1（rac1）> alter system dump datafile 4 block 32;System altered. 查看当前的trace 文件位置：SYS@anqing1（rac1）> oradebug setmypid;Statement processed.SYS@anqing1（rac1）> oradebug tracefile_name/u01/app/oracle/admin/anqing/udump/anqing1_ora_19997.trc 查看trace file：[oracle@rac1 ~]$ cat /u01/app/oracle/admin/anqing/udump/anqing1_ora_19997.trc *** 2011-06-07 11:02:30.023Start dump data blocks tsn: 4 file#: 4 minblk 32 maxblk 32buffer tsn: 4 rdba: 0x01000020 （4/32） -- rdba 的值scn: 0x0000.0006bfdb seq: 0x10 flg: 0x06 tail: 0xbfdb0610frmt: 0x02 chkval: 0x26a0 type: 0x06=trans dataHex dump of block: st=0, typ_found=1Dump of memory from 0x0ED09400 to 0x0ED0B400ED09400 0000A206 01000020 0006BFDB 06100000 [.... ...........]ED09410 000026A0 00180001 0000C7CC 0006BFD9 [.&..............].....ED094A0 00000000 00000000 00000000 00000000 [................] Repeat 465 timesED0B1C0 00000000 08012C00 2350C203 4C494D06 [.....,....P#.MIL]ED0B1D0 0552454C 52454C43 4EC2034B B6770753 [LER.CLERK..NS.w.]....ED0B3E0 05485449 52454C43 50C2034B B4770703 [ITH.CLERK..P..w.]ED0B3F0 0101110C 09C20201 15C102FF BFDB0610 [................]Block header dump: 0x01000020 Object id on Block？ Y seg/obj: 0xc7cc csc: 0x00.6bfd9 itc: 2 flg: E typ: 1 - DATA brn: 0 bdba: 0x1000019 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/Fsc0x01 0x0003.011.000000f2 0x00805794.00c8.49 --U- 14 fsc 0x0000.0006bfdb0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 data_block_dump,data header at 0xed09464===============tsiz: 0x1f98hsiz: 0x2epbl: 0x0ed09464bdba: 0x01000020 76543210flag=--------ntab=1nrow=14frre=-1fsbo=0x2efseo=0x1d61avsp=0x1d33tosp=0x1d330xe:pti[0] nrow=14 offs=0 -- 该块中保存了14条记录。从row 0到row 130x12:pri[0] offs=0x1f720x14:pri[1] offs=0x1f470x16:pri[2] offs=0x1f1c0x18:pri[3] offs=0x1ef30x1a:pri[4] offs=0x1ec60x1c:pri[5] offs=0x1e9d0x1e:pri[6] offs=0x1e740x20:pri[7] offs=0x1e4c0x22:pri[8] offs=0x1e260x24:pri[9] offs=0x1dfb0x26:pri[10] offs=0x1dd50x28:pri[11] offs=0x1daf0x2a:pri[12] offs=0x1d880x2c:pri[13] offs=0x1d61block_row_dump:tab 0, row 0, @0x1f72tl: 38 fb: --H-FL-- lb: 0x1 cc: 8col 0: [ 3] c2 4a 46col 1: [ 5] 53 4d 49 54 48col 2: [ 5] 43 4c 45 52 4bcol 3: [ 3] c2 50 03col 4: [ 7] 77 b4 0c 11 01 01 01col 5: [ 2] c2 09col 6: *NULL*col 7: [ 2] c1 15tab 0, row 1, @0x1f47tl: 43 fb: --H-FL-- lb: 0x1 cc: 8col 0: [ 3] c2 4b 64col 1: [ 5] 41 4c 4c 45 4ecol 2: [ 8] 53 41 4c 45 53 4d 41 4ecol 3: [ 3] c2 4d 63col 4: [ 7] 77 b5 02 14 01 01 01col 5: [ 2] c2 11col 6: [ 2] c2 04col 7: [ 2] c1 1ftab 0, row 2, @0x1f1ctl: 43 fb: --H-FL-- lb: 0x1 cc: 8col 0: [ 3] c2 4c 16col 1: [ 4] 57 41 52 44col 2: [ 8] 53 41 4c 45 53 4d 41 4ecol 3: [ 3] c2 4d 63col 4: [ 7] 77 b5 02 16 01 01 01col 5: [ 3] c2 0d 33col 6: [ 2] c2 06col 7: [ 2] c1 1ftab 0, row 3, @0x1ef3...tab 0, row 13, @0x1d61 tl: 39 fb: --H-FL-- lb: 0x1 cc: 8col 0: [ 3] c2 50 23col 1: [ 6] 4d 49 4c 4c 45 52col 2: [ 5] 43 4c 45 52 4bcol 3: [ 3] c2 4e 53col 4: [ 7] 77 b6 01 17 01 01 01col 5: [ 2] c2 0ecol 6: *NULL*col 7: [ 2] c1 0bend_of_block_dumpEnd dump data blocks tsn: 4 file#: 4 minblk 32 maxblk 32[oracle@rac1 ~]$ /* Formatted on 2011/6/7 11:27:10 （QP5 v5.163.1008.3004） */SELECT DBMS_UTILITY.data_block_address_file （ TO_NUMBER （LTRIM （""0x01000020"", ""0x""）, ""xxxxxxxx""）） AS file_no, DBMS_UTILITY.data_block_address_block （ TO_NUMBER （LTRIM （""0x01000020"", ""0x""）, ""xxxxxxxx""）） AS block_no FROM DUAL; FILE_NO BLOCK_NO---------- ---------- 4 32 这个和我们之前在rowid里查看的一致。刚才说了，在32这个块里保存了14条row记录，我们继续查询一下我们where=7521 那条： tab 0, row 13, @0x1d61SYS@anqing1（rac1）> select DBMS_UTILITY.data_block_address_block （TO_NUMBER （LTRIM （""0x1d61"", ""0x""）,""xxxxxxxx""）） as block_no from dual; BLOCK_NO---------- 7521 我们查询的那条row记录在最后一条。Oracle Rowid 介绍Oracle 数据块 Block 说明相关资讯 Oracle教程

Oracle中纯数字的varchar2类型和（07/29/2015 07:20:43）
Oracle教程：Oracle中查看DBLink密（07/29/2015 07:16:55）
[Oracle] SQL*Loader 详细使用教程（08/11/2013 21:30:36）

Oracle教程：Oracle中kill死锁进程（07/29/2015 07:18:28）
Oracle教程：ORA-25153 临时表空间（07/29/2015 07:13:37）
Oracle教程之管理安全和资源（04/08/2013 11:39:32）

本文评论查看全部评论（0）