This content has been marked as final. Show 4 replies
- Someone has set the max # number of extents to 4096
- When a table needs to store more records, sooner or later the table will extend itself, and allocate a new extent
- As a maximum # of extents has been set, and the table keeps growing sooner or later the maximum will be reached
- The following select will return 4096
- In versions of Oracle beyond 7.3 there is no gain in setting a maximum # of extents, as the architectural reasons for this have been removed.
alter table DWHSE.F_APS_ORDERS storage (maxextents unlimited);
Senior Oracle DBA
To hit a maximum extents error on a table you pretty much have to be using dictionary tablespace management. The maximum number of extents defaults based on the block size. If your Oracle version is 8.1 and up all your tablespaces should have been rebuilt as locally managed tablespaces using either auto-allocate or uniform extents. In the case where you have a newer version you should consider creating new tablespaces and moving the objects into them so you can take advantage of modern extent management features.
If you are running an older release then just take some time to review your object allocation layout to see if for space managment purposes you need to do any re-arranging or storage parameter modification. We used our own version of auto-allocate under 7.x where we had 4 different extent sizes for smaller objects where each size was an even multiple of the smaller sizes. For moderate size and large tables we had uniform extent sizes.
HTH -- Mark D Powell --