4 Replies Latest reply: Aug 5, 2010 12:34 PM by Mark D Powell RSS

    ORA-01631: max # extents (4096)


      I received below ORA error for one of my prod table, would like to in which scenario we get the below error and how to investigate why it has occured.

      ORA-01631: max # extents (4096) reached in table DWHSE.F_APS_ORDERS

        • 1. Re: ORA-01631: max # extents (4096)
          - 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
          select count(*)
          from dba_extents
          where owner='DWHSE'
          and segment_name='F_APS_ORDERS';
          - 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.
          - Solution
          alter table DWHSE.F_APS_ORDERS storage (maxextents unlimited);

          Sybrand Bakker
          Senior Oracle DBA
          • 2. Re: ORA-01631: max # extents (4096)
            Thankyou very much!!
            • 3. Re: ORA-01631: max # extents (4096)
              Tim Boles
              Mark the question as answered please.

              • 4. Re: ORA-01631: max # extents (4096)
                Mark D Powell
                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.

                Good luck.
                HTH -- Mark D Powell --