On Tue, 02 Mar 2004 19:54:04 -0500, Chris Bullivant wrote:

As someone mentioned, the empty space may be the result of deleted rows that
were once there or the result of having created the table with a large
initial extent size. To release the space, if the extent size is small (see
dbschema ... -ss or myschema output or the ti_fextsiz ) you can do:

ALTER FRAGMENT ON mytable INIT IN <dbspacename or fragment expression>;

Which will release all but the initial extent for use by other tables. If the
initial extent size is large you must drop the table and recreate it with a
smaller initial extent size.

Art S. Kagel

> I'm working on a project to reclaim space from inside a DB. I've written a
> simple script:
>
> select tabname, ti_nrows, ti_nptotal
> from sysmaster:systabinfo i,sysmaster:systabnames n where i.ti_partnum =
> n.partnum
> and tabname not like 'sys%'
> order by 2
>
> Here is a few rows of output:
>
> tabname ti_nrows ti_nptotal
>
> Table 1 0 8 Table 2 0
> 1424 Table 3 0 8 Table 4 0 8
> Table 5 0 55296 Table 6
> 0 8 Table 7 0 8 Table
> 8 0 4000
>
> What is happening here? How can a table have 0 rows in it and yet claim 55K
> pages? Am I interpreting the output correctly.
>
> Is it possible to reclaim this space? I tried "alter index ... to cluster"
> on one of them but it made no difference. I could drop and re-create the
> table but I'm concerned that I won't get all the indexes, grants, triggers,
> etc. exactly right. Also, there are some other tables with only a few rows
> but are using lots of pages so the drop/recreate wouldn't work for them.
>
> What I'd like is a command that says "Collect Garbage". Does something like
> this exist?
>
> Thanks for any ideas,
> Chris
> P.S. Is there something on the Web that describes the tables and meanings of
> the sysmaster DB?