ORA-604、1632: Max. Extents (505) On Index SYS.I_OBJ2 Reached -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#=
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.
References
BUG:12537403 - ORA-00701 ON I_OBJ1 INDEX REBUILDNOTE:152282.1 - Overview Of ORA-01632: Max # Extents (%s) Reached in Index %s.%s
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/17252115/viewspace-753108/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- ORACLE 8i 遇到報錯:ORA-01631: max # extents (505) reached in tableOracle
- SGU 505
- Overview of Extents(11)View
- MSSQL---extentsSQL
- Extents in Indexes (19)Index
- 一個ORA-604錯誤的分析
- redis rebloom 報錯 Maximum expansions reachedRedisOOM
- 如何修復 HTTP 505 錯誤?HTTP
- How To Efficiently Drop A Table With Many Extents
- 為lob型別分配extents型別
- oracle中extents存在的理由Oracle
- Extents in Materialized Views and Their Logs (18)ZedView
- skipped: maximum number of running instances reached (1)
- [doc]How To Efficiently Drop A Table With Many Extents
- JavaFX教程-廣度(Extents)和列舉Java
- Oracle 11G OCP 1Z0-053 505Oracle
- 解決 Error: ENOSPC: System limit for number of file watchers reachedErrorMIT
- AUM模式下,undo extents獲取演算法模式演算法
- KEEP INDEX | DROP INDEXIndex
- Find and Fix the Mismatch Between DBA_SEGMENTS and DBA_EXTENTS ViewsView
- dba_segments/dba_extents/dba_tables區別
- 揭開MSSQL---extents內部的神秘面紗SQL
- rowid,index,INDEX FULL SCAN,INDEX FAST FULL SCAN|IndexAST
- 500 501 502 503 504 505 伺服器錯誤伺服器
- INDEX UNIQUE SCAN,INDEX FULL SCAN和INDEX FAST FULL SCANIndexAST
- Clustered Index Scan and Clustered Index SeekIndex
- oracle hint之full,index,index_asc,index_desc,index_combile示例OracleIndex
- IndexIndex
- Index的掃描方式:index full scan/index fast full scanIndexAST
- rebuild index 和 recreate index (重新建立index) 的區別RebuildIndex
- Oracle OCP 1Z0 053 Q505(RMAN DATAFILE BACKUP COPIES)Oracle
- pk 、unique index 和 index 區別Index
- global index & local index的區別Index
- alter index rebuild與index_statsIndexRebuild
- 從dba_extents返回結果為空 - Oracle10gOracle
- B-index、bitmap-index、text-index使用場景詳解Index
- Index Full Scan vs Index Fast Full ScanIndexAST
- Using index condition Using indexIndex