Next extent size; ORA-1653 error message

• Article Type: General
• Product: Aleph
• Product Version: 16.02

Description:
What units is Oracle using to report its extent sizes [in the UTIL-O-14-1 and especially in the ORA-1653 error message]?

Resolution:
** UTIL-O-14-1 is in Megabytes. **
Supposedly the ORA-1653 depends on how the table is defined. If it's defined in Meg, it will supposedly be meg.; if it's K, it will supposedly be Kilobytes. But my experience, like yours, has been that the value we see here often doesn't make sense. If you have Locally-managed tablespace, then the value you see will depend completely on that and whatever is in the file_list is irrelevant. This SQL will tell you what the secondary allocation is (in bytes) for each table in a particular library:
SQL> select table_name, next_extent from all_tables where owner like 'USM01%';
(If the NEXT_EXTENT column is blank, that indicates Locally-managed tablespace.) So I suggest this rule: If the NEXT_EXTENT is blank (indicating Locally-managed), consult your Oracle person. If there's a value, then presume -- regardless of the ORA-1653 message -- that the extent it's trying to get is the NEXT_EXTENT shown. In this specific case we see that the largest free extent in tablespace ts0 is 1340 M. And the largest extent size in mit01 is 250 M (262,144,000 bytes). Thus, you currently have room for 5 such extents.

• Article last edited: 10/8/2013