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

ORA-25143: default storage clause is not compatible with allocation policy

When I set extent management to local I'm getting this error message: "ORA-25143: default storage clause is not compatible with allocation policy." How can I resolve this problem?

#--- Executing SQL statement:
create tablespace pin00 datafile 'D:/ORACLE/ORADATA/TRAINER/pin00.dbf' 
size 600M reuse autoextend on extent management dictionary default storage
( initial 64K next 64K pctincrease 0 );
#------------- SQL Results:
Executing sqlplus -s system/training@trainer < "e:/infranet/setup/temp/tmp.sql"
create tablespace pin00 datafile 'D:/ORACLE/ORADATA/TRAINER/pin00.dbf' 
size 600M reuse autoextend on extent management dictionary default storage
( initial 64K next 64K pctincrease 0 )
*
ERROR at line 1:
ORA-12913: Cannot create dictionary managed tablespace
When I set extent management to local I'm getting this error message.
SQL> create tablespace pin00 datafile 
SQL> 'D:/ORACLE/ORADATA/TRAINER/pin00.dbf' size 600M reuse autoextend on 
SQL> extent management local default storage( initial 64K next 64K 
SQL> pctincrease 0 );
create tablespace pin00 datafile 'D:/ORACLE/ORADATA/TRAINER/pin00.dbf' 
size 600M reuse autoextend on extent management local default storage
( initial 64K next 64K pctincrease 0 )
*
ERROR at line 1:
ORA-25143: default storage clause is not compatible with allocation policy
How can I resolve this problem?

If your database was created with a locally managed SYSTEM tablespace, then you cannot create dictionary-managed tablespaces. And I would recommend locally managed tablespaces (LMTs) every time.

As the ORA-25143 error states, your DEFAULT STORAGE clause is not compatible with your allocation policy. I would change the SQL statement to be the following:

CREATE TABLESPACE pin00 DATAFILE 'D:/ORACLE/ORADATA/TRAINER/pin00.dbf' 
SIZE 600M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE 20000M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT STORAGE (PCTINCREASE 0);

The first thing I would do is to limit the datafile maximum size. This way, a rogue process will not cause the datafile to extend farther than you may want it to.

The part of the DEFAULT STORAGE clause that you do not need with LMTs is the INITIAL and NEXT extent sizes. Since the tablespace is locally managed, Oracle will figure out these extent sizes for you.

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