|
| Thursday, April 16, 2009 |
| Error:ORA-01631 |
Overview Of ORA-01631: Max # Extents (%s) Reached in Table %s.%s
Text: max # extents (%s) reached in table %s.%s
---------------------------------------------------------------
Cause: A table tried to extend past maxextents
Action: If maxextents is less than the system maximum, 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 could be avoided by pro-actively monitoring the object sizes that may reach their max_extents.
Diagnostic Steps:
-----------------
1. Run the following script to identify the tables with extent problems.
SQL> select segment_name, owner, extents, max_extents
2 from dba_segments
3 where segment_type = 'TABLE'
4 and (extents +1) >= max_extents;
2. Follow up the scripts in: Note 1019721.6 SCRIPT: SCRIPT TO REPORT TABLES APPROACHING MAXEXTENTS.
Possible solutions:
-------------------
1. If the value of max_extents for the affected object is less than the limit of maximum extents for the db_block_size of the database (refer to Note 1015356.4), then more extents could be allocated to the object.
Alter the STORAGE clause of the object to increase MAXEXTENTS.
In databases version 7.3.X or higher one could specify maxextents
UNLIMITED (i.e. 2147483645), but use this with caution.
ALTER TABLE .table STORAGE ( MAXEXTENTS x);
where x is greater than max_extents and lesser than unlimited (2147483645);
ALTER TABLE .table STORAGE ( MAXEXTENTS UNLIMITED);
Remark: Note 50380.1 ALERT: Using UNLIMITED Extent Format
2. It is not advisable to have a lot (hundreds of thousands, etc.) of extents that could result in large amounts of of space management operations in dictionary managed tablespase. In that case, or when the number of max_extents for the affected object reached the limit of maximum extents for the db_block_size,recreate the object with more efficient storage.Export the table and precreate the table before import, using larger storage parameters (initial, next, pctincrease) - to result in fewer extents, or export using COMPRESS=Y - to minimize number of extents after import.Labels: Error:ORA-01631 |
posted by Srinivasan .R @ 4:54 AM  |
|
|
|
|
|
