How to Diagnose and Resolve ORA-1628, ORA-1630, ORA-1631, ORA-1632, ORA-1684 : max # of extents reached in < OBJECT >

How to Solve the Following Errors About MAX # EXTENTS REACHED
-------------------------------------------------------------
 
A. What you will need to address the issue: 
    
        * db_block_size 
        * max_extents for the object 
        * name of the tablespace in which the object resides 
 
B. How to Obtain the Information: 
 
   db_block_size 
   ============= 
 
   1. SQL> SELECT value FROM v$parameter WHERE  name='db_block_size'; 
 
   2. From the init<sid>.ora: 
 
      Look for the parameter db_block_size.  
      If it is not referenced, check for the parameter ifile and the name of the 
      configuration file it points to. 
 
   max_extent(s) and tablespace of the object 
   ==========================================
 
    1. Rollback Segment 
       ================ 
 
       SELECT  max_extents , tablespace_name
       FROM    dba_rollback_segs 
       WHERE   segment_name = '<rollback segment name>'; 
 
    2. Temporary Segment 
       ================= 
 
       SELECT  max_extents , tablespace_name
       FROM    dba_tablespaces 
       WHERE   tablespace_name = '<tablespace name>'; 
 
    3. Table Segment 
       ============= 
 
       SELECT  max_extents , tablespace_name
       FROM    dba_tables 
       WHERE   table_name = '<table name>' AND owner = '<owner>'; 
 
    4. Index Segment 
       ============= 
 
       SELECT  max_extents , tablespace_name
       FROM    dba_indexes 
       WHERE   index_name = '<index name>' and owner = '<owner>'; 
 
    5. Table Partition Segment
       =======================

       SELECT  max_extent , tablespace_name
       FROM    dba_tab_partitions 
       WHERE   partition_name = '<partition name>' and owner = '<owner>'; 
       
   Look at the chart below (the chart contains common but not all extent sizes).  
   Compare the max_extents value from the object to the value from the chart for 
   the db_block_size. 
 
   db_block_size      Max Extents 
   --------------     ----------- 
    2K                        121 
    4K                        249 
    8K                        505 
   16K                       1017 
   32K                       2041

   The Oracle 7.3 feature Unlimited Extents removes the constraint of an upper 
   limit to the number of possible extents. See Note: 1015356.4 for further
   information. 
 

C. Possible Solutions 
 
   1. If the value of the object's max_extents is less than the max_extents for 
      the db_block_size, alter the objects STORAGE clause to the maximum size. 
      If the database version is 7.3.X or higher, you can specify unlimited.
 
      Rollback Segment 
      ================ 
 
      ALTER ROLLBACK SEGMENT rollback_segment STORAGE ( MAXEXTENTS integer); 
 
      Temporary Segment 
      ================= 
 
      ALTER TABLESPACE tablespace DEFAULT STORAGE ( MAXEXTENTS integer); 
  
      Table Segment 
      ============= 
   
      ALTER TABLE table STORAGE ( MAXEXTENTS integer); 
 
      Index Segment 
      ============= 
 
      ALTER INDEX index STORAGE ( MAXEXTENTS integer); 
 
      Table Partition Segment
      =======================
 
      ALTER TABLE table MODIFY PARTITION partition STORAGE (MAXEXTENTS integer);
 
 
   2.  If the max_extents for the object is greater than or equal to the 
       max_extents for the db_block_size, the solution depends on the type of 
       object and the database version. 
       Regardless of the current max_extents for the object, the maximum number
       of extents for any object is based on the db_block_size prior to 
       version 7.3. 
 
 
      a. Version 7.3 and later: 
      
         You can set the MAXEXTENTS storage parameter value to UNLIMITED for any
         object. 
 
         Rollback Segment 
         ================ 

         For rollback segments other than SYSTEM, use

         ALTER ROLLBACK SEGMENT rollback_segment STORAGE ( MAXEXTENTS UNLIMITED); 
          
         For SYSTEM rollback segment, refer to Note:157175.1

         Temporary Segment 
         ================= 
 
         ALTER TABLESPACE tablespace DEFAULT STORAGE ( MAXEXTENTS UNLIMITED); 
 
         Table Segment 
         ============= 
 
         ALTER TABLE table STORAGE ( MAXEXTENTS UNLIMITED); 
 
         Index Segment 
         ============= 
 
         ALTER INDEX index STORAGE ( MAXEXTENTS UNLIMITED); 
  
         Table Partition Segment
         =======================

         ALTER TABLE table MODIFY PARTITION partition STORAGE (MAXEXTENTS UNLIMITED); 


      b. Prior to Version 7.3: 
 
         To solve max extents error, the goal is to store more data in a fixed
         number of extents. This is done by recreating the object with a larger 
         initial and next extent size.
         With a larger extent size, more data can be stored in the same number 
         of extents. 
 
         Rollback Segments 
         ================= 
 
         To adjust the extent size on a rollback segment, the segment must be 
         dropped and recreated. 
 
         1.  Alter the rollback segment offline : 
 
             SQL> ALTER ROLLBACK SEGMENT rollback_segment OFFLINE; 
 
         2.  Wait until the rollback segment status changes to offline. 
             When a rollback segment is altered offline, no new transaction is 
             allowed in the segment and it does not get the status offline until 
             active transactions within the segment have been committed or 
             rolled back. The status can be obtained by performing the following :

             SELECT segment_id
             FROM   dba_rollback_segs
             WHERE  segment_name = '<rollback segment name>';

             SELECT status
             FROM   v$rollstat
             WHERE  usn=<segment_id from above>; 
 
         3.  Drop the rollback segment : 
 
             DROP ROLLBACK SEGMENT rollback_segment; 
 
         4.  Create the rollback segment with a larger initial and next extent size :
 
             CREATE ROLLBACK SEGMENT rollback_segment TABLESPACE tablespace 
             STORAGE (INITIAL integer < |K|M> NEXT integer < |K|M>); 
 
 
         Temporary Segments 
         ================== 
 
         A temporary segment takes the default storage of the tablespace in 
         which it is created. 
         Alter the initial and next extent size for the tablespace. 
 
         ALTER TABLESPACE tablespace 
         DEFAULT STORAGE (INITIAL integer < |K|M> NEXT integer < |K|M>); 
 
 
         Table Segment 
         ============= 
 
         The table must be recreated. 
 
         1.  Export the table. 
 
             $exp username/password FILE=filename TABLES=(table) 
 
         2.  If possible rename the table, otherwise drop it. 
             
             RENAME tablename TO new; 
             DROP TABLE tablename; 

         3.  Import the table. 

             $imp username/password FILE=filename FULL=Y 
 
         4.  Alter the storage clause for the table. 
 
             ALTER TABLE table STORAGE ( NEXT integer < |K|M>); 
 
         Index Segment 
         ============= 
 
         Drop and recreate the index with a larger initial and next extent size.

         DROP INDEX index; 
  
         CREATE INDEX index ON tablename (columnname)
         STORAGE (INITIAL integer < |K|M> NEXT integer < |K|M>);