cancel
Showing results for 
Search instead for 
Did you mean: 

ORA-1631

KY.Chuang
Advisor

ORA-1631

Dear all,
I have a question about ORA-1631.
I know this error problem is
Cause:The maximum amount of space allowed for saving undo entries has been reached for the named table.
Action:If possible, increase the value of either the MAXEXTENTS or PCTINCREASE initialization parameters.
(I already modify maximun number of this table = 505)

But I want to know why this table deafult maximun number value is 121 ???
where to set this parameter when I create a new table.

Ps. db_blocksize = 8k
platform is Win2000 Server

Thanks...Kychuang
Service is King
3 REPLIES
Christian Gebhardt
Honored Contributor

Re: ORA-1631

Hi

You can specify the maxextents-value if you create a table:
create table .... storage (maxextents 505);
If you do not specify a maxextent the default value of the tablespace is used, check with
select max_extents from dba_tablespaces;
You can change it with
alter tablespace default storage (maxextents 505);

Chris
Yogeeraj_1
Honored Contributor

Re: ORA-1631

hi,

This is value is inherited from the tablespace.

E.g. when creating my system tablespace,

===========================================================
CREATE TABLESPACE ydtbs
DATAFILE '/u03/oracle/oradata/yddb/ydtbs01.dbf' SIZE 1048576 K
AUTOEXTEND ON NEXT 5120 K MAXSIZE 1048576 K
MINIMUM EXTENT 64 K
LOGGING
DEFAULT STORAGE(
INITIAL 64 K
NEXT 64 K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 50
)
ONLINE
PERMANENT
EXTENT MANAGEMENT DICTIONARY

===========================================================

I would suggest that you look into Locally managed tablespaces and this type of problem will never occur again!!

E.g.
CREATE TABLESPACE SLX1_TBSADMIN_MEDIUM
DATAFILE
'/u03/oracle/oradata/yddb/tbsadmin_medium01.dbf' SIZE 524288 K
AUTOEXTEND ON NEXT 512 K MAXSIZE 1048576 K
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128 K

if you need any further information, please let us know.

Best Regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Alex Ung
Occasional Visitor

Re: ORA-1631

Hi,

(Very) Old versions of Oracle had a MAXEXTENTS hard limit of 121. Newer Oracle versions don't have this limit, but if you don't explicitly set the limit with MAXEXTENTS when you create a tablespace, it will default to 121.