By Kevin Loney
Learn to take advantage of Oracle8's new extended RowID structure to get more information and let your database support more files.
Oracle customers know by now that one of the many benefits Oracle8 brings to the enterprise is increased capacity. In this excerpt from his new book, the Oracle8 DBA Handbook (1998, Oracle Press-Osborne/McGraw Hill), Kevin Loney describes how to make use of Oracle8's new RowID format in order to take advantage of this increase. --Editors
As of Oracle8, the formats for RowIDs have changed. Therefore, if you previously used the RowID values in Oracle7, you now need to become familiar with the structure and use of the new RowID format. This article explains how to use the functions that are provided by Oracle8 to retrieve information from the new extended RowID format.
The new format for the RowIDs is called the extended format. The format used in earlier versions of Oracle Data Server is now referred to as the restricted format. The new format for the RowID allows Oracle8 to support more files per database and more blocks per file.
As a result of the changes, Oracle Data Server can now store much more information--petabytes of data instead of terabytes. (One petabyte equals 1,024 terabytes; a terabyte, in turn, is the equivalent of 1,024 gigabytes.)
The new functions provided by Oracle8 allow you to obtain more information from the RowID than was ever available before, such as the object ID value. If you are more comfortable using the previous format of the RowID, used in Oracle7, you have the option of using a function called ROWID_TO_RESTRICTED to display the new RowID values in the old format from within Oracle8.
This article demonstrates the functions provided in the DBMS_ROWID package. The full package description is located in the dbmsutil.sql file, usually located in the /rdbms/admin subdirectory under the Oracle home directory.
You can select the extended RowID values the same way you selected the restricted RowID values in Oracle7:
select RowID from NUMBERTEST where Rownum=1;
ROWID ------------------ AAAArfABQAAAALBAAA
This query selects the RowID of a single row in the NUMBERTEST table. Although the extended RowID does not immediately appear to provide useful information, if you use the functions provided in Oracle8, you will find that the extended RowID values contain data that is not provided via the restricted RowID.
You can also use the extended RowID to determine the Object_ID value for the object to which the row belongs.
With the restricted RowID format, determining the object ID for an object previously required comparing the RowID's block- and file-number values with entries in DBA_EXTENTS. Oracle8's extended RowID format greatly simplifies the translation of RowIDs to object ID values.
To see the Object_ID value for an object based on its extended RowID in Oracle8, use the ROWID_ OBJECT function within the DBMS_ROWID package. The following query selects a row from the NUMBERTEST table and uses its extended RowID value as the input to the ROWID_OBJECT function:
select DBMS_ROWID.ROWID_OBJECT(RowID) from NUMBERTEST where RowNum=1;
DBMS_ROWID.ROWID_OBJECT(ROWID)
------------------------------
2783
The ROWID_OBJECT function assists you in identifying an object if you have nothing but the RowID value of a row within the object. If you already have the Object_ID value, you can query the object's name from DBA_OBJECTS as follows:
select Object_Name from DBA_OBJECTS where Object_ID = 2783;
OBJECT_NAME -------------------------- NUMBERTEST
You can employ the ROWID_TO_RESTRICTED function of the DBMS_ROWID package in order to display extended RowID values in restricted format. For instance, you may want to make use of the restricted format if you are already comfortable using the Oracle7 format for RowID values.
The ROWID_TO_RESTRICTED function has two parameters, namely the extended RowID value and the conversion type. Use 0 for the conversion type value, as shown in the following code sample. This example converts the RowID values from the NUMBERTEST table to restricted format.
select DBMS_ROWID.ROWID_TO_RESTRICTED(RowID,0) from NUMBERTEST;
DBMS_ROWID.ROWID_T ------------------ 000002C1.0000.0050 000002C1.0001.0050 000002C1.0002.0050 000002C1.0003.0050 000002C1.0004.0050 000002C1.0005.0050 000002C1.0006.0050 000002C1.0007.0050
In the restricted format, the first eight bytes constitute the block number in hexadecimal. Hexadecimal 2C1 is equivalent to decimal 705, so the data is stored in block 705 of its file. The file ID--which is the relative file number and not the absolute file number (see the following section)--is shown as 0050 (also in hexadecimal). Hexadecimal 50 is equivalent to decimal 80, which means the relative file number is 80.
The middle part of the restricted RowID is the sequence number of the row within the block, starting with 0. The eight rows in the NUMBERTEST table are all stored in the same block (705).
As of Oracle8, there are two types of file numbers: relative file numbers and absolute file numbers. The expanded types of file numbers allow Oracle8 to support a greater number of files per database.
The ROWID_TO_RESTRICTED function, shown in the previous section, displays the relative file number for the file in which the rows are stored. You can also select the relative file number directly from the RowID values, using the ROWID_RELATIVE_FNO function of the DBMS_ROWID package. In the following listing, ROWID_RELATIVE_FNO returns the relative file number (in decimal) of the file in which the row is stored:
select DBMS_ROWID.ROWID_RELATIVE_FNO(RowID) from NUMBERTEST where RowNum=1;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
------------------------------------
80
You can verify the relative file number simply by querying the file information from DBA_DATA_FILES. Whenever you're querying with the new relative file numbers, make sure you use the decimal version of the RELATIVE_FNO value. The following example shows the DBA_DATA_FILES entry for the NUMBERTEST row that was queried in the preceding listing:
select File_Name, File_ID from DBA_DATA_FILES where Relative_FNO = 80;
FILE_NAME FILE_ID ---------------------------------------- ---------- /db02/oracle/CC1/users01.dbf 5
In the example, the File_ID value, 5, represents the absolute file number for the data file, whereas 80 is the relative file number.
If you want to derive the absolute file numbers from the RowID values, use the ROWID_TO_ABSOLUTE_FNO function of the DBMS_ROWID package. This function requires three inputs. They are, in order: the RowID, the name of the schema that owns the table, and the name of the table. The following code sample queries the absolute file number for the NUMBERTEST table, which is owned by a user named Talbot:
select DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO(RowID,'TALBOT','NUMBERTEST') from NUMBERTEST where RowNum=1;
DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO(ROWID,'TALBOT','NUMBERTEST')
----------------------------------------------------------
5
The absolute file number, 5, corresponds to the datafile's File_ID value in DBA_DATA_FILES, as shown in the previous query of DBA_DATA_FILES.
You can query the block number for a row directly from the RowID by using the ROWID_BLOCK_NUMBER function of the DBMS_ROWID package. As shown in the following listing, the ROWID_ BLOCK_NUMBER has one input (the RowID) and returns the block number in decimal format:
select DBMS_ROWID.ROWID_BLOCK_NUMBER(RowID) from NUMBERTEST where RowNum=1;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
705
The row is stored in block 705 of the file whose relative file number is 80. Because the NUMBERTEST table is very small and has only a single extent, you can easily verify this block number by querying DBA_EXTENTS. The following query selects the header block for the NUMBERTEST extent:
select Relative_FNO, Block_ID from DBA_EXTENTS where Segment_Name = 'NUMBERTEST';
RELATIVE_FNO BLOCK_ID
------------ --------
80 704
The header block for the table is block number 704 (rows are stored starting with the next block, which is 705). You normally query file numbers and block numbers together. The following query selects both the relative file number and the block number for a row, via function calls:
select DBMS_ROWID.ROWID_RELATIVE_FNO(RowID),
DBMS_ROWID.ROWID_BLOCK_NUMBER(RowID)
from NUMBERTEST
where RowNum=1;
Multiple rows may be stored in a single block. Oracle8 assigns a sequence number to each row within an individual block, starting out with a sequence number of 0. You are able to select the row's sequence number directly from the extended RowID values, by using the ROWID_ROW_NUMBER function within the DBMS_ROWID package.
The following listing uses the ROWID_ROW_NUMBER function to determine the sequence numbers of the rows in the NUMBERTEST table:
select DBMS_ROWID.ROWID_ROW_NUMBER(RowID) from NUMBERTEST;
DBMS_ROWID.ROWID_ROW_NUMBER(ROWID)
----------------------------------
0
1
2
3
4
5
6
7
The output shows the sequence numbers within block 705 for the rows of the NUMBERTEST table. Row sequence numbers are usually selected along with their block numbers (via ROWID_BLOCK_NUMBER) and relative file numbers (via ROWID_RELATIVE_FNO) when you are attempting to determine the exact physical location of a row.
There is no loss of functionality in RowIDs in the move from Oracle7 to Oracle8 and its increased capacity. When you first migrate, you may find it simplest to use the ROWID_TO_RESTRICTED function to display the data in Oracle7's familiar format. However, you should learn to take advantage of Oracle8's new DBMS_ROWID functions to extract the wealth of data now available from the extended RowID values.
Oracle provides a number of additional procedures and functions in the DBMS_ROWID package. These options are useful if you develop applications that use ROWID as a datatype, in which case you will need to convert that data when you undertake the migration to Oracle8.
See the dbmsutil.sql file in the /rdbms/admin directory under the Oracle software home directory for further details on these additional options.
Kevin Loney is the author of theOracle8 DBA Handbook and the coauthor of Oracle8: The Complete Reference and Advanced Oracle Tuning and Administration, all published by Oracle Press-Osborne/McGraw-Hill.
This article is an excerpt from the Oracle8 DBA Handbook (ISBN: 0-07-882406-0, � 1998 by the McGraw-Hill Companies). Adapted with permission from Osborne/McGraw-Hill. For more information, call +800.272.0900 (within the United States) or visit www.osborne.com or www.oramag.com. For more information about the full line of Oracle Press books from Osborne/McGraw-Hill, visit www.oramag.com.
Copyright © 1994, 1995, 1996, 1997 & 1998 Oracle Corporation. All Rights Reserved.