Knowledge about Oracle rowid

Rowid structure of oracle8
PM

From: Linux documentation
Address: http://linux.sheup.com/linux/linux3983.htm

1. Why is rowid used?
Oracle uses rowid as the unique identifier of the B-tree and its internal algorithm to mark the row.
In versions earlier than oracle8, rowid marks file, block, and row number, and uses only one numeric generation.
Table file number.
In oracle8, a datafile has two numbers:
An absolute value is unique throughout the database. You can see the file_id in dba_data_files.
A relative value, which is unique in tablespace, can be viewed in relative _
Fno.

The new rowid uses relative values. Therefore, you must store the ID of the segment. Otherwise, it will be confused. So Orac
Le8 adds the object's segment number to rowid to indicate table or partition.

2. rowid Structure
Use base-64 code, including a-Z, A-Z, 0-9, + ,-. A total of 18 digits.
1-6 digits: representing the object
7-9 bits: relative file value
10-15: block in the file
16-18: slot value in the block

3. tablespace-relative addressing
Tablespace-relative addressing is used. Multiple files can have the same relative value because of its
They belong to different tablespaces, so we cannot get an absolute address from the new rowid, but this is no problem.
When processing an object, you can determine which tablesapce it belongs. In tables
In pace, the object relative value is unique, so rowid can uniquely identify an object. Table
Space-relative addressing is a key technology in oracle8 that supports ultra-large databases.

4. Data Object number
Data Object number indicates the segment. all segments have data object number,
It is stored in each data block and is not repeated.

At the beginning, dba_objects.object_id = DBA_OBJECTS.DATA-OBJECT_ID, but in
In the above case, the DATA-OBJECT_ID will increase in the following circumstances
Truncate table
Move Partition
Oracle checks the data object number in rowid and the data object number in the block,
Ensure that the versions are consistent.
Oracle also uses data object number to ensure rollback record and latest segment record 1
To.
Note that the data object number is not an object identifier.

5. Restricted rowid
The rowid format of oracle7 is
1-8 bits: block number
9-12 digits: row number
13-16 bits: file number
Oracle8 supports short and old format rowids.
Index Entry for nopartition table
Local index entry for partition table
Row piece chain pointer
The internal storage of restricted rowid is 6 bytes,
4 byte = data block number
2 byte = row number
This means that the index entry uses 6 bytes to store the rowid, which is sufficient for most indexes. However
This short rowid cannot be used on the global index of the patition table, because partition may
Cross-tablespace. It shows that this rowid is still 18 bits.

6. Extended rowid
Oracle stores 10 bytes internally, including (data object number, data block nu
Mber, row number)
Oracle8 uses the extended rowid:
Partition Table's global index
Server Algorithm
The extended rowid is still 18 characters displayed in the SELECT statement, and is stored in the rowid field.

7. Use the rowid of oracle7 in oracle8
When you query the rowid of oracle7 from an oracle8 database, rowid returns the format of oracle7.
Used in the where statement.
When you query the rowid of oracle8 from the database of oracle7, rowid returns the format of oracle8.
It is used in the where statement, but cannot be stored in the rowid field. However, you must use the dbms_rowid package.
Explanation.
If the extended oracle8 rowid is included, the data of oracle8 cannot be imported to oracle7. Slave
You can import data from oracle7 to oracle8.

8. Application migration problems
Generally, there should be no problem with program porting. The porting problem is considered only in the following situations:
Application uses rowid
Table includes rowid fields.
If the program has the following conditions, you must use the dbms_rowid package:
Self-Assembled rowid
Self-decomposition of rowid
If only rowid is passed to the variable or used as a whole, it is not affected.

9. data migration problems
Regardless of whether export/import is used or the port migration tool, the rowid field in oracle7 is included in oracle8.
It automatically expands.
If a field contains rowid, you must manually use the dbms_rowid package for conversion.

10. dbms_rowid package
Created by $ ORACLE_HOME/rdbms/admin/dbmsutil. SQL, it is included in catproc. SQL
. Provides some functions for processing rowid.
Rowid_create
Rowid_info
Rowid_type
Rowid_object
Rowid_relative_fno
Rowid_block_number
Rowid_to_absolute_fno
Rowid_to_extended
Rowid_to_restricted
Rowid_verify

 

Dbms_rowid.rowid_to_extended
(Old_rowid in rowid,
Schema_name in varchar2,
Object_name in varchar2,
Conversion_type in number
)
Return rowid;
Convert restricted rowid to extended rowid, which is used to convert the old rowid to oracle8 format.

Dbms_rowid.rowid_to_restricted
Convert the extended rowid to the restricted rowid.

Dbms_rowid.rowid_verify
Determines whether a restricted rowid can be converted to an extended format.

Dbms_rowid.row_info
Used to explain rowid. You can obtain data object number, relative file number, and block.
Number and row number.

Dbms_rowid.create
Generate rowid.

End

 

 

 

Oracle rowid Structure Analysis

 

From: http://rainnyzhong.bokee.com/5793871.html

Oracle does not introduce the encoding rules of the logical rowid, and it is difficult to introduce the encoding rules through dump results. Therefore, this article only briefly discusses the storage of the logical rowid.

The following is an example.

 

SQL> Create Table test_index (ID number primary key, name varchar2 (20) Organization index;

The table has been created.

SQL> insert into test_index values (1, 'A ');

One row has been created.

SQL> commit;

Submitted.

SQL> Col dump_rowid format A60
SQL> select rowid, dump (rowid) dump_rowid from test_index;

Rowid dump_rowid
-------------------------------------------------------------------
* Bafab4wcwql + typ = 208 Len = 10: 7,140, 2,193, 2,254

The first two values of the dump result of the logical rowid are 2 and 4, and the last one is 254 (I have not found any other situations). Because the logical rowid is related to the value of the primary key, therefore, the length is not fixed, so it should be used to indicate the start and end.

The four or 3rd bits are the same as the physical rowid, indicating that the data file number of the relative tablespace is multiplied by the value of 64.

5th and 6 bits indicate the number of blocks in the data file.

Starting from 7th bits to the second-to-last position of the dump result, which indicates the value of the primary key. The first is the length of the first field in the primary key. Here it is 2, and then the value of the primary key. Because it is of the number type, 193,2 indicates the value 1. For a primary key consisting of multiple fields, the first field is followed by the length of the second field, and then the value of the second field .......

SQL> select (1*256 + 64)/64 from dual;

(1*256 + 64)/64
-------------
5

SQL> select 7*256 + 140 from dual;

256 + 140
----------
1932

SQL> alter system dump datafile 5 blocks 1932;

The system has been changed.

Find the corresponding dump file to find the inserted record.

Dump File F: oracleadmintest4udumptest4_ora_3828.trc
Thu Dec 23 00:17:53 2004
Oracle v9.2.0.4.0-production vsnsta = 0
Vsnsql = 12 vsnxtr = 3
Windows 2000 version 5.1 Service Pack 1, CPU type 586
Oracle9i Enterprise Edition Release 9.2.0.4.0-Production
With the partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
Jserver release 9.2.0.4.0-Production
Windows 2000 version 5.1 Service Pack 1, CPU type 586
Instance name: test4

Redo thread mounted by this instance: 1

Oracle process number: 9

Windows Thread ID: 3828, image: Oracle. exe

* ** 00:17:53. 361
* ** Session ID: (8.82) 00:17:53. 301
Start dump data blocks TSN: 5 file #: 5 minblk 1932 maxblk 1932
Buffer TSN: 5 rdba: 0x0140078c (5/1932)
SCN: 0x0000. 00e9f122 seq: 0x01 flg: 0x02 tail: 0xf1220601
FRMT: 0x02 chkval: 0x0000 type: 0x06 = trans data
Block header dump: 0x0140078c
Object ID on block? Y
SEG/obj: 0x1e48 CSC: 0x00. e9f113 ITC: 2 flg: E Typ: 2-Index
BRN: 0 bdba: 0x1400789 Ver: 0x01
INC: 0 exflg: 0
 
ITL Xid UBA flag lck scn/FSC
0x01 0x0000. 000.00000000 0x00000000. 0000.00 ---- 0 FSC 0x0000.00000000
0x02 0x0005. 008.000000e7 0x00800226. 005c. 24 -- U-1 FSC 0x0000. 00e9f122
 
Leaf block dump
====================
Header address 71963236 = 0x44a1264
Kdxcolev0
Kdxcolevflags = ---
Kdxcolok 0
Kdxcoopc 0x90: opcode = 0: IOT flags = I -- is converted = y
Kdxconco 1
Kdxcosdc 0
Kdxconro 1
Kdxcofbo 38 = 0x26
Kdxcofeo 8026 = 0x1f5a
Kdxcoavs 7988
Kdxlespl 0
Kdxlende 0
Kdxlenxt 0 = 0x0
Kdxleprv 0 = 0x0
Kdxledsz 0
Kdxlebksz8036
Row #0 [8026] flag: K ----, lock: 2
Col 0; Len 2; (2): C1 02
TL: 5 FB: -- H-FL -- LB: 0x0 cc: 1
Col 0: [1]
Dump of memory from 0x044a31c7 to 0x044a31c8
44a31c0 61010100 [... A]
----- End of leaf block dump -----
End dump data blocks TSN: 5 file #: 5 minblk 1932 maxblk 1932

We can see that the physical location of the record can be located based on the 3, 4, 5, and 6 digits of the dump result.

Note that the index organization table stores data in the order of primary keys. Therefore, inserting, updating, and deleting data may change the physical location of a record, in this case, the data in datafile and block in rowid may not be able to correctly locate the physical location of the record. When you access an index to organize a table based on the Logical rowid, the corresponding block is first found based on the datafile and block information to check whether the data is in this block. If not, you can use the primary key information in the logical rowid to scan the index and find this record. This is the physical guess mentioned in the Oracle document.

The following is an example of a joint primary key consisting of a string and a date.

SQL> Create Table test_index2 (ID char (4), time date,
2 constraint pk_test_index2 primary key (ID, time) Organization index;

The table has been created.

SQL> insert into test_index2 values ('1', sysdate );

One row has been created.

SQL> Col dump_rowid format a75
SQL> select rowid, dump (rowid) dump_rowid from test_index2;

Rowid dump_rowid
----------------------------------------------------------------------------------------------
* Bafab5qemsagiad4aawxasmt/g typ = 208 Len = 20: 7,148, 120,104, 19,254

It can be seen that 7th bits are the length of the field ID 4, followed by the ASCII code of string 1 and three spaces, which is the storage format of the string, followed by 7 is the length of the field time, the last seven digits are the date storage format. In the logic rowid, the storage formats of values, characters, and dates are the same as those of their own storage formats, which are not described here.

 

Generally, one digit is used to indicate the length. However, if the length exceeds 127 (The hexadecimal dump result is 7f), the length is represented by two digits. The first digit starts with 8. This 8 is only a flag, indicating that the length field is represented by two digits. For example, the length of 128 represents 8080 bits, and the maximum supported value of 3800 represents 8ed8.

 

This article is from the csdn blog. For more information, see file: // D: /My % 20 documents/Oracle % 20rowid some knowledge % 20-% 20falent column % 20-% 20csdn blog. MHT