Oracle maxextents: how to tell what it's set to

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

Description:
I know that we need to prevent tables from going over the maximum number of extents allowed and I know that I can see how many extents each table has in the util a/17/12 (or util a/17/11) functions ... but how can I tell what the maxextents *is* in my database? And how is it determined?

Resolution:
This SQL query will give you current maximum number of extents allowed for each table in a particular library:
SQL-USM50> select table_name, max_extents from all_tables where owner like 'USM%';
and this will give you the value for one specific table:
SQL-USM50> select max_extents from all_tables where table_name = 'Z30';
Unlimited will show as "2147483645". Note: Oracle 9 always has unlimited extents, so this problem should not occur in Oracle 9. (ALEPH 16.02 requires Oracle 9, so that means that this problem should not occur in v16.02-up.)

The value of maxextents can be specified in the create-table script; if not present, it defaults to the value for the tablespace (as defined in the create-tablespace script); if not specified there, it defaults to the system-defined value, which seems for our sites to almost always be 505. The create-table script for each table is in the ./alephm/sql_tab directory. (For instance, there is a z30_create.sql script.) There is an aleph_tablespaces.sql in the same directory which defines our default "ts0" and "ts1" tablespaces.
As distributed, these scripts do not have any maxextents value which means that they are defaulting to 505.

• Article last edited: 10/8/2013