chap9_1.html

来自「Oracle资料大集合」· HTML 代码 · 共 424 行 · 第 1/2 页

<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"><B>9.1	How do I匘etect objects close to maximum extents?</B></font></b>
<p>
<B>Problem</B>
<p>
I have created some database objects and have either accepted the default for the number of maximum extents or imposed a maximum extent limit of my own. I want to query my database periodically to determine whether any of these objects are close to a point where the maximum extent limit will not permit them to grow. How can I determine which tables and indexes are close to their limit for maximum extents?
<p>
<B>Technique</B>
<p>
The Oracle8 server allocates storage space in extents. The data definition language (DDL) commands that create tables and indexes can contain a storage clause specifying the size of the first extent, the next extent, and the amount by which to increase extents subsequent to the second extent. There is controversy regarding the advisability of confining database objects to a single extent. One philosophy holds that this practice minimizes input/output processing. Another school of thought maintains that the alleged I/O performance improvements don抰 justify the effort of confining objects to a single extent. In any case, there is no doubt that maximally extended objects are likely to cause problems.
The Oracle8 data dictionary view DBA_SEGMENTS contains the informa-tion you need to discover whether any database objects are close to the MAXEXTENTS limit. Table 9.1 contains a description of this data dictionary object.
<p>
<table cellpadding=3 cellspacing=3 BGCOLOR="cdcdcd">
<TR><td>Column</td>
<td>Column Description</td></TR>

<TR><td>OWNER</td>
<td>User name of the segment owner</td></TR>
<TR>
<td>SEGMENT_NAME</td>
<td>Name, if any, of the segment</td>
</TR>
<TR>
<td>SEGMENT_TYPE</td>
<td>Type of segment: INDEX PARTITION, TABLE PARTITION, TABLE, CLUSTER, INDEX, ROLLBACK, DEFERRED ROLLBACK, TEMPORARY, or CACHE</td></TR>

<TR><td>TABLESPACE_NAME</td>
<td>Name of the tablespace containing the q segment</td></TR>

<TR><td>HEADER_FILE</td>
<td>ID of the file containing the segment header</td></TR>

<TR><td>HEADER_BLOCK</td>
<td>ID of the block containing the segment header</td></TR>

<TR><td>BYTES</td>
<td>Size, in bytes, of the segment</td></TR>

<TR><td>BLOCKS</td>
<td>Size, in Oracle blocks, of the segment</td></TR>

<TR><td>EXTENTS</td>
<td>Number of extents allocated to the segment</td></TR>

<TR><td>INITIAL_EXTENT</td>
<td>Size, in bytes, of the initial extent of the segment</td></TR>

<TR><td>NEXT_EXTENT</td>
<td>Size, in bytes, of the next extent to be allocated to the segment</td></TR>

<TR><td>MIN_EXTENTS</td>
<td>Minimum number of extents allowed in the segment</td></TR>

<TR><td>MAX_EXTENTS</td>
<td>Maximum number of extents allowed in the segment</td></TR>

<TR><td>PCT_INCREASE</td>
<td>Percent by which to increase the size of the next extent to be allocated</td></TR>

<TR><td>FREELISTS</td>
<td>Number of process freelists allocated to the segment</td></TR>

<TR><td>FREELIST_GROUPS</td>
<td>Number of freelist groups allocated to the segment</td></TR>

<TR><td>RELATIVE_FNO</td>
<td>Relative file number of the segment header</td></TR>

<TR><td>PARTITION_NAME</td>
<td>Object partition name (Set to NULL for non-partitioned objects)</td></TR>
</TABLE>

<p>

<B>Steps</B>
<p>

<B>1.</B>	Run SQL*Plus, connect as the WAITE user, and use the SQL*Plus START command to run the script contained in CHP9_1.SQL. This script creates a partitioned table (see How-To 4.10 for an in-depth explanation of object partitioning in Oracle8) and is shown in <A HREF="image/09oht01.gif">Figure 9.1</A>.

<p>

This example is contrived; it is unlikely that any developer would specify 3 for the maximum number of
extents for the first partition of this table. Here, it will serve to illustrate the next step.

<p>

<B>2.</B>	While still connected to SQL*Plus as the WAITE user, use the SQL*Plus START command to run the script contained in CHP9_2.SQL. The script will prompt for the substitution variable EXTLIMIT and will produce a report listing any database objects that are within EXTLIMIT extents of their maximum extent limit. The script and its output appear in Fig-ure 9.2.
<p>
This script queries the DBA_SEGMENTS data dictionary view. Experienced Oracle users will likely have familiarity with a script like this one that they developed for use with previous versions of Oracle. The one main difference is the inclusion of the PARTITION_NAME column that supports Oracle8 object partitioning.
<p>
<B>How It Works</B>
<p>
The DBA_SEGMENTS view contains everything you need to produce a meaningful report of all database objects that are close to maximum extension. Notice in <A HREF="image/09oht02.gif" TARGET="_blank">Figure 9.2</A>, for example, that a table and an index that user SCOTT owns, a CACHE object that user SYS owns, and the first partition of the EMP09 table created in Step 1 are all within five extents of their maximum extent limits.
<p>
<B>Comments</B>
<p>
Beginning with Oracle server release 7.3, DBAs can specify that objects grow through an unlimited number of extents. Tables and indexes featuring this option can grow until they consume all the available space in the tablespace. The script in step 2 will not fail, even in the presence of the UNLIMITED value for the MAXEXTENTS parameter. The value stored in the MAXEXTENTS attribute in the DBA_TABLES view in this case is still a number: 2147483645.


<!-- </td>

</td>
</tr>
</table> -->
</td>

</td>
</tr>
</table>

<!-- begin footer information -->

<MAP NAME="footer">
<AREA SHAPE=RECT COORDS="0,0,62,26" HREF="http://www.itlibrary.com/">
<AREA SHAPE=RECT COORDS="62,0,135,26" HREF="http://www.developer.com/about/">
<AREA SHAPE=RECT COORDS="135,0,199,26" HREF="http://www.itlibrary.com/search/">
<AREA SHAPE=RECT COORDS="200,0,274,26" HREF="http://www.itlibrary.com/subscribe/">
<AREA SHAPE=RECT COORDS="275,0,335,25" HREF="http://www.developer.com/contact/adinfo.html">
<AREA SHAPE=RECT COORDS="335,0,417,25" HREF="http://www.developer.com/contact/">
<AREA SHAPE=RECT COORDS="418,0,467,26" HREF="http://www.developer.com/about/faq.html">
</MAP>

<P>
<table width="640" cellpadding="0" cellspacing="0" border="0">
<tr>
<TD WIDTH="130"></TD>
<td width="468">
<IMG SRC="../../../images/footer/footerfile.gif" ALT="footer nav" width="467" height="26" BORDER="0" usemap="#footer" ismap>
</td>
</tr>
<tr>
<TD WIDTH="130"></TD>
<td width="468"> 
<font face=arial,helvetica size="1"> Use of this site is subject certain <a href="http://www.developer.com/legal/">Terms &amp; Conditions.</a><br>
Copyright (c) 1996-1999 <A HREF="http://www.earthweb.com/">EarthWeb, Inc.</A>.  All rights reserved.  Reproduction in whole or in part in any form or medium without express written permission of EarthWeb is prohibited.
<a href="http://www.earthweb.com/corporate/privacy.html">Please read our privacy policy for details.</a>
</td>
</tr>
</table>

</BODY>
</HTML>