Oracle rowid,dba,rdba,uba,rba，scn说明
Oracle Rowid 介绍
一. 官网说明
owid的定义：A globally unique address for a row in a database.
rowid 分为extended rowid 和 restricted rowied.
1.1 Restricted ROWID
Internally, the ROWID is a structure that holds information that the database server needs to access a row. The restricted internal ROWID is 6 bytes on most platforms.
Each restricted rowid includes the following data:
（1）Datafile identifier
（2）Block identifier
（3）Row identifier
The restricted ROWID pseudocolumn is returned to client applications in the form of an 18-character string with a hexadecimal encoding of the datablock, row, and datafile components of the ROWID.
1.2 Extended ROWID
The extended ROWID datatype includes the data in the restricted rowid plus a data object number. The data object number is an identification number assigned to every database segment. The extended internal ROWID is 10 bytes on most platforms.
Data in an extended ROWID pseudocolumn is returned to the client application in the form of an 18-character string (for example, “AAAA8mAALAAAAQkAAA”), which represents a base 64 encoding of the components of the extended ROWID in a four-piece format, OOOOOOFFFBBBBBBRRR. Extended rowids are not available directly. You can use a supplied package, DBMS_ROWID, to interpret extended rowid contents. The package functions extract and provide information that would be available directly from a restricted rowid as well as information specific to extended rowids.
1.3 Rowid Format
Oracle Database uses a rowid to uniquely identify a row. Internally, the rowid is a structure that holds information that the database needs to access a row. A rowid is not physically stored in the database, but is inferred from the file and block on which the data is stored.
An extended rowid includes a data object number. This rowid type uses a base 64 encoding of the physical address for each row. The encoding characters are A-Z, a-z, 0-9, +, and /.
Example 12-1 queries the ROWID pseudocolumn to show the extended rowid of the row in the employees table for employee 100.
Example 12-1 ROWID Pseudocolumn
SQL> SELECT ROWID FROM employees WHERE employee_id = 100;
ROWID
AAAPecAAFAAAABSAAA
Figure 12-8 illustrates the format of an extended rowid.
Figure 12-8 ROWID Format
An extended rowid is displayed in a four-piece format, OOOOOOFFFBBBBBBRRR, with the format divided into the following components:
（1）OOOOOO
The data object number identifies the segment (data object AAAPec in Example 12-1). A data object number is assigned to every database segment. Schema objects in the same segment, such as a table cluster, have the same data object number.
（2）FFF
The tablespace-relative data file number identifies the data file that contains the row (file AAF in Example 12-1).
（3）BBBBBB
The data block number identifies the block that contains the row (block AAAABS in Example 12-1). Block numbers are relative to their data file, not their tablespace. Thus, two rows with identical block numbers could reside in different data files of the same tablespace.
（4）RRR
The row number identifies the row in the block (row AAA in Example 12-1).
After a rowid is assigned to a row piece, the rowid can change in special circumstances. For example,
（1）if row movement is enabled, then the rowid can change because of partition key updates, Flashback Table operations, shrink table operations, and so on.
（2）If row movement is disabled, then a rowid can change if the row is exported and imported using Oracle Database utilities.
– rowid 改变的条件
Note:
Internally, the database performs row movement as if the row were physically deleted and reinserted. However, row movement is considered an update, which has implications for triggers.
二. rowid 说明
rowid是伪列（pseudocolumn），伪劣的意思是实际上这一列本身在数据字典中并不存在，在查询结果输出时它被构造出来的。
rowid并不会真正存在于表的data block中,但是他会存在于index当中，用来通过rowid来寻找表中的行数据。
2.1 利用rowid来得到相关信息
SQL> conn sys/admin as sysdba
已连接。
SQL> create table bl(id number);
表已创建。
SQL> insert into bl values(1);
已创建 1 行。
SQL> select owner,segment_name,file_id,RELATIVE_FNO,block_id from dba_extents where owner=‘SYS’ and segment_name=‘BL’;
OWNER SEGMENT_NA FILE_ID RELATIVE_FNO BLOCK_ID
SYS BL 1 1 62129
SQL> desc dbms_rowid.rowid_info
PACKAGE SYS.DBMS_ROWID
PROCEDURE ROWID_INFO
Argument Name Type In/Out
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
SQL> set serveroutput on
SQL>DECLARE
v_rowid_type NUMBER;
v_OBJECT_NUMBER NUMBER;
v_RELATIVE_FNO NUMBER;
v_BLOCK_NUMBERE_FNO NUMBER;
v_ROW_NUMBER NUMBER;
BEGIN
DBMS_ROWID.rowid_info (
rowid_in => ‘AAAJVnAANAAAACiAAA’,
rowid_type => v_rowid_type,
object_number => v_OBJECT_NUMBER,
relative_fno => v_RELATIVE_FNO,
block_number => v_BLOCK_NUMBERE_FNO,
ROW_NUMBER => v_ROW_NUMBER);
DBMS_OUTPUT.put_line ('ROWID_TYPE: ’ || TO_CHAR (v_rowid_type));
DBMS_OUTPUT.put_line ('OBJECT_NUMBER: ’ || TO_CHAR (v_OBJECT_NUMBER));
DBMS_OUTPUT.put_line ('RELATIVE_FNO: ’ || TO_CHAR (v_RELATIVE_FNO));
DBMS_OUTPUT.put_line ('BLOCK_NUMBER: ’ || TO_CHAR (v_BLOCK_NUMBERE_FNO));
DBMS_OUTPUT.put_line ('ROW_NUMBER: ’ || TO_CHAR (v_ROW_NUMBER));
END;
/
结果：
ROWID_TYPE: 1
OBJECT_NUMBER: 38247
RELATIVE_FNO: 13
BLOCK_NUMBER: 162
ROW_NUMBER: 0
2.2 . Rowid的结构
ROWID 格式:
扩展的ROWID 在磁盘上需要10 个字节的存储空间，并使用18 个字符来显示。
它包含下列组成元素：
1. 数据对象编号：每个数据对象（如表或索引）在创建时都分配有此编号，并且此编号在数据库中是唯一的
2. 相关文件编号：此编号对于表空间中的每个数据文件是唯一的
3. 块编号：表示包含此行的块在数据文件中的位置
4. 行编号：标识块头中行目录位置的位置
在内部，存储的10个字节(bytes)，即80位(bit)又按如下规则进行划分：
（1）数据对象编号需要32 bit
（2）相关文件编号需要10 bit
（3）块编号需要22 bit
（4）行编号需要16 bit
在oracle 8以前，一个rowid占用6个字节大小的存储空间(10bit file#+22bit block#+16bit row#), rowid格式为:BBBBBBBB.RRRR.FFFF。
在oracle 8以后， rowid的存储空间扩大到了10个字节(32bit object#+10bit rfile#+22bit block#+16bit row#),文件号仍然用10位表示,只是不再需要置换,为了向后兼容,同时引入了相对文件号(rfile#),所以从Oracle7到Oracle8,Rowid仍然无需发生变化.
Rdba(Tablespace relative database block address)就是rowid中的rfile#+block#.
rowid这样改变之后，数据库中数据库文件个数的限制从整个数据库最多只能有的2^10-2=1022个数据文件(去掉全0和全1), 变为了每个表空间中可以最多有2^10-2个数据文件。
所以说，数据库能支持的数据文件最大数是受rowid的长度限制的。
需要注意的是: local index中存储的rowid是6个字节，而global index中存储的rowid是10个字节。
那么增加的32bit object# 这个前缀主要就是用来定位表空间的，同时这个object#其实对应的就是data_object_id，由于一个段对象只能属于一个表空间，同时data_object_id就是标识了一个段的物理存储id.因此object#+rfile#就可以唯一定位当前的rowid是在那个数据文件上了。
可以通过dbms_rowid这个包来转换我们的rowid成不同组成部分：
dbms_rowid.rowid_object(rowid)—> 32bit object#
dbms_rowid.rowid_relative_fno(rowid)—> 10bit rfile#
dbms_rowid.rowid_block_number(rowid)—> 22bit block#
dbms_rowid.rowid_row_number(rowid)—> 16bit row#
扩展的ROWID 使用以64 为基数的编码方案来显示，该方案将六个位置用于数据对象编号、三个位置用于相关文件编号、六个位置用于块编号、三个位置用于行编号。
以64 为基数的编码方案使用字符“A-Z”、“a-z”、“0-9” 和“/”。共有64 个字符，如下例所示：
SQL> SELECT department_id, rowid FROM hr.departments;
EMPNO ROWID
7488 AAAMfPAAEAAAAAgAAA
7499 AAAMfPAAEAAAAAgAAB
7521 AAAMfPAAEAAAAAgAAC
7566 AAAMfPAAEAAAAAgAAD
7654 AAAMfPAAEAAAAAgAAE
7698 AAAMfPAAEAAAAAgAAF
7782 AAAMfPAAEAAAAAgAAG
7788 AAAMfPAAEAAAAAgAAH
7839 AAAMfPAAEAAAAAgAAI
7844 AAAMfPAAEAAAAAgAAJ
7876 AAAMfPAAEAAAAAgAAK
7900 AAAMfPAAEAAAAAgAAL
在本例中：
AAAMfP 是数据对象编号
AAE 是相关文件编号
AAAAAg是块编号
AAA是EMPNO=7488 的部分的行编号
其他内容参考：
Oracle 数据块 Block 说明
http://blog.csdn.net/xujinyang/article/details/6829273
Oracle 数据块 Block 说明
Logical Storage Structures
http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/logical.htm#CNCPT7668
Oracle Database manages the logical storage space in the data files of a database in units called data blocks, also called Oracle blocks or pages. A data block is the minimum unit of database I/O.
一. Data Blocks and Operating System Blocks
At the physical level, database data is stored in disk files made up of operating system blocks. An operating system block is the minimum unit of data that the operating system can read or write. In contrast, an Oracle block is a logical storage structure whose size and structure are not known to the operating system.
Figure shows that operating system blocks may differ in size from data blocks. The database requests data in multiples of data blocks, not operating system blocks.
When the database requests a data block, the operating system translates this operation into a requests for data in permanent storage. The logical separation of data blocks from operating system blocks has the following implications:
（1）Applications do not need to determine the physical addresses of data on disk.
（2）Database data can be striped or mirrored on multiple physical disks.
二. Database Block Size
Every database has a database block size. The DB_BLOCK_SIZE initialization parameter sets the data block size for a database when it is created. The size is set for the SYSTEM and SYSAUX tablespaces and is the default for all other tablespaces. The database block size cannot be changed except by re-creating the database.
If DB_BLOCK_SIZE is not set, then the default data block size is operating system-specific. The standard data block size for a database is 4 KB or 8 KB. If the size differs for data blocks and operating system blocks, then the data block size must be a multiple of the operating system block size.
三. Tablespace Block Size
You can create individual tablespaces whose block size differs from the DB_BLOCK_SIZE setting. A nonstandard block size can be useful when moving atransportable tablespace to a different platform.
四. Data Block Format
Every data block has a format or internal structure that enables the database to track the data and free space in the block. This format is similar whether the data block contains table, index, or table cluster data. Figure shows the format of an uncompressed data block.
五. Data Block Overhead
Oracle Database uses the block overhead to manage the block itself. The block overhead is not available to store user data. The block overhead includes the following parts:
5.1 Block header
This part contains general information about the block, including disk address and segment type. For blocks that are transaction-managed, the block headercontains active and historical transaction information.
A transaction entry is required for every transaction that updates the block. Oracle Database initially reserves space in the block header for transaction entries. In data blocks allocated to segments that support transactional changes, free space can also hold transaction entries when the header space is depleted.The space required for transaction entries is operating system dependent. However, transaction entries in most operating systems require approximately 23 bytes.
5.2 Table directory
For a heap-organized table, this directory contains metadata about tables whose rows are stored in this block. Multiple tables can store rows in the same block.
– metadata，元数据，指表的定义语句。
heap-organized table: A table in which the data rows are stored in no particular order on disk. By default, CREATE TABLE creates a heap-organized table.
5.3 Row directory
For a heap-organized table, this directory describes the location of rows in the data portion of the block.
After space has been allocated in the row directory, the database does not reclaim this space after row deletion. Thus, a block that is currently empty but formerly had up to 50 rows continues to have 100 bytes allocated for the row directory. The database reuses this space only when new rows are inserted in the block.
Some parts of the block overhead are fixed in size, but the total size is variable. On average, the block overhead totals 84 to 107 bytes.
六. Row Format
The row data part of the block contains the actual data, such as table rows or index key entries. Just as every data block has an internal format, every row has a row format that enables the database to track the data in the row.
Oracle Database stores rows as variable-length records. A row is contained in one or more row pieces. Each row piece has a row header and column data.
Figure shows the format of a row.
6.1 Row Header
Oracle Database uses the row header to manage the row piece stored in the block. The row header contains information such as the following:
（1）Columns in the row piece
（2）Pieces of the row located in other data blocks
If an entire row can be inserted into a single data block, then Oracle Database stores the row as one row piece. However, if all of the row data cannot be inserted into a single block or an update causes an existing row to outgrow its block, then the database stores the row in multiple row pieces (see"Chained and Migrated Rows"). A data block usually contains only one row piece per row.
（3）Cluster keys for table clusters (see “Overview of Table Clusters”)
A row fully contained in one block has at least 3 bytes of row header.
6.2 Column Data
After the row header, the column data section stores the actual data in the row. The row piece usually stores columns in the order listed in the CREATE TABLE statement, but this order is not guaranteed. For example, columns of type LONG are created last.
For each column in a row piece, Oracle Database stores the column length and data separately. The space required depends on the data type. If the data type of a column is variable length, then the space required to hold a value can grow and shrink with updates to the data.
Each row has a slot in the row directory of the data block header. The slot points to the beginning of the row.
See Also: “Table Storage” and “Index Storage”
七. Rowid Format
Oracle Database uses a rowid to uniquely identify a row. Internally, the rowid is a structure that holds information that the database needs to access a row. A rowid is not physically stored in the database, but is inferred from the file and block on which the data is stored.
An extended rowid includes a data object number. This rowid type uses a base 64 encoding of the physical address for each row. The encoding characters are A-Z, a-z, 0-9, +, and /.
Example 12-1 queries the ROWID pseudocolumn to show the extended rowid of the row in the employees table for employee 100.
Example 12-1 ROWID Pseudocolumn
SQL> SELECT ROWID FROM employees WHERE employee_id = 100;
ROWID
AAAPecAAFAAAABSAAA
the following Figure illustrates the format of an extended rowid.
An extended rowid is displayed in a four-piece format, OOOOOOFFFBBBBBBRRR, with the format divided into the following components:
7.1 OOOOOO
The data object number identifies the segment (data object AAAPec in Example 12-1). A data object number is assigned to every database segment. Schema objects in the same segment, such as a table cluster, have the same data object number.
– 数据对象编号：每个数据对象（如表或索引）在创建时都分配有此编号，并且此编号在数据库中是唯一的.
7.2 FFF
The tablespace-relative data file number identifies the data file that contains the row (file AAF in Example 12-1).
相关文件编号：此编号对于表空间中的每个文件是唯一的
7.3 BBBBBB
The data block number identifies the block that contains the row (block AAAABS in Example 12-1). Block numbers are relative to their data file, not their tablespace. Thus, two rows with identical block numbers could reside in different data files of the same tablespace.
块编号：表示包含此行的块在文件中的位置
7.4 RRR
The row number identifies the row in the block (row AAA in Example 12-1).
行编号：标识块头中行目录位置的位置
After a rowid is assigned to a row piece, the rowid can change in special circumstances. For example,
if row movement is enabled, then the rowid can change because of partition key updates, Flashback Table operations, shrink table operations, and so on.
If row movement is disabled, then a rowid can change if the row is exported and imported using Oracle Database utilities.
Note:
Internally, the database performs row movement as if the row were physically deleted and reinserted. However, row movement is considered an update, which has implications for triggers.
八. Data Block Compression
The database can use table compression to eliminate duplicate values in a data block (see “Table Compression”). This section describes the format of data blocks that use compression.
The format of a data block that uses basic and OLTP table compression is essentially the same as an uncompressed block. The difference is that a symbol table at the beginning of the block stores duplicate values for the rows and columns. The database replaces occurrences of these values with a short reference to the symbol table.
Assume that the rows in Example 12-2 are stored in a data block for the seven-column sales table.
Example 12-2 Rows in sales Table
2190,13770,25-NOV-00,S,9999,23,161
2225,15720,28-NOV-00,S,9999,25,1450
34005,120760,29-NOV-00,P,9999,44,2376
9425,4750,29-NOV-00,I,9999,11,979
1675,46750,29-NOV-00,S,9999,19,1121
When basic or OLTP table compression is applied to this table, the database replaces duplicate values with a symbol reference.
Example 12-3 is a conceptual representation of the compression in which the symbol * replaces 29-NOV-00 and % replaces 9999.
Example 12-3 OLTP Compressed Rows in sales Table
2190,13770,25-NOV-00,S,%,23,161
2225,15720,28-NOV-00,S,%,25,1450
34005,120760,,P,%,44,2376
9425,4750,,I,%,11,979
1675,46750,*,S,%,19,1121
Table 12-1 conceptually represents the symbol table that maps symbols to values.
Table 12-1 Symbol Table
Symbol Value Column Rows
- 29-NOV-00 3 958-960
% 9999 5 956-960
九. Space Management in Data Blocks
As the database fills a data block from the bottom up, the amount of free space between the row data and the block header decreases. This free space can also shrink during updates, as when changing a trailing null to a nonnull value. The database manages free space in the data block to optimize performance and avoid wasted space.
Note: This section assumes the use of automatic segment space management.
9.1 Percentage of Free Space in Data Blocks
The PCTFREE storage parameter is essential to how the database manages free space. This SQL parameter sets the minimum percentage of a data reserved as free space for updates to existing rows. Thus, PCTFREE is important for preventing row migration and avoiding wasted space.
For example, assume that you create a table that will require only occasional updates, most of which will not increase the size of the existing data. You specify the PCTFREE parameter within a CREATE TABLE statement as follows:
CREATE TABLE test_table (n NUMBER) PCTFREE 20;
Figure 12-9 shows how a PCTFREE setting of 20 affects space management. The database adds rows to the block over time, causing the row data to grow upwards toward the block header, which is itself expanding downward toward the row data. The PCTFREE setting ensures that at least 20% of the data block is free. For example, the database prevents an INSERT statement from filling the block so that the row data and header occupy a combined 90% of the total block space, leaving only 10% free.
Figure 12-9 PCTFREE
Note:
This discussion does not apply to LOB data types, which do not use the PCTFREE storage parameter or free lists. See “Overview of LOBs”.
9.2 Optimization of Free Space in Data Blocks
While the percentage of free space cannot be less than PCTFREE, the amount of free space can be greater. For example, a PCTFREE setting of 20% prevents the total amount of free space from dropping to 5% of the block, but permits 50% of the block to be free space. The following SQL statements can increase free space:
（1）DELETE statements
（2）UPDATE statements that either update existing values to smaller values or increase existing values and force a row to migrate
（3）INSERT statements on a table that uses OLTP compression
If inserts fill a block with data, then the database invokes block compression, which may result in the block having more free space.
The space released is available for INSERT statements under the following conditions:
（1）If the INSERT statement is in the same transaction and after the statement that frees space, then the statement can use the space.
（2）If the INSERT statement is in a separate transaction from the statement that frees space (perhaps run by another user), then the statement can use the space made available only after the other transaction commits and only if the space is needed.
9.3 Coalescing Fragmented Space
Released space may or may not be contiguous with the main area of free space in a data block, as shown in Figure 12-10. Noncontiguous free space is called fragmented space.
Figure 12-10 Data Block with Fragmented Space
Oracle Database automatically and transparently coalesces the free space of a data block only when the following conditions are true:
（1）An INSERT or UPDATE statement attempts to use a block that contains sufficient free space to contain a new row piece.
（2）The free space is fragmented so that the row piece cannot be inserted in a contiguous section of the block.
After coalescing, the amount of free space is identical to the amount before the operation, but the space is now contiguous. Figure 12-11 shows a data block after space has been coalesced.
Figure 12-11 Data Block After Coalescing Free Space
Oracle Database performs coalescing only in the preceding situations because otherwise performance would decrease because of the continuous coalescing of the free space in data blocks.
9.4 Reuse of Index Space
The database can reuse space within an index block. For example, if you insert a value into a column and delete it, and if an index exists on this column, then the database can reuse the index slot when a row requires it.
The database can reuse an index block itself. Unlike a table block, an index block only becomes free when it is empty. The database places the empty block on the free list of the index structure and makes it eligible for reuse. However, Oracle Database does not automatically compact the index: an ALTER INDEX REBUILD or COALESCE statement is required.
Figure 12-12 represents an index of the employees.department_id column before the index is coalesced. The first three leaf blocks are only partially full, as indicated by the gray fill lines.
Figure 12-12 Index Before Coalescing
Figure 12-13 shows the index in Figure 12-12 after the index has been coalesced. The first two leaf blocks are now full, as indicated by the gray fill lines, and the third leaf block has been freed.
Figure 12-13 Index After Coalescing
9.5 Chained and Migrated Rows
Oracle Database must manage rows that are too large to fit into a single block. The following situations are possible:
（1）The row is too large to fit into one data block when it is first inserted.
In row chaining, Oracle Database stores the data for the row in a chain of one or more data blocks reserved for the segment. Row chaining most often occurs with large rows.
Examples include rows that contain a column of data type LONG or LONG RAW, a VARCHAR2(4000) column in a 2 KB block, or a row with a huge number of columns. Row chaining in these cases is unavoidable.
（2）A row that originally fit into one data block is updated so that the overall row length increases, but insufficient free space exists to hold the updated row.
In row migration, Oracle Database moves the entire row to a new data block, assuming the row can fit in a new block. The original row piece of a migrated row contains a pointer or "forwarding address" to the new block containing the migrated row. The rowid of a migrated row does not change.
（3）A row has more than 255 columns.
Oracle Database can only store 255 columns in a row piece. Thus, if you insert a row into a table that has 1000 columns, then the database creates 4 row pieces, typically chained over multiple blocks.
Figure 12-14 depicts shows the insertion of a large row in a data block. The row is too large for the left block, so the database chains the row by placing the first row piece in the left block and the second row piece in the right block.
Figure 12-14 Row Chaining
Figure 12-15, the left block contains a row that is updated so that the row is now too large for the block. The database moves the entire row to the right block and leaves a pointer to the migrated row in the left block.
Figure 12-15 Row Migration
When a row is chained or migrated, the I/O needed to retrieve the data increases. This situation results because Oracle Database must scan multiple blocks to retrieve the information for the row.
For example, if the database performs one I/O to read an index and one I/O to read a nonmigrated table row, then an additional I/O is required to obtain the data for a migrated row.
The Segment Advisor, which can be run both manually and automatically, is an Oracle Database component that identifies segments that have space available for reclamation. The advisor can offer advice about objects that have significant free space or too many chained rows.
Oracle rdba和 dba 说明
一.DB(Data block)
From:http://www.orafaq.com/wiki/Data_block
A data block is the smallest unit of storage in anOracle database. Every database has a default block size (specified when the database is created), although blocks in different tablespacesmay have different block sizes.
An extent consist of one or more contiguous Oracle data blocks. A block determines the finest level of granularity of where data can be stored. One data block corresponds to a specific number of bytes of physical space on disk.
Information about data blocks can be retrieved from the data dictionary views USER_SEGMENTS and USER_EXTENTS. These views show how many blocks are allocated for database object and how many blocks are available(free) in asegment/extent.
1.1 Dumping data blocks
Start by getting the file and block number to dump. Example:
SQL>
SELECT
dbms_rowid.rowid_relative_fno(rowid) REL_FNO,
dbms_rowid.rowid_block_number(rowid) BLOCKNO,
dbms_rowid.rowid_row_number(rowid) ROWNO,
empno, ename
FROM emp
WHERE empno = 7369;
REL_FNOBLOCKNOROWNOEMPNO ENAME
42007369 SMITH
Dump the block:
SQL>alter system dump datafile 4 block 20;
System altered.
Look for the newly created dump file in your UDUMP directory.
– dump多个blocks
Use the following syntax to dump multiple blocks:
ALTER SYSTEM dump datafile <file_id> block min <block_id> block max <block_id+blocks-1>;
1.2Analyzing data block dumps
From the above block dump:
block_row_dump:
tab 0, row 0, @0x1d49
tl: 38 fb: --H-FL-- lb: 0x0cc: 8
col0: [ 3]c2 4a 46
col1: [ 5]53 4d 49 54 48
col2: [ 5]43 4c 45 52 4b
col3: [ 3]c2 50 03
col4: [ 7]77 b4 0c 11 01 01 01
col5: [ 2]c2 09
col6: NULL
col7: [ 2]c1 15
Converting back to table values:
Col 0 (EMPNO)
SQL> SELECT utl_raw.cast_to_number(replace(‘c2 4a 46’,’ ')) value FROM dual;
VALUE
7369
Col 2 (ENAME) - simply convert the hex values to ascii - 53 4d 49 54 48 -> SMITH. Alternatively:
SQL> SELECT utl_raw.cast_to_varchar2(replace(‘53 4d 49 54 48’,’ ')) value FROM dual;
VALUE
SMITH
二.DBA（Data Block Address）
From：http://www.orafaq.com/wiki/Data_block_address
AData Block Address(DBA) is the address of an Oracledata blockfor access purposes.
DBA一般指绝对数据块地址. rowid用来表示一行的物理地址，一行唯一确定一个rowid，并且在使用中一般不会改变，除非rowid之后在行的物理位置发生改变的情况下才会发生变化。在rowid中，就有一段是来表示DBA的。有关rowid的内容，参考我的Blog：
Oracle Rowid介绍
http://blog.csdn.net/xujinyang/article/details/6829751
2.1 Find the DBA for a given row
Start by getting the file and block number of the row. Example:
SQL>SELECT
2dbms_rowid.rowid_relative_fno(rowid) REL_FNO,
3dbms_rowid.rowid_block_number(rowid) BLOCKNO,
4empno, ename
5FROM emp WHERE empno = 7369;
REL_FNOBLOCKNOEMPNO ENAME
4 20 7369 SMITH
2.2 convert the file and block numbers to a DBA address:
SQL> variable dba varchar2(30)
SQL>exec :dba := dbms_utility.make_data_block_address(4, 20);
PL/SQL procedure successfully completed.
SQL> print dba
DBA
16777236
2.3Convert a DBA back to file and block numbers
十进制计算：
Example:
SQL>
SELECT dbms_utility.data_block_address_block(16777236) “BLOCK”,
dbms_utility.data_block_address_file(16777236) “FILE”
FROM dual;
BLOCKFILE
204
十六进制计算：
SELECT DBMS_UTILITY.data_block_address_file(
TO_NUMBER(LTRIM(‘0x00800466’,‘0x’),‘xxxxxxxx’))
ASfile_no,
DBMS_UTILITY.data_block_address_block(
TO_NUMBER(LTRIM(‘0x00800466’,‘0x’),‘xxxxxxxx’))
ASblock_no
FROM DUAL;
三.RDBA（Tablespace relative database block address）
在讲RDBA之前，要先了解下rowid的组成。关于rowid的内容，参考我的blog：
Oracle Rowid介绍
http://blog.csdn.net/xujinyang/article/details/6829751
RDBA是相对数据块地址，是数据字典(表空间及一些对象定义)所在块的地址。
oracle 8以后，rowid的存储空间扩大到了10个字节(32bit object#+10bit rfile#+22bit block#+16bit row#)。rdba就是rowid中的rfile#+block#。
SYS@anqing1(rac1)>
SELECT
rowid,
dbms_rowid.rowid_relative_fno(rowid) REL_FNO,
dbms_rowid.rowid_block_number(rowid) BLOCKNO,
dbms_rowid.rowid_row_number(rowid) ROWNO,
empno, ename
FROM scott.emp WHEREempno = 7521;
ROWIDREL_FNOBLOCKNOROWNOEMPNO ENAME
AAAMfMAAEAAAAAgAAA43207369 SMITH
rowid = AAAMfMAAEAAAAAgAAA
BlockNo= 4
rowno =0
把这个block dump到trace:
SYS@anqing1(rac1)> alter system dump datafile 4 block 32;
System altered.
查看当前的trace文件位置：
SYS@anqing1(rac1)> oradebug setmypid;
Statement processed.
SYS@anqing1(rac1)> oradebug tracefile_name
/u01/app/oracle/admin/anqing/udump/anqing1_ora_19997.trc
查看trace file：
[oracle@rac1 ~]$ cat /u01/app/oracle/admin/anqing/udump/anqing1_ora_19997.trc
*** 2011-06-07 11:02:30.023
Start dump data blocks tsn: 4 file#: 4 minblk 32 maxblk 32
buffer tsn: 4 rdba:0x01000020(4/32)-- rdba的值
scn: 0x0000.0006bfdb seq: 0x10 flg: 0x06 tail: 0xbfdb0610
frmt: 0x02 chkval: 0x26a0 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0ED09400 to 0x0ED0B400
ED09400 0000A206 01000020 0006BFDB 06100000[… …]
ED09410 000026A0 00180001 0000C7CC 0006BFD9[.&…]
…
ED094A0 00000000 00000000 00000000 00000000[…]
Repeat 465 times
ED0B1C0 00000000 08012C00 2350C203 4C494D06[…,…P#.MIL]
ED0B1D0 0552454C 52454C43 4EC2034B B6770753[LER.CLERK…NS.w.]
…
ED0B3E0 05485449 52454C43 50C2034B B4770703[ITH.CLERK…P…w.]
ED0B3F0 0101110C 09C20201 15C102FF BFDB0610[…]
Block header dump:0x01000020
Object id on Block? Y
seg/obj: 0xc7cccsc: 0x00.6bfd9itc: 2flg: Etyp: 1 - DATA
brn: 0bdba: 0x1000019 ver: 0x01 opc: 0
inc: 0exflg: 0
ItlXidUbaFlagLckScn/Fsc
0x010x0003.011.000000f20x00805794.00c8.49–U-14fsc 0x0000.0006bfdb
0x020x0000.000.000000000x00000000.0000.00----0fsc 0x0000.00000000
data_block_dump,data header at 0xed09464
tsiz: 0x1f98
hsiz: 0x2e
pbl: 0x0ed09464
bdba: 0x01000020
76543210
flag=--------
ntab=1
nrow=14
frre=-1
fsbo=0x2e
fseo=0x1d61
avsp=0x1d33
tosp=0x1d33
0xe:pti[0]nrow=14 offs=0–该块中保存了14条记录。从row 0到row 13
0x12:pri[0]offs=0x1f72
0x14:pri[1]offs=0x1f47
0x16:pri[2]offs=0x1f1c
0x18:pri[3]offs=0x1ef3
0x1a:pri[4]offs=0x1ec6
0x1c:pri[5]offs=0x1e9d
0x1e:pri[6]offs=0x1e74
0x20:pri[7]offs=0x1e4c
0x22:pri[8]offs=0x1e26
0x24:pri[9]offs=0x1dfb
0x26:pri[10]offs=0x1dd5
0x28:pri[11]offs=0x1daf
0x2a:pri[12]offs=0x1d88
0x2c:pri[13]offs=0x1d61
block_row_dump:
tab 0, row 0, @0x1f72
tl: 38 fb: --H-FL-- lb: 0x1cc: 8
col0: [ 3]c2 4a 46
col1: [ 5]53 4d 49 54 48
col2: [ 5]43 4c 45 52 4b
col3: [ 3]c2 50 03
col4: [ 7]77 b4 0c 11 01 01 01
col5: [ 2]c2 09
col6: NULL
col7: [ 2]c1 15
tab 0, row 1, @0x1f47
tl: 43 fb: --H-FL-- lb: 0x1cc: 8
col0: [ 3]c2 4b 64
col1: [ 5]41 4c 4c 45 4e
col2: [ 8]53 41 4c 45 53 4d 41 4e
col3: [ 3]c2 4d 63
col4: [ 7]77 b5 02 14 01 01 01
col5: [ 2]c2 11
col6: [ 2]c2 04
col7: [ 2]c1 1f
tab 0, row 2, @0x1f1c
tl: 43 fb: --H-FL-- lb: 0x1cc: 8
col0: [ 3]c2 4c 16
col1: [ 4]57 41 52 44
col2: [ 8]53 41 4c 45 53 4d 41 4e
col3: [ 3]c2 4d 63
col4: [ 7]77 b5 02 16 01 01 01
col5: [ 3]c2 0d 33
col6: [ 2]c2 06
col7: [ 2]c1 1f
tab 0, row 3, @0x1ef3
…
tab 0, row 13, @0x1d61
tl: 39 fb: --H-FL-- lb: 0x1cc: 8
col0: [ 3]c2 50 23
col1: [ 6]4d 49 4c 4c 45 52
col2: [ 5]43 4c 45 52 4b
col3: [ 3]c2 4e 53
col4: [ 7]77 b6 01 17 01 01 01
col5: [ 2]c2 0e
col6: NULL
col7: [ 2]c1 0b
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 32 maxblk 32
[oracle@rac1 ~]$
/* Formatted on 2011/6/7 11:27:10 (QP5 v5.163.1008.3004) */
SELECTDBMS_UTILITY.data_block_address_file(
TO_NUMBER(LTRIM(‘0x01000020’,‘0x’),‘xxxxxxxx’))
ASfile_no,
DBMS_UTILITY.data_block_address_block(
TO_NUMBER(LTRIM(‘0x01000020’,‘0x’),‘xxxxxxxx’))
ASblock_no
FROMDUAL;
FILE_NOBLOCK_NO
432
这个和我们之前在rowid里查看的一致。
刚才说了，在32这个块里保存了14条row记录，我们继续查询一下我们where=7521那条：
tab 0, row 13, @0x1d61
SYS@anqing1(rac1)> select DBMS_UTILITY.data_block_address_block (TO_NUMBER (LTRIM (‘0x1d61’, ‘0x’),‘xxxxxxxx’)) as block_no from dual;
BLOCK_NO
7521
我们查询的那条row记录在最后一条。
彻底搞懂uba地址转换
uba = Address of the last undo block used + Sequence + Last Entry in UNDO record map
SQL> update t set owner=‘killdb.com’ where object_id=555;
已更新 1 行。
SQL> select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec,START_SCN from v$transaction;
XIDUSN XIDSLOT XIDSQN UBABLK UBAFIL UBAREC START_SCN
6 15 916 366 3 6 968248
SQL> select dbms_rowid.rowid_block_number(rowid) blk# from t where object_id=555;
BLK#
491
SQL> select distinct dbms_rowid.rowid_relative_fno(rowid) file# from t;
FILE#
5
what’s xidsqn and ubasqn in v$transaction?
原创 IT综合 作者：warehouse 时间：2008-07-27 22:46:05 2782 0
不容易理解的2个字段，Jonathan Lewis的解释
[@more@]
Jonathan Lewis
The slots in the transaction table in the undo segment header
are continuously being reused. Each time a slot it re-used,
its sequence number (xidsqn) goes up.
Similarly, undo blocks are re-used (though it takes more
time to get round to re-using them than transaction slots).
Each time an undo block is “newed” and reused, its
sequence number UBASQN goes up.
alter system dump datafile 5 block 491;
Block header dump: 0x014001eb
Object id on Block? Y
seg/obj: 0x15a1d csc: 0x00.ef4ce itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x14001e8 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C— 0 scn 0x0000.000ec619
0x02 0x0006.00f.00000394 0x00c0016e.0085.06 ---- 1 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
xid: 0x0006.00f.00000394 即 6 15 916
bdba: 0x014001eb 10进制20972011 ，即dba地址，rdba 5,491
data_block_dump,data header at 0xfffffd7ffdd34a7c
tsiz: 0x1f80
hsiz: 0x474
pbl: 0xfffffd7ffdd34a7c
76543210
flag=--------
ntab=1
nrow=561
frre=-1
fsbo=0x474
fseo=0x799
avsp=0x330
tosp=0x330
0xe:pti[0] nrow=561 offs=0 0-560行
0x12:pri[0] offs=0x1f76
0x14:pri[1] offs=0x1f6c
0x16:pri[2] offs=0x1f62
0x18:pri[3] offs=0x1f58
0x1a:pri[4] offs=0x1f4e
0x1c:pri[5] offs=0x1f44
0x1e:pri[6] offs=0x1f3a
0x20:pri[7] offs=0x1f30
0x22:pri[8] offs=0x1f26
0x24:pri[9] offs=0x1f1c
0x26:pri[10] offs=0x1f12
0x28:pri[11] offs=0x1f08
0x02 0x0006.00f.00000394 0x00c0016e.0085.06 ---- 1 fsc 0x0000.00000000
select dbms_utility.data_block_address_file(to_number(substr(uba, 3, 8), ‘xxxxxxxxxxxx’)) as undo_file#,
dbms_utility.data_block_address_block(to_number(substr(uba, 3, 8), ‘xxxxxxxxxxxx’)) as undo_block,
to_number(substr(uba, 12, 4),
‘xxxxxxxxxxxx’) undo_sequence,
to_number(substr(uba, 17, 2),
‘xxxxxxxxxxxx’) undo_record
from (select ‘0x00c0016e.0085.06’ uba from dual);
UNDO_FILE# UNDO_BLOCK UNDO_SEQUENCE UNDO_RECORD
3 366 133 6
uba = Address of the last undo block used + Sequence + Last Entry in UNDO record map
dump undo header:
SQL> select name from v$rollname where usn=6;
NAME
_SYSSMU6_112126372’;
Undo Segment: _SYSSMU6_112126372$ (6)
Extent Control Header
Extent Header:: spare1: 0 spare2: 0 #extents: 4 #blocks: 151
last map 0x00000000 #maps: 0 offset: 4080
Highwater:: 0x00c0016f ext#: 2 blk#: 7 ext size: 8
#blocks in seg. hdr’s freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 2
Unlocked
Map Header:: next 0x00000000 #extents: 4 obj#: 0 flag: 0x40000000
Extent Map
0x00c000d1 length: 7
0x00c000d8 length: 8
0x00c00168 length: 8
0x00c00900 length: 128
Retention Table
Extent Number:0 Commit Time: 1408809628
Extent Number:1 Commit Time: 1408809628
Extent Number:2 Commit Time: 1408846934
Extent Number:3 Commit Time: 1408809628
TRN CTL:: seq: 0x0085 chd: 0x000b ctl: 0x0012 inc: 0x00000000 nfb: 0x0001
mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x00c0016f.0085.01 scn: 0x0000.000ebd47
Version: 0x01
FREE BLOCK POOL::
uba: 0x00c0016f.0085.01 ext: 0x2 spc: 0x1f84
uba: 0x00000000.0085.02 ext: 0x2 spc: 0x1ee2
uba: 0x00000000.0085.1f ext: 0x2 spc: 0x978
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
0x0a 9 0x00 0x0394 0x0012 0x0000.000ec5eb 0x00c0016d 0x0000.000.00000000 0x00000001 0x00000000 1408814848
0x0b 9 0x00 0x0393 0x0015 0x0000.000ebd59 0x00c0016c 0x0000.000.00000000 0x00000001 0x00000000 1408810230
0x0c 9 0x00 0x0394 0x0006 0x0000.000ec5e8 0x00c0016e 0x0000.000.00000000 0x00000001 0x00000000 1408814848
0x0d 9 0x00 0x0393 0x0019 0x0000.000ec24e 0x00c0016c 0x0000.000.00000000 0x00000001 0x00000000 1408813250
0x0e 9 0x00 0x0394 0x000c 0x0000.000ec5ae 0x00c0016d 0x0000.000.00000000 0x00000001 0x00000000 1408814750
0x0f 10 0x80 0x0394 0x0002 0x0000.000ec638 0x00c0016e 0x0000.000.00000000 0x00000001 0x00000000 0
usn: 6
sp1:0x00000000 sp2:0x00000000 sp3:0x00000000 sp4:0x00000000
sp5:0x00000000 sp6:0x00000000 sp7:0x00000000 sp8:0x00000000
EXT TRN TBL::
这里的dba=0x00c0016e和uba=0x00c0016e.0085.06 一致。
Oracle RBA(Redo Byte Address) 说明
一. RBA 说明
之前整理了一个rdba和dba 的说明文档，参考：
Oracle rdba和 dba 说明
http://blog.csdn.net/tianlesoftware/article/details/6529346
在MOS的文档：Question About RBA (Redo Block Address) [ID 759966.1] 有对RBA的说明。
A"Redo Block Address" (RBA) describes a physical location within aredo log file.
RBA 由以下三部分组成：
（1）the log file sequence number (4 bytes)
（2）the log file block number (4 bytes)
（3）the byte offset into the block at which the redo record starts (2bytes)
如：RBA[0x19.2.10] 代表 Log squence25, Block number 2 with byte offset 16.
注意这里的格式：都是16进行。
Redo 与checkpoint 关系很大，当设置log_checkpoints_to_alert 参数为true后，checkpoint 发生时会写入alert log里。
如：
SYS@dave2(db2)>alter system set log_checkpoints_to_alert=true scope=both;
System altered.
SYS@dave2(db2)> alter system switchlogfile;
System altered.
log里的信息如下：
Thu Aug 18 18:46:18 2011
ALTER SYSTEM SETlog_checkpoints_to_alert=TRUE SCOPE=BOTH;
Thu Aug 18 18:48:07 2011
Beginning log switch checkpoint up to RBA[0xa.2.10], SCN: 2148380730
Thread 1 advanced to log sequence 10
Current log# 3 seq# 10 mem# 0: /u01/app/oracle/oradata/dave2/redo03.log
Thu Aug 18 18:52:54 2011
Completed checkpoint up to RBA [0xa.2.10],SCN: 2148380730
Thu Aug 18 19:08:59 2011
Incremental checkpoint up to RBA[0xa.736.0], current log tail at RBA [0xa.80a.0]
Thu Aug 18 19:39:01 2011
Incremental checkpoint up to RBA[0xa.c96.0], current log tail at RBA [0xa.d7c.0]
…
Fri Aug 19 07:10:00 2011
Incremental checkpoint up to RBA[0xa.1688a.0], current log tail at RBA [0xb.8328.0]
Fri Aug 19 07:11:04 2011
Completed checkpoint up to RBA [0xb.2.10],SCN: 2148412930
Fri Aug 19 07:40:02 2011
Incremental checkpoint up to RBA[0xb.896c.0], current log tail at RBA [0xb.89c1.0]
根据我们前面的说明，RBA 由三部分组成，第一部分是sequence number，即序列号。
在我们贴出来的log里有2个日志序列：RBA [0xa.1688a.0] 和 RBA [0xb.896c.0]。
SYS@dave2(db2)> select to_number(‘a’,‘xx’)from dual;
TO_NUMBER(‘A’,‘XX’)
10
SYS@dave2(db2)> selectto_number(‘b’,‘xxx’) from dual;
TO_NUMBER(‘B’,‘XXX’)
11
a 对应的是日志序列10，b 对应的是日志序列11.
SYS@dave2(db2)> select sequence# fromv$archived_log;
SEQUENCE#
1
2
3
4
5
6
7
8
9
10
SYS@dave2(db2)> select sequence#,group#,status from v$log;
SEQUENCE# GROUP# STATUS
11 1 CURRENT
9 2 INACTIVE
10 3 INACTIVE
根据这个查询结果判断10是已经归档的，11是正在使用的current redo log file。
第二部分就是我们对应的block：
SYS@dave2(db2)> selectto_number(‘1688a’,‘xxxxxxxxx’) from dual;
TO_NUMBER(‘1688A’,‘XXXXXXXXX’)
92298
在之前的blog：
Oracle Dump Redo Log File 说明
http://blog.csdn.net/tianlesoftware/article/details/6670962
里提到，可以根据RBA 来dump redo block：
格式如下：
ALTER SYSTEM DUMP LOGFILE ‘filename’ RBAMIN seqno .blockno RBA MAX seqno . blockno;
示例：
SYS@anqing1(rac1)> alter system dumplogfile’+data/anqing/onlinelog/redo02.log’ rbamin 121 10458 rba max125 10472;
System altered.
这里的参数：seqno .blockno; 就是我们RBA中的前2部分。
二. RBA 使用
RBAs are not necessarily unique within their thread, because the log file sequencenumber may be reset to 1 in all threads if a database is opened with theRESETLOGS option.
RBAs are used in the following important ways.
With respect to a dirty block in thebuffer cache, the low RBA is theaddress of the redo for the first change that was applied to the block since itwas last clean, and the high RBA is the address of the redo for the most recent change to have been applied to theblock.
Dirty buffers are maintained on the buffer cache checkpoint queues in low RBA order. The checkpoint RBA is the point up to which DBWn has written buffers from thecheckpoint queues if incremental checkpointing is enabled -- otherwise it isthe RBA of last full thread checkpoint.
The checkpoint RBA is copied into the checkpoint progress record of the controlfile by the checkpoint heartbeat once every 3 seconds. In stance recovery, when needed, begins from the checkpoint RBA recorded in thecontrolfile.
The target RBA is the point up to which DBWnshould seek to advance the checkpoint RBA to satisfy instance recovery objectives.
The on-disk RBA is the point up to which LGWR has flushed the redo thread to the online log files. DBWn may not write ablock for which the high RBA is beyond the on-disk RBA. Otherwise transactionrecovery (rollback) would not be possible, because the redo needed to undo achange is always in the same redo record as the redo for the change itself.
The term sync RBA is sometimes used to referto the point up to which LGWR is required to sync the thread. However, this isnot a full RBA -- only a redo block number is used at this point.
在上面提到的几种RBA 值可以通过XBH,和 XKCCRT 视图进行查看：
The low and high RBAs for dirty buffers can be seen in XTARGETRBA. The incremental checkpointRBA and the on-disk RBA can also be seen in XKCCRT.
