Informix software has a hard limit of 16,777,134 for the number of data pages that can be used in a single table partition.  This is true for all versions of Informix software from 7.31 to 11.70.  Unfortunately, if you hit this limit, the message that is returned is a misleading “no more extents” error.

You can run “oncheck –pt dbname:tablename” to find out if the problem is the number of extents or pages.  If the number of used pages is 16,777,215, then you have maxed out on pages.

In order to resolve this problem, you can delete data out of the table or fragment the table into multiple partitions.  Of course, the time to think about this is before you reach 16.7 million pages, as fragmenting such a large table could mean significant down time for a critical application.  The number of allocated pages is just one of the metrics that is tracked by XTIVIA’s Virtual-DBA monitoring service to detect a potential problem before it happens.

Share This