Detailed explanation of rowid in Oracle Study Guide

This article brings you relevant knowledge about Oracle, which mainly introduces related issues about rowid. Each row of data in the Oracle database table has a unique identifier. Or called rowid, it is usually used inside Oracle to access data. I hope it will be helpful to everyone.

Recommended tutorial: "Oracle Learning Tutorial"

1. Overview of saving information in rowid

oracle database Each row of data in the table has a unique identifier, or rowid, which is usually used to access data within Oracle. rowid requires 10 bytes of storage space and uses 18 characters to display. This value indicates the specific physical location of the row in the Oracle database. Rowid can be used in a query to indicate that the value is included in the query results.

Save rowid requires 10 bytes or 80 binary bits. An extended rowid is stored in 10 bytes, with a total of 80 bits, including obj#32bit, rfile#10bit, block#22bit, and row#16bit. Therefore, the relative file number cannot exceed 1023, that is, the number of data files in a table space cannot exceed 1023 (there is no file with file number 0). A datafile can only have 2^22=4M blocks, and no more than 1023 blocks can be included in one block. 2^16=64K rows of data. There cannot be more than 2^32=4G objects in a database.
These 80 binary bits are:
1. Data object number, indicating the number of the database object to which this row belongs. Each data object is uniquely assigned a number when the database is created, and this number is unique . The data object number occupies approximately 32 bits.
2. Corresponding file number, indicating the number of the file where the row is located. Each file label in the table space is unique. The file number occupies 10 digits.
3. Block number, indicating the location of the block of the file where the line is redirected. The block number requires 22 digits.
4. Row number, indicating the specific position of the row in the row directory. The row number requires 16 digits.
This adds up to 80 bits.

Note: Before Oracle 8 version, rowid was composed of file# block# row#, occupying 6 bytes of space, 10 bit file#, 22bit block#, 16 bit row# . Oracle8 and later versions change the space to 10 bytes.

Oracle's physical extended ROWID has 18 bits, each bit is encoded in 64 bits, using A~Z, a~z, 0~9, ,/ A total of 64 characters are represented. A represents 0, B represents 1, ...Z represents 25, a represents 26, ...z represents 51, 0 represents 52, ..., 9 represents 61, represents 62, / represents 63.

SELECT T.ROWID, T.* FROM DEPT T

In order to verify that the storage space of rowid is 10 bytes, including 32bit object#, 10bit rfile#, 22bit block#, and 16bit row#. We need to use the dump function.

select rowid,dump(rowid,16) from DEPT

2. Verify and view the rowid content

1. Use the function in the dbms_rowid package to view

SELECT ROWID,
       DBMS_ROWID.ROWID_OBJECT(ROWID) AS OBJECT,
       DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) AS FILENUM,
       DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) AS BLOCK,
       DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) AS ROWN
  FROM DEPT;

The results are as follows:

2. Query data information from the table

1) Query object number: DATA_OBJECT_ID

SELECT OBJECT_NAME, OBJECT_TYPE, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID
  FROM DBA_OBJECTS T
 WHERE T.OBJECT_NAME ='DEPT'
 AND T.OWNER = 'CHF';

The results are as follows:

Description: The difference between DATA_OBJECT_ID and OBJECT_ID

Object_id and data_object_id are both unique identifiers of objects.

object_id is the logical identification of the object

data_object_id is the physical identification of the object

Only tables, indexes, and undo objects with actual physical storage locations have data_object_id. For objects without physical storage, data_object_id is empty. For example: (procedure, function, package, data type, db link, mv definition, view definition, temporary table, partition table definition, etc.)

In most cases, the two are equal. However, after truncate, move, rebuild and other operations are performed on the object, the data_object_id will change, but the object_id will not change.

Perform the truncate operation on the target DEPT, code: TRUNCATE TABLE DEPT;

Query the results again:

Replace the truncate operation Re-insert the data into the table and check that the rowid representing the object number has changed from AAAUOO to AAAUOP, increasing by 1.

2) Query file number:

SELECT T.SEGMENT_NAME, T.HEADER_BLOCK, T.BLOCKS, T.EXTENTS, T.RELATIVE_FNO
  FROM DBA_SEGMENTS T
 WHERE T.SEGMENT_NAME = 'DEPT'
   AND T.OWNER = 'CHF';

HEADER_BLOCK: The first data block number of this table

BLOCKS: The number of the first data block of this table Number of data blocks

RELATIVE_FNO: Relative file number

The results are as follows:

说明：

从Oracle8开始，Oracle开始使用“相对文件号”，使原来一个数据库最多只能有1023个文件，扩展为一个表空间最多可以有1023个文件，每个库最多可以有65534个文件

验证文件号

SELECT T.TABLE_NAME,
       T.TABLESPACE_NAME,
       G.FILE_NAME,
       G.FILE_ID,
       G.RELATIVE_FNO
  FROM DBA_TABLES T
 INNER JOIN DBA_DATA_FILES G
    ON G.TABLESPACE_NAME = T.TABLESPACE_NAME
 WHERE T.TABLE_NAME = 'DEPT'
 AND T.OWNER = 'CHF';

执行结果：

因为创建用户时没用指定默认表空间，建表时也没用指定表空间，所以此处使用的USERS表空间（大家不必在意这些细节...），可以看到文件号和相对文件号都是 4 ，这是因为我的数据库中每个表空间只有一个数据文件，如果一个表空间有多个数据文件，这两个值有可能不一样。

知识扩展：

我们可以使用跟踪文件查看数据文件信息，命令：alter session set events 'immediate trace name FILE_HDRS level 10';

执行完此代码后，将在数据库服务器生成一个跟踪文件，查看文件路径代码：

select
　　u_dump.value || '/' ||
　　db_name.value || '_ora_' ||
　　v$process.spid ||
　　nvl2(v$process.traceid, '_' || v$process.traceid, null )
　　|| '.trc' "Trace File"
from
v$parameter u_dump
cross join v$parameter db_name
cross join v$process
join v$session
on v$process.addr = v$session.paddr
where
　　u_dump.name = 'user_dump_dest' and
　　db_name.name = 'db_name' and
　　v$session.audsid=sys_context('userenv','sessionid');

推荐教程：《Oracle教程》

The above is the detailed content of Detailed explanation of rowid in Oracle Study Guide. For more information, please follow other related articles on the PHP Chinese website!