IDS Forum

Re: Claim unused/deleted space from tables

Posted By: Art Kagel
Date: Wednesday, 17 September 2008, at 5:47 p.m.

In Response To: Claim unused/deleted space from tables (Reyna.Sabina)

There are three basic methods for reorganizing a table - one of the side
effects of such a reorg is releasing unused space within the table for other
tables to use:

1. Use dbschema -ss to save the table's DDL, UNLOAD or otherwise export

the data from the table to a flat file, drop or rename the table (drop all

indexes if renaming), recreate the table empty, LOAD or otherwise reinsert

the exported data.

2. Cluster an index: ALTER INDEX indexname TO CLUSTER;

3. ALTER FRAGMENT ON TABLE tablename INIT IN <dbspace | fragmentation

expression>; - the single dbspace or fragmentation expression can be

identical to the table's current dbspace/fragmentation or it can be

different.

Often these are preceded by ALTERing the table's NEXT size to reduce the
number of extents in the reorganized table.

Method 3 tends to be the fastest, uses little additional disk space (the
size of the tables largest extent or newly ALTERED NEXT size) during the
reorg bu requires lots of logical log space. Method 2 requires the fewest
resources but is the middle ground on speed. Method one, the most popular
since it's obvious, MAY use the most resources and the speed depends on what
tool you use to extract and restore the data - the dbaccess UNLOAD & LOAD
verbs are the slowest and use the most resources onpload is probably the
fastest and my ul utility is somewhere in between.

Art

On Wed, Sep 17, 2008 at 5:09 PM, Reyna.Sabina <Reyna.Sabina@noaa.gov> wrote:

> Hello,
>
> After deleting many rows from a table, the amount of free
> space in the 'dbscpace' remains same after the delete.
>
> I have read that unloading and loading the table is
> a way to recover the space.
>
> Does anyone know of another method to claim the unused/deleted
> space from the tables in the 'dbspace'.
>
> Thanks!
>
> Reyna
> _________________________________________________________
> Reyna Sabina Phone: (305) 361-4324
> NOAA/AOML/PHOD Fax: (305) 361-4392
> 4301 Rickenbacker Causeway Email: Reyna.Sabina@noaa.gov
> Miami, FL 33149-1087
>
> "Things do not get better by being left alone."
>
> -Winston Churchill
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)

Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.

Messages In This Thread

• Claim unused/deleted space from tables
  Reyna.Sabina -- Wednesday, 17 September 2008, at 5:09 p.m.
  • Re: Claim unused/deleted space from tables
    Art Kagel -- Wednesday, 17 September 2008, at 5:47 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.