ORA-01632: max # extents (505) reached in index SYS.I_OBJ5

ORA-01632: max # extents (505) reached in index SYS.I_OBJ5

Error:
During Upgrade from 12.1 to 12.2 got the following error:
ORA-01632: max # extents (505) reached in index SYS.I_OBJ5

Solution:
1. Check the MAXEXTENTS size of object

select max_extents from DBA_SEGMENTS where SEGMENT_NAME='I_OBJ5';

2. Take the backup of database.

3. Tried to increase the extent size to unlimited.

ALTER index SYS.I_OBJ5 MAXEXTENTS UNLIMITED;

4. If 3 point is worked then its good otherwise you need to upgrade with another method like Transport tablespace or logical backup.

5. Donot recommended must take cold backup, If you want to try unsupported method, take cold backup and change the seg$ increase the maxextent size.

-- find the file no and header block
SELECT RELATIVE_FNO, HEADER_BLOCK FROM DBA_SEGMENTS
WHERE OWNER='SYS' AND SEGMENT_NAME='I_OBJ5';

--Start db in restrict mode

--use file# and block# from first query and set maxexts parameter, it may cause dictionary corruption (take cold backup).
update seg$ set maxexts=10000, extsize=100
where ts#=0 and file#= and block#=;

--Check again the changed value
select max_extents from DBA_SEGMENTS where SEGMENT_NAME='I_OBJ5';

--Start the database with startup force.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.