The secret of ROWID

The secret of ROWID table each data row has a row header, where The number of columns contained in The row data and The lock mark are stored. When a transaction updates a record, it will record the ITL Slot Number and lock mark used in the header of the Data row. The following describes the column length and column value. When Oracle stores different columns, there is no gap between each column, which is closely arranged. At the same time, there is no gap between different data rows, which is closely arranged. Because there is a structure in the header of the data block: row directory ). In the row directory, an entry is recorded for each data row, and each entry points to the row header of the record. Therefore, Oracle can distinguish different rows. Each record has a ROWID column, which is a fake. That is to say, the column value is not actually saved in the data block, but can be queried and displayed. For Oracle 10 Gb, The ROWID format is: oooooooofffbbbbbbrrr. OOOOOO indicates the object number of the Data row, FFF indicates the relative file number of the Data row, and BBBBBB indicates the data block number of the Data row, RRR indicates the row number in the data block. ROWID is represented in 64-bit notation, that is, ~ Z, ~ Z, 0 ~ 9,/, + the 64 characters indicate ROWID. That is, ~ Z ----> 0 ~ 25a ~ Z ----> 26 ~ 510 ~ 9 ----> 52 ~ 61/----> 62 + ----> 63 for example, let's look at the example below:

conn hr/hrSQL> create table student(student_id number,student_name varchar2(30)) tablespace users initrans 2 maxtrans 50;Table created. insert into student values(1,'watson');commit;SQL> select rowid ,student_id from student;ROWID              STUDENT_ID------------------ ----------AAACqLAAEAAAWsSAAA          1

 

We can see that the rowid of the inserted record is [AAACqL] [AAE] [AAAWsS] [AAA ]. We can perform the following calculation: AAACqL indicates the object number. A corresponds to 0, C corresponds to 2, q corresponds to 42, L corresponds to 11, so we have:

0 + 0 + 0 + 2 * power (10891) + 42 * power (10891) + 11 * power () = indicates that the student table corresponds to the object number. we can verify the object number: SQL> select object_id from user_objects where lower (object_name) = 'student '; OBJECT_ID ---------- 10891

 

AAE indicates the file number. E corresponds to 4. Therefore, 0 + 0 + 4 * power (64,0) = 4 indicates the file number of this record is 4. We can verify it using the dbms_rowid package:

SQL> select dbms_rowid.rowid_relative_fno('AAACqLAAEAAAWsSAAA') as file_no from dual;   FILE_NO----------         4

 

AAAWsS indicates the data block number. A corresponds to 0, W corresponds to 22. s corresponds to 44, S corresponds to 18, so we have: 0 + 0 + 0 + 22 * power (64,2) + 44 * power (64,1) + 18 * power (64,0) = 92946 indicates that the record is saved as 92946. we can also use the dbms_rowid package for verification:

SQL> select dbms_rowid.rowid_block_number('AAACqLAAEAAAWsSAAA') as block_no from dual;  BLOCK_NO----------     92946

 

AAA indicates the row number of the row. Therefore, 0 + 0 + 0 * power (64, 0) = 0 indicates that the row number of the record is 0, that is, the first record. We use the dbms_rowid package for verification:

SQL> select dbms_rowid.rowid_row_number('AAACqLAAEAAAWsSAAA') as row_no from dual;    ROW_NO----------         0