alter table: 'lost' extents

Hi, everybody, I've just found a very unpleasant problem with 9.21uc4 I have a huge table that was altered (column added) when it was big enough. 'Alter table' was 'in-place' - just because the operation went very fast. After that, a lot of inserts were made, and old records were not updated. Later, the table was dropped. When I made an analysis of disk extents with 'oncheck -pe', I was surprised: a lot of extents (actually, containing all the 'before alter table' records) were not released by the 'drop table'!!! Is it a known bug? Is it fixed in 9.40? Is there way to return extents to the free page pool? ------------------------------------------ Alexey Sonkin

Alexey Sonkin wrote > I've just found a very unpleasant problem with 9.21uc4 > > I have a huge table that was altered (column added) when it > was big enough. > 'Alter table' was 'in-place' - just because the operation > went very fast. > After that, a lot of inserts were made, and old records were > not updated. > > Later, the table was dropped. > > When I made an analysis of disk extents with 'oncheck -pe', I > was surprised: > a lot of extents (actually, containing all the 'before alter > table' records) were not released by the 'drop table'!!! > > Is it a known bug? > Is it fixed in 9.40? > Is there way to return extents to the free page pool? As far as I am aware,when doing an in place alter, only the new/changed columns are moved to new extents. The existing data stays in the existing extents until each row is amended. Perhaps a dummy update on every row will do the trick. Colin Bull ________________________________________________________________________ This email has been scanned for all known viruses by the MessageLabs Email Security System. ________________________________________________________________________