ORA-604、1632: Max. Extents (505) On Index SYS.I_OBJ2 Reached -1500033.1-CSDN博客

ORA-00604 and ORA-01632: Max. Extents (505) On Index SYS.I_OBJ2 Reached When Trying To Drop Tables. [ID 1500033.1]

In this Document

Applies to:

Oracle Server - Enterprise Edition - Version 10.2.0.1 to 11.2.0.3 [Release 10.2 to 11.2]
Information in this document applies to any platform.

Goal

Got the error ORA-00604 and ORA-01632: Max. Extents (505) on index SYS.I_OBJ2 during drop table operation.

Alter index SYS.I_OBJ storage (maxextents unlimited) produces the following error:
ORA-00701: object necessary for warmstarting database cannot be altered

Fix

If a database is migrated from a previous release with a poor default storage clause on the system tablespace, then it is possible to get dictionary objects with bad storage clauses which can encounter 'max # extents reached' errors.

See Bug:1275356 for more details of problem scenario.

eg: Any of the following errors on a dictionary object.
ORA-1631 "max # extents (%s) reached in table %s.%s"
ORA-1632 "max # extents (%s) reached in index %s.%s"
ORA-1656 "max # extents 505 reached in cluster sys.c_obj#"

The problem with these errors is that attempts to raise MAXEXTENTS results in an ORA-701 error on a number of objects.
eg: alter cluster c_obj# storage(maxextents unlimited);

ORA-701:object necessary for warm starting database cannot be altered

Regarding this issue there are 2 options:

A) supported option is to recreate the database using expdp/create/impdp with or without TTS option (TTS would take less time due to no physical import of data/creation of indexes). This is a known option and TTS recreation can be found in document: Note:733824.1

B) unsupported option does consist of an update of the DD (DataDictionary) which requires careful testing. This is an undocumented/unsupported option which does consist of an update of the DD (DataDictionary)

a. Check if "ALTER .. MAXEXTENTS UNLIMITED" raises and ORA-701 or not

b. If so get the header FILE and BLOCK of the segment
(Note that you need the RELATIVE FILE# not the absolute FILE#)

eg: SELECT RELATIVE_FNO, HEADER_BLOCK FROM DBA_SEGMENTS
WHERE WNER='SYS' AND SEGMENT_NAME='&OBJECT_WITH_ERROR';
Example
~~~~~~~~

SELECT RELATIVE_FNO, HEADER_BLOCK FROM DBA_SEGMENTS
WHERE WNER='SYS' AND SEGMENT_NAME='I_OBJ4'; 2
RELATIVE_FNO HEADER_BLOCK
------------ ------------
1 360

c. Patch the SEG$ entry for this segment to set a higher MAXEXTENTS
value
(Note: We are constraining SEG$ updates to TS#=0 for the
relevant relative FILE#)

STEPS:
1. shutdown the database (cleanly)
2. Take a full backup as a fallback option
3. Run these steps taking great care to reference the correct
FILE# and BLOCK# (these will be in TS#=0)

startup restrict
commit;
set transaction use rollback segment system;
update seg$ set maxexts=10000, extsize=100
where ts#=0 and file#= and block#=;

Example
~~~~~~~~
set transaction use rollback segment system;
update seg$ set maxexts=10000, extsize=100
where ts#=0 and file#=1and block#=360;

This should return one and only one row.
IF NOT ROLLBACK NOW AND ABORT
If it returns ONE row then commit as below

commit;

shutdown abort;

4. Startup again after the above abort

5. select max_extents from DBA_SEGMENTS where SEGMENT_NAME='I_OBJ4';

MAX_EXTENTS
-----------
10000

This should show MAX_EXTENTS=10000

6. Shutdown cleanly and take another fresh backup.