Oracle get DBA and RDBA

最新推荐文章于 2024-09-13 16:46:56 发布

原创最新推荐文章于 2024-09-13 16:46:56 发布 · 1.4k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Oracle

Oracle 专栏收录该内容

165 篇文章

订阅专栏

本文介绍如何在 Oracle 数据库中进行 DBA 地址与文件号、块号之间的相互转换，并演示了如何通过 SQL 语句获取这些信息。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Get dba

[oracle@ggos ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Sep 25 16:32:13 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT rowid,
  2         dbms_rowid.rowid_relative_fno(rowid) rfno#,
  3         dbms_rowid.rowid_block_number(rowid) block#
  4  FROM zwc.ggtest;

ROWID                   RFNO#     BLOCK#
------------------ ---------- ----------
AAAVoiAAEAAAAIPAAA          4        527

SQL>

convert to dba address

SQL> variable dba varchar2(30)
SQL> exec :dba :=dbms_utility.make_data_block_address(4,527);

PL/SQL procedure successfully completed.

SQL> print dba

DBA
--------------------------------
16777743

SQL> 
SQL> select dbms_utility.MAKE_DATA_BLOCK_ADDRESS(4, 527) dba from dual;

       DBA
----------
  16777743

SQL>

convert dba address to file# block#

SQL> SELECT dbms_utility.data_block_address_file(16777743) FILE#,
  2         dbms_utility.data_block_address_block(16777743) BLOCK#
  3  FROM dual;

     FILE#     BLOCK#
---------- ----------
         4        527

SQL>

Get rdba

SQL> select rowid,
  2         dbms_rowid.rowid_to_absolute_fno(rowid, 'ZWC', 'GGTEST') fno,
  3         dbms_rowid.rowid_relative_fno(rowid) rfno#,
       dbms_rowid.rowid_block_number(rowid) block#,
  5         dbms_rowid.rowid_row_number(rowid) rowno#
  6    from ZWC.ggtest;

ROWID                     FNO      RFNO#     BLOCK#     ROWNO#
------------------ ---------- ---------- ---------- ----------
AAAVoiAAEAAAAIPAAA          4          4        527          0

SQL>

dump trace

SQL> oradebug setmypid
Statement processed.
SQL> alter system dump datafile 4 block 527;

System altered.

SQL> oradebug tracefile_name;
/u01/app/oracle/diag/rdbms/oraprod/oraprod/trace/oraprod_ora_30366.trc

Dump of buffer cache at level 4 for tsn=4 rdba=16777743
BH (0x85bd8718) file#: 4 rdba: 0x0100020f (4/527)class: 1 ba: 0x85842000

*** 2013-09-25 17:05:07.547
*** SESSION ID:(47.89) 2013-09-25 17:05:07.547
*** CLIENT ID:() 2013-09-25 17:05:07.547
*** SERVICE NAME:(SYS$USERS) 2013-09-25 17:05:07.547
*** MODULE NAME:(sqlplus@ggos (TNS V1-V3)) 2013-09-25 17:05:07.547
*** ACTION NAME:() 2013-09-25 17:05:07.547
 
Processing Oradebug command 'setmypid'


*** 2013-09-25 17:05:07.548
Oradebug command 'setmypid' console output: <none>


*** 2013-09-25 17:05:09.563
Start dump data blocks tsn: 4 file#:4 minblk 527 maxblk 527
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4 rdba=16777743
BH (0x85bd8718) file#: 4 rdba: 0x0100020f (4/527) class: 1 ba: 0x85842000
  set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 175,28
  dbwrid: 0 obj: 88610 objn: 88610 tsn: 4 afn: 4 hint: f
  hash: [0x8e10d330,0x8e10d330] lru: [0x85bd8940,0x85bd86d0]
  ckptq: [NULL] fileq: [NULL] objq: [0x85bd8968,0x74f0dd30] objaq: [0x85bd8978,0x74f0dd20]
  st: XCURRENT md: NULL fpin: 'kdswh01: kdstgr' tch: 1
  flags: only_sequential_access
  LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
Block dump from disk:
buffer tsn: 4 rdba: 0x0100020f (4/527)
scn: 0x0000.000fe6ca seq: 0x01 flg: 0x06 tail: 0xe6ca0601
frmt: 0x02 chkval: 0xd78c type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F72E42B0A00 to 0x00007F72E42B2A00
7F72E42B0A00 0000A206 0100020F 000FE6CA 06010000  [................]
7F72E42B0A10 0000D78C 00000001 00015A22 000FE6C9  [........"Z......]
7F72E42B0A20 00000000 00320002 01000208 001D0006  [......2.........]
7F72E42B0A30 0000045A 00C004B8 00130096 00008000  [Z...............]
7F72E42B0A40 000FE6BC 00210003 000003A2 00C00C06  [......!.........]
7F72E42B0A50 0019009B 00092001 000FE6CA 00000000  [..... ..........]
7F72E42B0A60 00000000 00020100 0016FFFF 1F5F1F5D  [............]._.]
7F72E42B0A70 00001F6A 1F800002 00001F5D 00000000  [j.......].......]
7F72E42B0A80 00000000 00000000 00000000 00000000  [................]
        Repeat 499 times
7F72E42B29C0 02023C00 0403C102 324C4F43 0202002C  [.<......COL2,...]
7F72E42B29D0 481103C1 685A2069 20676E6F 63696557  [...Hi Zhong Weic]
7F72E42B29E0 676E6568 0202002C 481102C1 685A2069  [heng,......Hi Zh]
7F72E42B29F0 20676E6F 63696557 676E6568 E6CA0601  [ong Weicheng....]
Block header dump:  0x0100020f
 Object id on Block? Y
 seg/obj: 0x15a22  csc: 0x00.fe6c9  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1000208 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0006.01d.0000045a  0x00c004b8.0096.13  C---    0  scn 0x0000.000fe6bc
0x02   0x0003.021.000003a2  0x00c00c06.009b.19  --U-    1  fsc 0x0009.000fe6ca
bdba: 0x0100020f
data_block_dump,data header at 0x7f72e42b0a64
===============
tsiz: 0x1f98
hsiz: 0x16
pbl: 0x7f72e42b0a64
     76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f5d
avsp=0x1f5f
tosp=0x1f6a
0xe:pti[0]      nrow=2  offs=0
0x12:pri[0]     offs=0x1f80
0x14:pri[1]     offs=0x1f5d
block_row_dump:
tab 0, row 0, @0x1f80
tl: 24 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c1 02
col  1: [17]  48 69 20 5a 68 6f 6e 67 20 57 65 69 63 68 65 6e 67
tab 0, row 1, @0x1f5d
tl: 2 fb: --HDFL-- lb: 0x2 
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 527 maxblk 527


*** 2013-09-25 17:05:11.268
Processing Oradebug command 'tracefile_name'


*** 2013-09-25 17:05:11.268
Oradebug command 'tracefile_name' console output:

SQL> select dbms_utility.MAKE_DATA_BLOCK_ADDRESS(4, 527) rdba_dec,
  2         to_char(dbms_utility.MAKE_DATA_BLOCK_ADDRESS(4, 527), 'xxxxxxxx') rdba_hex
  3  from dual;

  RDBA_DEC RDBA_HEX
---------- ---------
  16777743   100020f

SQL>

SQL> SELECT DBMS_UTILITY.data_block_address_file(TO_NUMBER(LTRIM('0x0100020f',
  2                                                              '0x'),
  3                                                        'xxxxxxxx')) file#,
  4         DBMS_UTILITY.data_block_address_block(TO_NUMBER(LTRIM('0x0100020f',
  5                                                               '0x'),
  6                                                         'xxxxxxxx')) block#
  7    FROM DUAL;

     FILE#     BLOCK#
---------- ----------
         4        527

SQL>