Oracle error : ORA-01632 : Max extents reached in index

Error: ORA-01632
Text: max # extents (%s) reached in index %s.%s
—————————————————————
Cause: An index tried to extend past maxextents
Action: If maxextents is less than the system max, raise it.
Otherwise, you must recreate with larger initial, next
or pctincrease params.

EXPLANATION:

The max extents error occurs when the current number of extents equals the
maximum number of extents in the max_extents parameter for the object or the
maximum number of extents allowable for the db_block_size , whichever is
smaller, and an attempt is made to add another extent. Max extents can be set
for an object using the MAXEXTENTS option of the storage clause.
These kind of problems can be avoided by pro-actively monitoring the object
sizes that may reach their max_extents one day.

Diagnostic Steps:

1- Run the following script to identify the indexes with extent problems:

SQL>select segment_name, owner, extents, max_extents
            from dba_segments
            where segment_type = ‘INDEX’ and
            (extents +1) >= max_extents;

2- Retrieve the tablespace_name of the index:
SQL>select tablespace_name
          from dba_indexes
         where index_name=%s.%s;

3- Retrieve the table_name from the index;
SQL>select table_name
         from dba_indexes
        where index_name=%s.%s;

4-Alter the tablespace to unlimited maxextent
SQL>alter tablespace <tablespace_name> default storage (maxextents unlimited);

5-Alter the related table’s maxextent to unlimited as well.
SQL>alter table <owner.table_name> storage(maxextents unlimited);

6-Alter the index to unlimited maxextent
SQL>alter index <owner.index_name> storage (maxextents unlimited);

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s