chap4_8.html

Oracle资料大集合

<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.groupwareandcollaboration1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Groupware &amp; Collaboration</b></font></td>
</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.contentmanagement.html"><font face="verdana,helvetica" size="1" color="000000"><b>Content Management</b></font></td>
</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.productivityapplications1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Productivity Applications</b></font></td>
</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.hardware1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Hardware</b></font></td>
</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.funandgames1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Fun &amp; Games</b></font></td>
</tr>
</table>


<!-- <IMG SRC="/images/leftnav.gif" WIDTH=111 HEIGHT=404 ALT="nav" border="0"  ISMAP USEMAP="#leftnav"> -->
<p>
<a href="http://www.earthweb.com/jobs/" target=new><IMG SRC="../../../images/dice-105x30.gif" WIDTH=105 HEIGHT=30 ALT="EarthWeb Direct" border="0"></a>
<a href="http://www.earthwebdirect.com/" target=new><IMG SRC="../../../images/earthwebdirect.gif" WIDTH=105 HEIGHT=30 ALT="EarthWeb Direct" border="0"></a>
<a href="http://www.fatbrain.com/home.html?from=UUX592" target=new><IMG SRC="../../../images/fatbrain.gif" WIDTH=105 HEIGHT=30 ALT="Fatbrain" border="0"></a>
<a href="http://auctions.earthweb.com" target=new><IMG SRC="../../../images/auctions_105x30.gif" WIDTH=105 HEIGHT=30 ALT="Auctions" border="0"></a>
<a href="http://www.supportsource.com" target=new><IMG SRC="../../../images/ss2000sneak2.gif" WIDTH=105 HEIGHT=30 ALT="Support Source Answers" border="0"></a>
<P>

<b><font face="verdana,helvetica" size="2">EarthWeb sites</FONT></b><br>
  <FONT SIZE="1">
  <a href="http://www.crossnodes.com"><font face="verdana,helvetica" size="1" color="000000">Crossnodes</font></a><br>
  <a href="http://www.datamation.com"><font face="verdana,helvetica" size="1" color="000000">Datamation</font></a><br>
  <a href="http://www.developer.com"><font face="verdana,helvetica" size="1" color="000000">Developer.com</FONT></A><br>
  <a href="http://www.dice.com"><font face="verdana,helvetica" size="1" color="000000">DICE</font></a><br>
  <a href="http://www.earthweb.com"><font face="verdana,helvetica" size="1" color="000000">EarthWeb.com</font></a><br>
  <a href="http://www.earthwebdirect.com"><font face="verdana,helvetica" size="1" color="000000">EarthWeb Direct</font></a><br>
  <a href="http://www.erphub.com"><font face="verdana,helvetica" size="1" color="000000">ERP Hub</font></a><br>
  <a href="http://www.gamelan.com"><font face="verdana,helvetica" size="1" color="000000">Gamelan</font></a><br>
  <a href="http://www.gocertify.com"><font face="verdana,helvetica" size="1" color="000000">GoCertify.com</font></a><br>
  <a href="http://www.htmlgoodies.com"><font face="verdana,helvetica" size="1" color="000000">HTMLGoodies</font></a><br>
  <a href="http://www.intranetjournal.com"><font face="verdana,helvetica" size="1" color="000000">Intranet Journal</font></a><br>
  <a href="http://www.itknowledge.com"><font face="verdana,helvetica" size="1" color="000000">IT Knowledge</font></a><br>
  <a href="http://www.itlibrary.com"><font face="verdana,helvetica" size="1" color="000000">IT Library</font></a><br>
  <a href="http://www.javagoodies.com"><font face="verdana,helvetica" size="1" color="000000">JavaGoodies</font></a><br>
  <a href="http://www.jars.com"><font face="verdana,helvetica" size="1" color="000000">JARS</font></a><br>
  <a href="http://www.javascripts.com"><font face="verdana,helvetica" size="1" color="000000">JavaScripts.com</font></a><br>
  <a href="http://www.opensourceit.com"><font face="verdana,helvetica" size="1" color="000000">open source IT</font></a><br>
  <a href="http://www.roadcoders.com"><font face="verdana,helvetica" size="1" color="000000">RoadCoders</font></a><br>
  <a href="http://www.Y2Kinfo.com"><font face="verdana,helvetica" size="1" color="000000">Y2K Info</font></a>
</FONT>

</td>



<!--End Left Navigation column -->
<!-- spacer column  -->

<TD width="40">
<img src="../../../images/dotclear.gif" WIDTH="40" HEIGHT="1" border=0>
</TD>

<!--Begin Content Column -->

<TD VALIGN="TOP" width="500">

<P>




<blockquote>

<b><font size="+1">4.8	How do I匢nterpret the format of ROWID?<p></font></b>
<p>
<B>Problem</B>
<p>
I need to use the ROWID pseudo-column for fast retrieval and location of records. I also need to determine where my actual data resides within a tablespace. How do I interpret the format of ROWID?
<p>
<B>Technique</B>
<p>
Use the various DBMS_ROWID functions to extract information about the ROWID pseudo-column. There are two ROWID formats: extended and restricted. Restricted is used mostly to be backward-compatible with Oracle7. The extended format takes advantage of new Oracle8 features. Table 4.2 shows the components of the extended ROWID format, and Table 4.3 shows the components of the restricted ROWID format.

<p>

Table 4.2 Description of the extended format for ROWID (10 bytes)

<p>
<table border=0 cellpadding=3  cellspacing=3 bgcolor="cdcdcd">

<TR>
<td>Portion</td>
<td>Description</td>
</TR>

<TR>
<td>oooooo</td>
<td>6-character object number (base-64 encoded)</td>
</TR>

<TR>
<td>fff</td>
<td>3-character relative file number (base-64 encoded)</td>
</TR>

<TR>
<td>bbbbbb</td>
<td>6-character block number in file (base-64 encoded)</td>
</TR>

<TR>
<td>sss</td>
<td>3-character slot within the block (base-64 encoded)Table 4.3 Description of the restricted format for ROWID (6 bytes)</td>
</TR>
</table>
<p>
Table 4.3 Description of the restricted format for ROWID (6 bytes)
<p>
<table bgcolor="cdcdcd" cellpadding=3 cellspacing=3>
<TR>
<td>Portion</td>
<td>Description</td>
</TR>

<TR>
<td>bbbbbbbb</td>
<td>6-character block (hexidecimal encoded)</td>
</TR>

<TR>
<td>rrrr</td>
<td>4-character row number in block (hexidecimal encoded)</td>
</TR>

<TR>
<td>ffff</td>
<td>4-character file number (hexidecimal encoded)</td>
</TR>
</table>

<p>

<B>Steps</B>

<p>
1.	Connect to SQL*Plus as the Waite account. If you have not already done so, create the EMPLOYEE04 table by running the CHP4_1.SQL. Also, if you have not already done so, insert sample data into the EMPLOYEE04 table by running the CHP4_18.SQL script.
<p>
The DBMS_ROWID package has several procedures and functions to interpret the ROWIDs of records. Table 4.4 shows the DBMS_ROWID functions, most of which will be explained in this How-To.
<p>
<table bgcolor="cdcdcd" cellpadding=3 cellspacing=3>
<TR><td>Function</td>
<td>Description</td></TR>

<TR><td>ROWID_BLOCK_NUMBER</td>
<td>The block number that contains the record; 1 = extended ROWID.</td></TR>

<TR><td>ROWID_OBJECT</td>
<td>The object number of the object that contains the record.</td></TR>

<TR><td>ROWID_RELATIVE_FNO</td>
<td>The relative file number contains the record.</td></TR>

<TR><td>ROWID_ROW_NUMBER</td>
<td>The row number of the record.</td></TR>

<TR><td>ROWID_TO_ABSOLUTE_FNO</td>
<td>The absolute file number; you need to input rowid_val, schema, and object; the absolute file number is returned.</td></TR>

<TR><td>ROWID_TO_EXTENDED</td>
<td>Converts the ROWID from restricted to extended; you need to input restr_rowid, schema, object; the extended number is returned.</td></TR>

<TR><td>ROWID_TO_RESTRICTED</td>
<td>Converts the ROWID from extended to restricted.</td></TR>

<TR><td>ROWID_TYPE</td>
<td>0 = restricted ROWID, 1 = extended.</td></TR>
<TR>
<td>ROWID_VERIFY</td>
<td>Verifies if the ROWID can be extended; 0 = can be converted to extended format; 1= cannot be converted to extended format. </td></TR>
</table>
<p>
2.	Run the CHP4_26.SQL script to retrieve ROWID information on each record.

<code><b>
<p>
SQL> GET CHP4_26.sql
<p>
1  select
<p>
2     b.object_name||