Extents problem

Hi experts, we have a problem with extents. All our tables are re-organized, but today the instance got out of extents error for multiple databases at the same time. I have checked extends using a sysmaster query and found the following: database sysmaster; set isolation to dirty read;select n.tabname[1,18] as tabelle, n.dbsname[1,18] as datenbank, h.nextns as extents, round(npused*sh_pagesize/1024/1024 , 2) as MB, nrows from sysptnhdr h, systabnames n, sysshmvals s where h.partnum = n.partnum and h.nextns > 80 order by 3 desc tabelle datenbank extents mb nrows TBLSpace rootdbs 234 375.14 0 This is probably the reason for the error. But this is a pseudo table .... Is there a way to re-organize extents like in a user table (modify next size, alter fragment init ...) ? Our DB Version on this server is (still) 11.10FC3, running on Linux amd64. We have lots of small databases there, no lack of disk space. Any hints ? Thank you all ! Marcus Haarmann

You have to unload all of your databases, initialize the instance and recreate all of your chunks, logs, etc. and then reloaf all of the databases making sure no user databases are created in the root dbspace. BTW, since you are using 11.10 and it does not support the dbschema -c option, you may want to get my myschema utility (in the package utils2_ak). It's --infrastructure option will produce the commands you need to easily recreate the dbspace and logs. Art On Jan 9, 2012 11:03 AM, "Marcus Haarmann" <marcus.haarmann@midoco.de> wrote: > Hi experts, > > we have a problem with extents. All our tables are re-organized, but > today the instance got out of extents error > for multiple databases at the same time. > I have checked extends using a sysmaster query and found the following: > database sysmaster; > set isolation to dirty read;> select > n.tabname[1,18] as tabelle, > n.dbsname[1,18] as datenbank, > h.nextns as extents, > round(npused*sh_pagesize/1024/1024 , 2) as MB, > nrows > from sysptnhdr h, systabnames n, sysshmvals s > where h.partnum = n.partnum > and h.nextns > 80 > order by 3 desc > > tabelle datenbank extents mb > nrows > > TBLSpace rootdbs 234 375.14 > 0 > > This is probably the reason for the error. But this is a pseudo table > ..... > Is there a way to re-organize extents like in a user table (modify next > size, alter fragment init ...) ? > > Our DB Version on this server is (still) 11.10FC3, running on Linux > amd64. > We have lots of small databases there, no lack of disk space. > > Any hints ? > > Thank you all ! > Marcus Haarmann > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0015175caeb08f0dc804b61ac6e4

Please define proper TBLTBLFIRST and TBLTBLNEXT parameters if you initialize your instance. Regards. On Mon, Jan 9, 2012 at 4:20 PM, Art Kagel <art.kagel@gmail.com> wrote: > You have to unload all of your databases, initialize the instance and > recreate all of your chunks, logs, etc. and then reloaf all of the > databases making sure no user databases are created in the root dbspace. > BTW, since you are using 11.10 and it does not support the dbschema -c > option, you may want to get my myschema utility (in the package utils2_ak). > > It's --infrastructure option will produce the commands you need to easily > recreate the dbspace and logs. > > Art > On Jan 9, 2012 11:03 AM, "Marcus Haarmann" <marcus.haarmann@midoco.de> > wrote: > > > Hi experts, > > > > we have a problem with extents. All our tables are re-organized, but > > today the instance got out of extents error > > for multiple databases at the same time. > > I have checked extends using a sysmaster query and found the following: > > database sysmaster; > > set isolation to dirty read;> > select > > n.tabname[1,18] as tabelle, > > n.dbsname[1,18] as datenbank, > > h.nextns as extents, > > round(npused*sh_pagesize/1024/1024 , 2) as MB, > > nrows > > from sysptnhdr h, systabnames n, sysshmvals s > > where h.partnum = n.partnum > > and h.nextns > 80 > > order by 3 desc > > > > tabelle datenbank extents mb > > nrows > > > > TBLSpace rootdbs 234 375.14 > > 0 > > > > This is probably the reason for the error. But this is a pseudo table > > ..... > > Is there a way to re-organize extents like in a user table (modify next > > size, alter fragment init ...) ? > > > > Our DB Version on this server is (still) 11.10FC3, running on Linux > > amd64. > > We have lots of small databases there, no lack of disk space. > > > > Any hints ? > > > > Thank you all ! > > Marcus Haarmann > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --0015175caeb08f0dc804b61ac6e4 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --20cf300fae91cdf74004b61aced9