Informix Error -136

-136 ISAM error: no more extents.

The database server needs to add an extent to a table but cannot do so. Either not enough disk space is available in the dbspace, or the table has been given the maximum number of extents that is allowed, or the maximum number of pages has been reached. The database server administrator can determine the cause as follows:

1. Determine the tblspace number for the table. It is the value in the partnum column of the systables table for this table.

2. Convert the tblspace number to hexadecimal and extract its most-significant 2 digits (the high-order byte). This chunk number indicates where the table resides.

3. Use the onstat utility -t option to find out disk usage for this table. Note particularly the values reported for npages (disk pages available), nused (disk pages used), and nextns (number of extents).

If nused is less than npages, and nextns is large, the table may have too many extents. The upper limit of extents per table depends on the page size of the dbspace it is in, as well as how much space is consumed by other entries on its tblspace page. An estimate of the maximum allowed number of extents is the page size in bytes, minus 150 bytes of overhead, divided by eight. Thus a table in a 2K page size dbspace is limited to roughly (2,048 - 150) / 8 = 237 extents, but the actual limit may be lower if other entries on its tblspace page reduce the space available for extent entries. Larger page sizes allow correspondingly higher numbers of extents.

If there are too many extents, reallocate the table using fewer, larger extents, or in a dbspace with a larger page size. To reallocate the table, unload it to a flat file, then drop the table. Re-create the table, specifying a first-extent size sufficient to hold all its current data and a next-extent size between one-fourth and one-sixteenth its current size. Then reload the data into the table.

If nextns is small or the difference between npages and nused is less than the size of the next-extent size for the table, not enough disk space is available in the dbspace where the table resides. Use the chunk number from step 2 and the ON-Monitor or ON-Monitor Chunks display to determine the dbspace, then add a new chunk to that dbspace.

If nused is close to 0xFFFFFF (16,777,215), the maximum number of pages has been reached for the table. Reallocate the table in a dbspace with a larger page size as described above.

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-136 means the engine needs to add another extent to a table and can't — and the official text is explicit that this can be any one of three distinct conditions, each requiring a different fix. Diagnosing which one actually applies is the whole task; guessing wrong wastes real effort (reallocating a table that didn't need it, or adding space that wasn't the actual constraint).

Insufficient disk space in the dbspace. The same underlying condition as -131 — the dbspace genuinely doesn't have room, whether from raw exhaustion or fragmentation.
The table has reached its maximum allowed number of extents. Independent of available space, tables have a ceiling on how many extents they can be split into — a table that grew in many small increments over a long period can accumulate a large extent count well before it runs out of actual disk space.
The table has reached the maximum page limit for its dbspace's page size — 0xFFFFFF (16,777,215 pages). This is an absolute ceiling tied to the page size in use, hit by tables that have simply grown very large, regardless of how much free space remains elsewhere.

Solutions / Resolution

Work through the three conditions in the order the official diagnostic guidance implies:

Check whether used pages are well below available pages, but the extent count is high. This pattern means the table's extents have been too small and too numerous — reallocate the table with fewer, larger extents (a table rebuild with adjusted extent sizing) to consolidate.
Check whether the dbspace genuinely has no available space remaining. If so, add a new chunk to the dbspace — the same fix as -131's straightforward case.
Check whether used pages approach 0xFFFFFF (16.7 million pages). If so, the table has hit the absolute page-count ceiling for its current page size — the fix is reallocating the table into a dbspace configured with a larger page size, since no amount of added space in the current dbspace raises this particular ceiling.

Examples

Too many small extents, plenty of actual space

oncheck -pe mydb:orders
-- reveals 400 extents, most quite small, while onstat -d shows the
-- dbspace is nowhere near full

The fix here is reallocating orders with a smaller number of larger extents — adding more disk space wouldn't help, since space isn't actually the constraint.

Genuine space exhaustion

onstat -d
-- the dbspace hosting this table shows 0 free pages

Add a new chunk to the dbspace — this is the -131-style fix, appropriate when the diagnosis actually points at space, not extent count or page-count ceiling.

Hitting the absolute page-count ceiling

-- Table's used page count is approaching 16,777,215 in a 2KB-page dbspace

No amount of added space or extent reallocation resolves this — the table needs to move to a dbspace with a larger page size, which raises the total page-count ceiling for the same logical data volume.

Diagnostic Checks

Check the table's extent count and sizes:
```
oncheck -pe <database>:<table>
```
Check dbspace free space:
```
onstat -d
```
Compare used page count against the 0xFFFFFF ceiling (16,777,215) for the table's current page size — this is the check that distinguishes the third scenario from the other two.
Review the table's extent-size growth history if the extent-count scenario seems likely — a pattern of many small extents rather than a few large ones confirms it.

Related Errors / Related Topics

-131 — "ISAM error: no free disk space." One of -136's three possible causes directly overlaps with -131's condition — genuine dbspace exhaustion.
-132 — "ISAM error: rowsize too big." Another hard structural limit in the same general category — both call for identifying which specific ceiling was hit before deciding on a fix, rather than assuming the first plausible explanation.

Diagnose which of the three conditions actually applies before acting — adding disk space to a dbspace that's actually suffering from extent-count fragmentation, or vice versa, wastes effort without fixing the real constraint.