chap9_6.html

Oracle资料大集合

<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>

<font size="+1"><B>9.6	How do I匘eallocate unused space in a segment?</B></font>
<p>
<B>Problem</B>
<p>
I suspect that the initial extent for some of my database objects is much too large. Also, the value of PCTINCREASE for some tables is so large it抯 likely that Oracle8 has allocated much more space than necessary to these tables. How can I find out whether there is unused space allocated to some objects in the database? If there is unused space, how can I transform the wasted space back into free space?
<p>
<B>Technique</B>
<p>
The UNUSED_SPACE procedure in the DBMS_SPACE package reveals some segment information absent from the Oracle8 data dictionary. Table 9.4 summarizes this procedure抯 input and output parameters. This How-To uses this procedure to return the total space and the unused space allocated to a segment.
<p>
<table cellpadding=3 cellspacing=3 border=0 bgcolor="cdcdcd">
<TR>
<td>Parameter Name</td>
<td>Parameter Type</td>
<td>Parameter Description</td>
</TR>

<TR><td>segment_owner</td>
<td>IN</td>
<td>Owner of the target segment</td></TR>

<TR><td>segment_name</td>
<td>IN</td>
<td>Name of the target segment</td></TR>

<TR><td>segment_type</td>
<td>IN</td>
<td>Type of the segment to be analyzed (TABLE, INDEX, or CLUSTER)</td></TR>

<TR><td>total_blocks</td>
<td>OUT</td>
<td>Total number of blocks in the segment</td></TR>

<TR><td>total_bytes</td>
<td>OUT</td>
<td>Total number of bytes in the segment</td></TR>
<TR>
<td>unused_blocks</td>
<td>OUT</td>
<td>Number of blocks not used in the segment</td></TR>

<TR><td>unused_bytes</td>
<td>OUT</td>
<td>Number of bytes not used in the segment</td></TR>

<TR><td>last_used_extent_file_id</td>
<td>OUT</td>
<td>File ID of the last extent that contains data</td></TR>

<TR><td>last_used_extent_block_id</td>
<td>OUT</td>
<td>Block ID of the last extent containing data</td></TR>

<TR><td>last_used_block</td>
<td>OUT</td>
<td>Last block within the last extent containing data</td></TR>

<TR>
<td>partition_name</td>
<td>IN</td>
<td>Partition name of the target segment</td></TR>
</table>

<p>

The ALTER TABLE statement with the DEALLOCATE option can dynamically return unused space in database segments. Use this statement after the UNUSED_SPACE procedure indicates that some segments are larger than necessary.
<p>
Steps
<p>
<B>1.</B>	Run SQL*Plus and connect as the WAITE user. Use the START command to load and execute the script CHP9_9.SQL. The script appears in List-ing 9.3.
<p>
Listing 9.3 The script CHP9_9.SQL creates a nested table
<p>
<code><b>2  DROP TABLE dept09;
<p>
3  DROP TYPE emp_t_table09;
<p>
4  DROP TYPE emp_t09;
<p><BR>
5  CREATE TYPE emp_t09 AS OBJECT
<p>
6  (empno  number(4),
<p>
7   ename  varchar2(10)
<p>
8  );
<p>
9  CREATE TYPE emp_t_table09 AS TABLE OF emp_t09;
<p>
10  CREATE TABLE dept09
<p>
11  (
<p>
12   deptno   number(2),
<p>
13   dname    varchar2(14),
<p>
14   emps     emp_t_table09
<p>
15  )
<p>
16  NESTED TABLE emps STORE AS dept_emps09
<p>
17  STORAGE
<p>
18    (INITIAL 50k
<p>
19     NEXT    50k
<p>
20    );
<p>
21  INSERT INTO dept09 VALUES
<p>
22  (
<p>
23   1, 'IT', emp_t_table09(emp_t09(1, 'JOE')
<p>
24                          emp_t09(2, 'JANE'))
<p>
25  );
<p></b></code>
This script creates a table called DEPT09 that contains a nested table column called EMPS (read more about nested tables in Chapter 16). These tables are similar to the EMP and DEPT tables traditionally found in the schema SCOTT.
<p>
The preceding DDL contains a new twist on these common tables using the nested table capability of the Oracle8 server. Lines 5 through 7 create an object type called EMP_T09 to describe an employee. Line 9 creates a table of the newly defined EMP_T09 type. Lines 10 through 20 create the DEPT09 table, which stores departments and, using the nested table, the employees who work in each department. Line 16 specifies the storage table to hold the nested table EMPS. This is an Oracle8 segment that users cannot query directly, although it does have its own physical storage parameters. In this case, the storage clause in lines 17 through 20 specifies 50KB for the size of the initial and next extents of the table DEPT. The storage table, EMPS09, inherits the default storage parameters for the tablespace. In lines 21 through 25, a single record is inserted into table DEPT for the Information Technology department. This department currently has two employees: Joe and Jane.
<p>
<B>2.</B>	Although it is unlikely that the one now in the DEPT09 table has consumed all 50KB of the initial extent, the data dictionary does not contain information indicating how much space that single row requires. The script CHP9_10.SQL creates a stored procedure called SEGSPACE, which can reveal how much segment space is used and how much is free in any data segment; the script appears in Listing 9.4. Use the START command to load and execute the CHP9_10.SQL script.
<p>
Listing 9.4 The script CHP9_10.SQL creates the SEGSPACE procedure
<p>
<code><b>1  CREATE OR REPLACE PROCEDURE segspace<BR>
<p>
2     (owner IN  varchar2,
<p>
3      name  IN  varchar2,
<p>
4      type  IN  varchar2
<p>
5     )
<p>
6  IS
<p>
7  freespace  number(10);
<p>
8  totalspace number(10);
<p>
9  dummyn     number(10);
<p>
10  using      number(10);
<p>
11  needs      number(10);
<p>
12  BEGIN
<p>
13  dbms_space.unused_space(segment_owner => owner,
<p>
14 segment_name => name,
<p>
15 segment_type => type,
<p>
16 total_blocks => dummyn,
<p>
17 total_bytes => totalspace,
<p>
18 unused_blocks => dummyn,
<p>
19 unused_bytes => freespace,
<p>
20 last_used_extent_file_id => dummyn,
<p>
21 last_used_extent_block_id => dummyn,
<p>
22 last_used_block => dummyn,
<p>
23 partition_name => null);
<p>
24  using := round(totalspace/1024, 0);
<p>
25  needs := round((totalspace-freespace)/1024, 0);
<p>
26  dbms_output.put_line ('object ' || name || ' is allocated
<p>
' ||
<p>
27 to_char(using) || 'k and is using ' ||
<p>
28 to_char(needs) || 'k');
<p>
29  END;</b></code>
<p>
SEGSPACE calls the UNUSED_SPACE procedure in lines 13 through 23, using named notation to pass the appropriate parameters. Lines 24 through 26 convert the values returned by the UNUSED_SPACE procedure from bytes to kilobytes. The PUT_LINE procedure that appears in lines 26 through 28 displays the object抯 name, how much space the object is using, and how much space is free.
<p>
<B>3.</B>	Use the START command to load and execute the SEGSPACE procedure for the database objects you want to query for space consumption. <A HREF="image/09oht14.gif" TARGET="_blank">Figure 9.14</A> contains the results of running script CHP9_11.SQL, which runs the SEGSPACE procedure against the DEPT09 table and the DEPT_EMPS09 storage table.
<p>
Notice that the type passed to the SEGSPACE procedure is TABLE for the DEPT_EMPS09 storage table and for the DEPT09 table.
<p>
<B>4.</B>	Use the ALTER TABLESPACE DEALLOCATE UNUSED command to free allocated but unused space. The script CHP9_12.SQL contains two invocations of this command, one for the DEPT09 table and one for the DEPT_EMPS09 storage table. The script and its output appear in <A HREF="image/09oht15.gif" TARGET="_blank">Figure 9.15</A>.
<p>
<B>5.</B>	Run the UNUSED_SPACE procedure again for the resized tables. <A HREF="image/09oht16.gif" TARGET="_blank">Figure 9.16</A> contains the results of the CHP9_11.SQL script when it runs after the deallocation operation in Step 4.
<p>
<B>How It Works</B>
<p>
Steps 2 and 3 summarize creation and use of the SEGSPACE procedure, which acts as a parameterized interface to the UNUSED_SPACE procedure in the Oracle-supplied package_DBMS_SPACE. Step 4 deallocates unused segment space using the ALTER TABLESPACE command with the DEALLOCATE option.
<p>
<B>Comments</B>
<p>
The alternative to the DEALLOCATE OPTION of the ALTER_TABLESPACE command is to re-create the table with a more appropriate storage clause or to create tablespaces with storage clauses tailored for the tablespace抯 planned contents. These are admirable goals, but even the most watchful DBA occasionally sizes a table incorrectly. The technique in the How-To provides a much more straightforward method of deallocating unused segment space if it becomes necessary to adjust a table in this way. Note that without the KEEP option in Step 4, the size of the initial extent or space allocated via the number of minimum extents is preserved throughout a deallocation attempt.
<p>

<!-- </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>