Claim unused/deleted space from tables

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

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.