Oracle rowid and rdba Ding jieniu

The ROWID (also called restricted Rowid) below Oracle8 is: FFFF. BBBBBBBB. RRRR, which occupies 6 bytes (10 bitfile # + 22bit + 16 bit ).

The ROWID (also called restricted Rowid) below Oracle 8 is: FFFF. BBBBBBBB. RRRR, which occupies 6 bytes (10bit file # + 22bit + 16bit ).

The ROWID (also called restricted Rowid) below Oracle 8 is: FFFF. BBBBBBBB. RRRR occupies 6 bytes (10bit file # + 22bit + 16bit). However, to expand the data file, change the Rowid to OOOOOOFFFBBBBBBRRR, occupies 10 bytes (32bit + 10bit rfile # + 22bit + 16bit ). Here, O is the Object ID, F is the file ID, B is the block ID, and R is the row ID. Since the composition of rowid changes from file # To rfile #, the limit on the number of data files cannot exceed 1023 in the entire database, but cannot exceed 1023 data files in each tablespace.

Note: Here, O Represents data_object_id, which is an information related to the physical storage location of a segment, because a segment object can only be in one tablespace, data_object_id can uniquely confirm ts #, while data_object_id + rfile # can finally locate the specified physical data file of the rowid.

If we query the ROWID of a table, we can obtain the object information, file information, block information and row information. For example, based on the block information, you can know the exact number of blocks occupied by the table, the block on which each row is located, and the data file.

When selecting data without specifying the sorting field, oracle extracts data in the ascending order of rowid. For example

SQL> select t. id, rowid from skate. tab2 t;

ID ROWID

----------------------------

1067511 AAAO1lAAEAAAHKkAAA

1067513 AAAO1lAAEAAAHKkAAB

1067515 AAAO1lAAEAAAHKkAAC

1067517 AAAO1lAAEAAAHKkAAD

1067519 AAAO1lAAEAAAHKkAAE

1067523 AAAO1lAAEAAAHKkAAG

1067525 AAAO1lAAEAAAHKkAAH

1067527 AAAO1lAAEAAAHKkAAI

8 rows selected

SQL>

As shown in the preceding figure, the data is sorted in descending order by rowid. We will use rowid "AAAO1lAAEAAAHKkAAA" to describe it.

Data Object number = AAAO1l

File id = AAE

Block id = AAAHKk

Row = AAA

The Rowid is in 64-digit format and can be viewed by the Conversion Tool ()

A-Z <=> 0-25 (26)
A-z <=> 26-51 (26)
0-9 <=> 52-61 (10)
+/<=> 62-63 (2)

Take Data Object number = AAAO1l as an example,

L is 37 in 64-digit format and its position is 0.

37*(64 ^ 0) = 37

1 is the 53 in 64-digit format and the position is 1.

53*(64 ^ 1) = 3392

O is 14 in 64-digit format and the position is 2.

14*(64 ^ 2) = 57344

A is 0 in 64-digit format.

So

A * (64 ^ 3) = 0

A * (64 ^ 4) = 0

A * (64 ^ 5) = 0

AAAO1l = 0 + 0 + 0 + 57344 + 3392 + 37 = 60773 indicates the object existing in the row, and the corresponding object number is 60773.

Manual calculation is still troublesome. oracle provides the corresponding function dbms_rowid for implementation.

SQL> select dbms_rowid.rowid_object ('aaao1laaeaaahkkaaa ') data_object_id #,

2 dbms_rowid.rowid_relative_fno ('aaao1laaeaaahkkaa') rfile #,

3 dbms_rowid.rowid_block_number ('aaao1laaeaaahkkaaa ') block #,

4 dbms_rowid.rowid_row_number ('aaao1laaeaaahkkaaa ') row # from dual;

DATA_OBJECT_ID # RFILE # BLOCK # ROW #

---------------------------------------------

60773 4 29348 0

You can learn more through the package instructions or the oracle official manual, for example:

SQL> desc dbms_rowid

Element Type

-------------------------------

ROWID_TYPE_RESTRICTED CONSTANT

ROWID_TYPE_EXTENDED CONSTANT

ROWID_IS_VALID CONSTANT

ROWID_IS_INVALID CONSTANT

ROWID_OBJECT_UNDEFINED CONSTANT

ROWID_CONVERT_INTERNAL CONSTANT

ROWID_CONVERT_EXTERNAL CONSTANT

ROWID_INVALID EXCEPTION

ROWID_BAD_BLOCK EXCEPTION

ROWID_CREATE FUNCTION

ROWID_INFO PROCEDURE

ROWID_TYPE FUNCTION

ROWID_OBJECT FUNCTION

ROWID_RELATIVE_FNO FUNCTION

ROWID_BLOCK_NUMBER FUNCTION

ROWID_ROW_NUMBER FUNCTION

ROWID_TO_ABSOLUTE_FNO FUNCTION

ROWID_TO_EXTENDED FUNCTION

ROWID_TO_RESTRICTED FUNCTION

ROWID_VERIFY FUNCTION

SQL> desc dbms_rowid.rowid_info

Parameter Type Mode Default?

---------------------------------

ROWID_IN ROWID IN

ROWID_TYPE NUMBER OUT

OBJECT_NUMBER NUMBER OUT

RELATIVE_FNO NUMBER OUT

BLOCK_NUMBER NUMBER OUT

ROW_NUMBER NUMBER OUT

TS_TYPE_IN VARCHAR2 IN Y

If you understand the meaning of the preceding ROWID, it is easy to understand the block address rdba, that is, the FFFBBBBBB part of the ROWID, 10bit rfile # + 22bit. For example, we analyze a block address:

Rdba: 0x010072a4

Convert 0x010072a4 to 10 hexadecimal 16806564

SQL> select dbms_utility.data_block_address_file (16806564) "file ",

2 dbms_utility.data_block_address_block (16806564) "block"

3 from dual;

File block

--------------------

4 29348

SQL>

Now the dba of the block knows the file and block. How can we confirm that the push is correct? This block address is the content of the dump file. We can check it with the dump file.

Start dump data blocks tsn: 4 file #: 4 minblk 29348 maxblk 29348

Buffer tsn: 4 rdba: 0x010072a4 (4/29348)

Scn: 0x0000. 00e66a1e seq: 0x02 flg: 0x06 tail: 0x6a1e0602

Frmt: 0x02 chkval: 0x4590 type: 0x06 = trans data

Hex dump of block: st = 0, typ_found = 1

The result is the same as that of dump. However, here we get rfile #, which is the relative file number, and the relative file number cannot exceed 1023, if you want to dump data file Blocks Based on this address, you 'd better check v $ datafile:

Select file # from v $ datafile where rfile # = 4 and ts # = <: dbfile_in_ts>

Table space ts # can be obtained through the following SQL:

Select ta. tablespace_name, da. TS #
From dba_tablespaces ta, dba_data_files df, v $ datafile da
Where ta. tablespace_name = df. tablespace_name
And da. NAME = df. file_name

For example:

SQL> select file # from v $ datafile where rfile # = 4 and ts # = 4;

FILE #

----------

4

SQL>

If the number of data files is greater than 1023, file # Is not 4 here. It should be an absolute file number, which is the difference between file # And rfile. If dump data blocks, use the absolute file number, for example:

Alter system dump datafile 4 block 29348;

You can also use dba_segments, as shown in figure

SQL> select header_file, header_block from dba_segments where owner = 'skate' and segment_name = 'tab2 ';

HEADER_FILE HEADER_BLOCK

-----------------------

4 29347

Here, the field header occupies a block, and data is stored later. Therefore, the block for storing data should be 29348

----- End -----