Oracle basic data type ------ (ROWID)

Oracle's ROWID can be divided into two types: Physical ROWID and logical ROWID. The index organization table uses the logical ROWID, and other types of tables use the physical ROWID.
 
SQL> create table test_rowid (id number, row_id rowid );
 
 
The table has been created.
 
 
SQL> insert into test_rowid values (1, null );
 
 
One row has been created.
 
 
SQL> update test_rowid set row_id = rowid where id = 1;
 
 
1 row updated.
 
 
SQL> commit;
 
 
Submitted.
 
 
SQL> select rowid, row_id from test_rowid;
 
 
ROWID ROW_ID
------------------------------------
AAABnRAAGAAAACWAAA
Oracle's physical extended ROWID has 18 bits, each of which uses 64-bit encoding, respectively using ~ Z, ~ Z, 0 ~ 9, +,/represents a total of 64 characters. A Indicates 0, B Indicates 1 ,...... Z indicates 25, a indicates 26 ,...... Z indicates, 0 indicates 52 ,......, 9 indicates 61, + indicates 62,/indicates 63.
ROWID can be divided into four parts.
1. The first six digits represent the data object number. After converting them into numbers, they correspond to dba_objects.Data_object_id.
In the above example, the data object number is AAABnR, and the conversion bit NUMBER is 1 × 64 × 64 + 39 × 64 + 17.
 
 
SQL> select owner, object_name from dba_objects
2 where data_object_id = 1*64*64 + 39*64 + 17;
 
 
OWNER OBJECT_NAME
-----------------------------------------------------------
YANGTK TEST_ROWID
The numbers 2.7, 8, and 9 represent the data file number of the data file in oracle.
SQL> select file_name, tablespace_name from dba_data_files where relative_fno = 6;
 
 
FILE_NAME TABLESPACE_NAME
------------------------------------------------------------
E: ORACLEORADATATESTYANGTK01.DBF YANGTK
3. BBBBBB: 10th to 15 bits indicate the BLOCK number of the record in the data file.
 
 
The above example is AAAACW. The conversion bit number is 2 × 64 + 22, indicating that the record contains 150th blocks in the data file.
4. RRR: the last three digits indicate the number of records in the BLOCK.
 
 
The above example is AAA, indicating 0th records (always counted from 0 ).
SQL> select row_id, dump (row_id, 16) dump_rowid from test_rowid;
 
 
ROW_ID DUMP_ROWID
-------------------------------------------------------------------
AAABnRAAGAAAACWAAA Typ = 69 Len = 10:, 19, d1
 
 
The first four digits indicate the first six digits of the ROWID, that is, the DATA_OBJECT_ID information. The data is saved as a numerical value.
 
 
SQL> select to_number ('19d1 ', 'xxxxxx') from dual;
 
 
TO_NUMBER ('19d1 ', 'xxxxxx ')
--------------------------
6609
 
 
SQL> select 1*64*64 + 39*64 + 17 from dual;
 
 
1*64*64 + 39*64 + 17
----------------
6609
 
 
There is a problem here. According to the value range of ROWID, the maximum value of OBJECT_DATA_ID is 64 to the power of 6, and according to DUMP, oracle only saves four digits, therefore, the value range is 4 to the power of 256.
 
 
SQL> set numwid 12
SQL> select power (64, 6), power (256, 4), power (64, 6)/power (256, 4) from dual;
 
 
POWER ()/POWER)
------------------------------------------------
68719476736 4294967296 16
 
 
It can be seen that the maximum value of OBJECT_DATA_ID is 4294967296. When this value is exceeded, duplicate occurs. (Of course, in reality, it is unlikely ).
SQL> select to_number ('96 ', 'xxx') from dual;
 
 
TO_NUMBER ('96 ', 'xxx ')
---------------------
150
 
 
SQL> select 2*64 + 22 from dual;
 
 
2*64 + 22
----------
150
 
 
Because the value of two data files is saved, and the minimum unit is 64, the number of data files that can be saved in ROWID is 1024, and more than 1024 will cause repeated ROWID.
 
 
SQL> select 256*256/64 from dual;
 
 
256*256/64
----------
1024
 
 
Because the value of the BLOCK shares these four digits with the data file, the maximum value of the BLOCK's 3rd bits should be smaller than 64, so as to ensure that the ROWID is not repeated. Therefore, the maximum BLOCK value should be 4194304.
 
 
SQL> select 64*256*256 from dual;
 
 
64x256*256
----------
4194304
 
 
The last two digits store the record values in the BLOCK. The maximum value of this value is 65536.
 
 
SQL> select 256*256 from dual;
 
 
256*256
----------
65536