| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Space util question
On Tue, 23 Jun 1998 22:01:47 -0400, "MMK Productions"
<nospam.mmkprod_at_bellatlantic.net> wrote:
>Example:
>
>10M table. Expands to 20M, 100,000 rows. DELETE 50,00 rows. Query
>DBA_SEGMENTS. BYTES = 20M. Why?
>
>Do I have to be on 7.3 and use "deallocate unused" to have the right value
>in BYTES?
>
>
Check out the Oracle7 Server Concepts manual to fully understand the "why". "In general, the extents of a segment do not return to the tablespace until you drop the object whose data is stored in the segment (using a DROP TABLE or DROP CLUSTER statement)".
From the Oracle7 Administrator's Guide, in the secion Guidelines for Managing Schema Objects:
<snip>
Deallocating Space
This section describes aspects of deallocating unused space, and
includes the following topics:
Viewing the High Water Mark
Issuing Space Deallocation Statements
It is not uncommon to allocate space to a segment, only to find out
later that it is not being used. For example, you may set PCTINCREASE
to a high value, which could create a large extent that is only
partially used. Or you could explicitly overallocate space by issuing
the ALTER TABLE ALLOCATE EXTENT statement. If you find that you have
unused or overallocated space, you can release it so that the unused
space can be used by other segments.
Viewing the High Water Mark
Prior to deallocation, you can use the DBMS_SPACE package, which
contains a procedure (UNUSED_SPACE) that returns information about the
position of the high water mark and the amount of unused space in a
segment.
Within a segment, the high water mark indicates the amount of used
space. You cannot release space below the high water mark (even if
there is no data in the space you wish to deallocate). However, if the
segment is completely empty, you can release space using the TRUNCATE
DROP STORAGE statement.
Issuing Space Deallocation Statements
The following statements deallocate unused space in a segment (table,
index or cluster). The KEEP clause is optional.
ALTER TABLE table DEALLOCATE UNUSED KEEP integer; ALTER INDEX index DEALLOCATE UNUSED KEEP integer; ALTER CLUSTER cluster DEALLOCATE UNUSED KEEP integer; When you explicitly identify an amount of unused space to KEEP, this space is retained while the remaining unused space is deallocated. If the remaining number of extents becomes smaller than MINEXTENTS, the MINEXTENTS value changes to reflect the new number. If the initial extent becomes smaller, the INITIAL value changes to reflect the new size of the initial extent.
If you do not specify the KEEP clause, all unused space (everything above the high water mark) is deallocated, as long as the size of the initial extent and MINEXTENTS are preserved. Thus, even if the high water mark occurs within the MINEXTENTS boundary, MINEXTENTS remains and the initial extent size is not reduced.
See Also: For details on the syntax and options associated with deallocating unused space, see the Oracle7 Server SQL Reference.
You can verify that deallocated space is freed by looking at the DBA_FREE_SPACE view. For more information on this view, see the Oracle7 Server Reference.
For details about the DBMS_SPACE package, see page 16 - 25.
Deallocating Space: Examples
This section includes various space deallocation scenarios. Prior to
reading it, you should familiarize yourself with the
ALTER...DEALLOCATE UNUSED statements in the Oracle7 Server SQL
Reference.
Example 1
Table dquon consists of three extents (see figure Figure 10 - 3). The first extent is 10K, the second is 20K, and the third is 30K. The high water mark is in the middle of the second extent, and there is 40K of unused space. The following statement deallocates all unused space, leaving table dquon with two remaining extents. The third extent disappears, and the second extent size is 10K.
ALTER TABLE dquon DEALLOCATE UNUSED;
</snip>
Thanks!
Joel
Joel R. Kallman Oracle Government, Education, & Health
Columbus, OH http://govt.us.oracle.com jkallman@us.oracle.com http://www.oracle.com
 |
 |