ORA-1631

[KY.Chuang]

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

[Christian Gebhardt]

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]

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

[Alex Ung]

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.