Q
Problem solve Get help with specific problems with your technologies, process and projects.

Error inserting new record in table

I created this tablespace:
CREATE TABLESPACE TBS DATAFILE 'C:....XXX01.dbf' SIZE 500M REUSE
DEFAULT STORAGE (INITIAL 10240 NEXT 10240 MINEXTENTS 1 MAXEXTENTS 256
PCTINCREASE 0) ONLINE;

When I try to insert a new record in table ST I get this error:
ORA-01631:maxextents(256) reached in table TOM.ST

I tried to increase maxextents: (user=system)
ALTER TABLE TOM.ST STORAGE(MAXEXTENTS 500);

but I get this error:
ERROR at line 1:
ORA-25150: ALTERING of extent parameters not permitted

also
alter tablespace TBS default storage (maxextents 500);

ERROR at line 1:
ORA-25143: default storage clause is not compatible with allocation policy

How can I resolve this error?
Your tablespace is a locally managed tablespace (LMT). LMTs do not use maxextents that is why you are receiving the ORA-25150 and ORA-25143 errors. But if that were the only case, you should not be receiving the ORA-1631 error either. Try to create a different tablespace as follows:

CREATE TABLESPACE TBS2 DATAFILE 'C:....XXX01.dbf' SIZE 500M REUSE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

Then move the table to this new tablespace with the following:

ALTER TABLE TOM.ST MOVE TABLESPACE TBS2;

You won't be getting these errors any more.

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close