recover the space of indexes

hi all, I have decided to dettache my indexes from my tables for some tables. so now my tables are in dbspace1 and the indexes in the dbspace2. my question is : is there any solution to recover from the space in dbspace1 who was used by the detached indexes without droping and recreating the tabls. thanks for any suggestion ! ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com

samir BADAOUI wrote: > > hi all, > I have decided to dettache my indexes from my tables for some tables. > so now my tables are in dbspace1 and the indexes in the dbspace2. > my question is : > is there any solution to recover from the space in dbspace1 who was used by > the detached indexes without droping and recreating the tabls. > thanks for any suggestion ! ALTER FRAGMENT FOR TABLE mytable INIT IN dbspace1; This will rebuild the table, compressing any fragmentation and releasing unused pages if the EXTENT SIZE is small enough (the new table will take up AT LEAST EXTENT SIZE K of space though). Since this is an alter the table's tabid and other attributes remain unchanged and all indexes are maintained. Art S. Kagel

I ran this, but the syntax is; ALTER FRAGMENT ON TABLE mytable INIT IN dbspace1; Use 'on', instead of 'for'. At least for a table stored completey in one dbspace without fragmentation. Art is da man... Art S. Kagel <kagel@bloomberg.net> wrote in message news:380F79D4.A46FB350@bloomberg.net... > samir BADAOUI wrote: > > > > hi all, > > I have decided to dettache my indexes from my tables for some tables. > > so now my tables are in dbspace1 and the indexes in the dbspace2. > > my question is : > > is there any solution to recover from the space in dbspace1 who was used by > > the detached indexes without droping and recreating the tabls. > > thanks for any suggestion ! > > ALTER FRAGMENT FOR TABLE mytable INIT IN dbspace1;> > This will rebuild the table, compressing any fragmentation and releasing > unused pages if the EXTENT SIZE is small enough (the new table will take > up AT LEAST EXTENT SIZE K of space though). Since this is an alter the > table's tabid and other attributes remain unchanged and all indexes are > maintained. > > Art S. Kagel

I can't keep all those ON's and FOR's straight. Usually have to look them up, sorry. Which brings up my pet peeve of the new month. Why cannot the ANSI committee decide on either ON or FOR and just stick to that for all the statements operating ON the various objects? Art S. Kagel Glenn Travis wrote: > > I ran this, but the syntax is; > > ALTER FRAGMENT ON TABLE mytable INIT IN dbspace1;> > Use 'on', instead of 'for'. At least for a table stored completey in one > dbspace without fragmentation. > > Art is da man... > > Art S. Kagel <kagel@bloomberg.net> wrote in message > news:380F79D4.A46FB350@bloomberg.net... > > samir BADAOUI wrote: > > > > > > hi all, > > > I have decided to dettache my indexes from my tables for some tables. > > > so now my tables are in dbspace1 and the indexes in the dbspace2. > > > my question is : > > > is there any solution to recover from the space in dbspace1 who was used > by > > > the detached indexes without droping and recreating the tabls. > > > thanks for any suggestion ! > > > > ALTER FRAGMENT FOR TABLE mytable INIT IN dbspace1;> > > > This will rebuild the table, compressing any fragmentation and releasing > > unused pages if the EXTENT SIZE is small enough (the new table will take > > up AT LEAST EXTENT SIZE K of space though). Since this is an alter the > > table's tabid and other attributes remain unchanged and all indexes are > > maintained. > > > > Art S. Kagel