How many pages really used?

Using Informix 7.30.UCx We have a dbspace which shows as full with 'onstat -d' but we know it isn't - it was at one point but 6,000,000 rows were deleted. However we don't know just how much room it has left as the info is saying how many pages *have* been used not *are* used. All clues most welcome. Surfer! URL: http://www.nevis-vieww.demon.co.uk Email: surfer@nevis-vieww.demon.co.uk Hopeful anti-spam: alter double 'w' to single 'w' to view site & send Email.

The problem lies in the fact that even though you may have removed 6million rows, the pages have still been allocated to the table so in theory, they are being used but in actuality, the pages are free for use but by that table only. The only way that you can free up the actual space is by dropping & re-creating the table or altering the table. oncheck -pc will show you how much space is allocated and how much isactually being used. There are some useful utils on www.iiug.org to help you out. Sean Surfer! wrote in message ... > >Using Informix 7.30.UCx > >We have a dbspace which shows as full with 'onstat -d' but we know it >isn't - it was at one point but 6,000,000 rows were deleted. However we >don't know just how much room it has left as the info is saying how many >pages *have* been used not *are* used. > >All clues most welcome. > >Surfer! >URL: http://www.nevis-vieww.demon.co.uk >Email: surfer@nevis-vieww.demon.co.uk >Hopeful anti-spam: alter double 'w' to single 'w' to view site & send Email.

Hi Surfer You should run tbcheck -pT <db>:<tab> and see how many extents your table really owns. To free the space you must unload data, run dbschema, drop the table (If you have space you can rename and drop it only if all goes fine) . Then run the script from dbschema, load data (take care with long transactions, in case use dbload). Regards Ricardo On Mon, 15 Feb 1999 22:14:03 +0000, Surfer! <surfer@nevis-view.demon.co.uk> wrote: > >Using Informix 7.30.UCx > >We have a dbspace which shows as full with 'onstat -d' but we know it >isn't - it was at one point but 6,000,000 rows were deleted. However we >don't know just how much room it has left as the info is saying how many >pages *have* been used not *are* used. > >All clues most welcome. > >Surfer! >URL: http://www.nevis-vieww.demon.co.uk >Email: surfer@nevis-vieww.demon.co.uk >Hopeful anti-spam: alter double 'w' to single 'w' to view site & send Email.

In article <36d1383e.7943592@news.telecom.pt>, NB <nuno_brito@hotmail.com> writes >Hi Surfer > >You should run tbcheck -pT <db>:<tab> and see how many extents your >table really owns. > >To free the space you must unload data, run dbschema, drop the table >(If you have space you can rename and drop it only if all goes fine) . We're not bothered about freeing up the space - it will get used as the #rows in the table grows. We *are* bothered about knowing when it's going to run out of room! > >Then run the script from dbschema, load data (take care with long >transactions, in case use dbload). > >Regards >Ricardo > > > >On Mon, 15 Feb 1999 22:14:03 +0000, Surfer! ><surfer@nevis-view.demon.co.uk> wrote: > >> >>Using Informix 7.30.UCx >> >>We have a dbspace which shows as full with 'onstat -d' but we know it >>isn't - it was at one point but 6,000,000 rows were deleted. However we >>don't know just how much room it has left as the info is saying how many >>pages *have* been used not *are* used. >> >>All clues most welcome. >> >>Surfer! >>URL: http://www.nevis-vieww.demon.co.uk >>Email: surfer@nevis-vieww.demon.co.uk >>Hopeful anti-spam: alter double 'w' to single 'w' to view site & send Email. > Surfer! URL: http://www.nevis-vieww.demon.co.uk Email: surfer@nevis-vieww.demon.co.uk Hopeful anti-spam: alter double 'w' to single 'w' to view site & send Email.

Hi! If I understood the problem correctly, running tbcheck will still report dbspace is full, since space used by deleted rows has not been 'reclaimed'. One easy way to do this 'reclamation' is to add a cluster index (or change an existing one to cluster), or do any 'alter table' that will force table rewrite. (Of course, you will free some space only as the result or table rewrite at least one extent ends up empty - so it may be a good idea to adjust 'next extent size' in advance.) However, you will have to have enough space in the same dbspace for another copy of the table to do that, so maybe Ricardo's approach is the only one. HTH. Dragi "Bonzi" Raos NB wrote in message <36d1383e.7943592@news.telecom.pt>... >Hi Surfer > >You should run tbcheck -pT <db>:<tab> and see how many extents your >table really owns. > >To free the space you must unload data, run dbschema, drop the table >(If you have space you can rename and drop it only if all goes fine) . > >Then run the script from dbschema, load data (take care with long >transactions, in case use dbload). > >Regards >Ricardo > > > >On Mon, 15 Feb 1999 22:14:03 +0000, Surfer! ><surfer@nevis-view.demon.co.uk> wrote: > >> >>Using Informix 7.30.UCx >> >>We have a dbspace which shows as full with 'onstat -d' but we know it >>isn't - it was at one point but 6,000,000 rows were deleted. However we >>don't know just how much room it has left as the info is saying how many >>pages *have* been used not *are* used. >> >>All clues most welcome. >> >>Surfer! >>URL: http://www.nevis-vieww.demon.co.uk >>Email: surfer@nevis-vieww.demon.co.uk >>Hopeful anti-spam: alter double 'w' to single 'w' to view site & send Email. >

I'm not bothered about reclaiming the free space - all I want to know is how much 'used' space is occupied by the deleted rows so that we know when we will *really* run out of space. The table had about 8million rows last time I looked and grows by quite a few every fortnight. In article <7bmpfl$tnc$1@as102.tel.hr>, Dragi Raos <draos@4mate.hr> writes >Hi! > >If I understood the problem correctly, running tbcheck will still report >dbspace is full, since space used by deleted rows has not been 'reclaimed'. >One easy way to do this 'reclamation' is to add a cluster index (or change >an existing one to cluster), or do any 'alter table' that will force table >rewrite. (Of course, you will free some space only as the result or table >rewrite at least one extent ends up empty - so it may be a good idea to >adjust 'next extent size' in advance.) However, you will have to have enough >space in the same dbspace for another copy of the table to do that, so maybe >Ricardo's approach is the only one. > >HTH. > >Dragi "Bonzi" Raos > >NB wrote in message <36d1383e.7943592@news.telecom.pt>... >>Hi Surfer >> >>You should run tbcheck -pT <db>:<tab> and see how many extents your >>table really owns. >> >>To free the space you must unload data, run dbschema, drop the table >>(If you have space you can rename and drop it only if all goes fine) . >> >>Then run the script from dbschema, load data (take care with long >>transactions, in case use dbload). >> >>Regards >>Ricardo >> >> >> >>On Mon, 15 Feb 1999 22:14:03 +0000, Surfer! >><surfer@nevis-view.demon.co.uk> wrote: >> >>> >>>Using Informix 7.30.UCx >>> >>>We have a dbspace which shows as full with 'onstat -d' but we know it >>>isn't - it was at one point but 6,000,000 rows were deleted. However we >>>don't know just how much room it has left as the info is saying how many >>>pages *have* been used not *are* used. >>> >>>All clues most welcome. >>> >>>Surfer! >>>URL: http://www.nevis-vieww.demon.co.uk >>>Email: surfer@nevis-vieww.demon.co.uk >>>Hopeful anti-spam: alter double 'w' to single 'w' to view site & send >Email. >> > > Surfer! URL: http://www.nevis-vieww.demon.co.uk Email: surfer@nevis-vieww.demon.co.uk Hopeful anti-spam: alter double 'w' to single 'w' to view site & send Email.